SQL Mirror and Bandwidth

[snootalope]

We've got SQL Server 2005 running on both ends of a 3MB pipe. From what I can tell, I'd say the bandwidth consumption average during working hours is about 800Kbps.

Now, we've only got about 80 users, but nearly all are working in something that uses our primary SQL server. So, there's probably lots of transactions/changes being made through out the day.

Question is, does 800 Kbps seem high to anyone? That just seems like an awful lot of constant traffic between these two mirror servers to me.

If that's the way it is, ok. But if there are any options I might have, I'm all ears if you'd like to lend some advice!

Thanks

 

[mrdenny]

What else is going through the pipe? Are you replicating active directory, email between sites, and users in one site hitting file shares in another site?

An easy way to see how much of this is the transaction log and how much is overhead is to check the space usage of the transaction log file. How much does it grow per minute / second?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[snootalope]

Our transaction log is about 3GB and it never seems to change.. I was under the impression that once a backup occurs that that file it truncated. Is that not the case?

I guess first off, I'm some what of an SQL noob, if I hit the properties of my database and choose file, I can see the "Logical Name" of my two database files, the one that has _log after it is the transaction log right?

Do I need to do something to that file so it shrinks every night?

If it stays at or above 3GB the whole time, does that mean it's constantly having to replicate 3GB?

 

[snootalope]

Sorry, forgot to answer your other questions. We use the pipe for our internet as well. We're a fairly small shop with only about 45 users. I know it's SQL taking up 90% of the traffic though cause I can watch the third interface on our firewall and it's traffic rate, that's where the replication is going.

 

[mrdenny]

The log is truncated only after a transaction log backup. Full backups do not truncate the log.

When the log is truncated the file does not shrink. Shrinking the log file isn't recommended as this puts unneeded load on the disk.

Mirroring only replicates the new transactions, not the entire log file at once. Mirroring sends a near real time stream from server to server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[snootalope]

Is there a way to see the size of the data stream from server to server?

 

[mrdenny]

You would have to use network monitoring tools to see that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog