Hi
I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?
On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?
Does anyone have any experience of mirroring and is using it in prefence to log shipping?
Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring
For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?
Thanks in advance for any feedback
Robert
I also was greatly disappointed to learn that MS recommends only 10 mirrors per server instance. Considering that I have around 1300 databases in a federated model spread out over 12 servers, it threw a major monkey-wrench into our plans. So, now we are considering ranking clients on a scale and putting top level clients on DB Mirrors and 2nd level clients on log shipping. Demo db's for the various inernal departments get nothing (no soup for you!!).
Currently, I am still trying to figure out how many mirrors I can actually do on a server. I have 19 mirrors running on a 64 bit server with a 64 bit mirror with no noted performance degradation. Thread consumption did double (approximately) when I added the mirrors, but I'm still way below what's available (average of 100 threads consumed out of an available 512). However, I have to keep in mind that this is the slow part of the year for our clients and we have to leave enough threads avaialble to handle the increased load that will come at the end of the year.
I've suggested offering DB miroring as an upgrade that clients can purchase due to the increased hardware requirements. Log shipping = free, mirroring = $$$.
Yes, handling of the automatic failover does require some specific coding. The SQL Native Client and ADO.Net 2.0 have the ability to specify a failover partner in the connection string, and it automatically tries the failover partner if the principal server is not available. Earlier versions of ADO or other db access interfaces that do not support failover partners must be coded to manually try a second connection string if the first fails (which generally invovles a long timeout period). Another option is to build a middle tier that handles the transport of client db access so that the client isn't even aware of which server it is using.
If indeed you do not need the data to be up to the minute, then perhaps log shipping is the answer for you.
|||Yes, I'm rapidly coming to the conclusion that 15 minute log shipping will be sufficient for our needs.
The majority of our database requirements are from 3rd party software. There are often very slow to update - many don't support SQL 2005 yet anyway!
|||Robert,
I would be interested in any real world information you would like to share about mirroring. It would be good fodder for future articles at SSWUG.ORG as well as support to my company for implementing mirroring. They are concerned about the newness of this feature.
Cheers,
Ben
btaylor@.sswug.org
|||As was stated above, we currently recommend no more than ten databases be configured for mirroring on a single instance. This is not a fixed limit, but due to resource consumption is a good general guideline. Some customers are perfectly happy with larger number of mirrored databases, however your mileage may vary depending on hardware, application, etc. Mirroring was not designed for large numbers of database on the same instance. If you are facing this scenario, MSCS is a more natural choice (with optional addition of storage level replication for disaster recovery).
Regards,
Matt Hollingsworth
Microsoft SQL Server
No comments:
Post a Comment