Post reply

Max databases for mirroring

  • July 19, 2009 at 10:05 am

    #155984

    Just wondering, I know according to the 'Official' guides you should not mirror more than 10 databases. However this is due to the number of threads and not a real limit. Also with 64 bit OS's the number of threads is increased.

    Does anyone have any real experience on the number of databases they are mirroing?

    Any issues / maximums they have reached ?

    Any issues with the time between 'mirroring' increasing ?

    You thoughts ?

    Thanks

    Shane

  • July 19, 2009 at 11:20 am

    #1026648

    I have only done some tests with DBmirroring.

    I don't have actual experience with many mirrored databases of the same insatnce.

    I just wanted to let you know MS customer advisory team (SQLCAT.COM) has some nice info on DB mirroring.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 19, 2009 at 11:24 am

    #1026649

    Theres some discussion on this on the discussion thread for the mirroring QOTD from the 16th july

    http://www.sqlservercentral.com/Forums/Topic753931-1598-1.aspx

    ---------------------------------------------------------------------

  • July 20, 2009 at 10:36 am

    #1027140

    We are using the Mirroring feature to provide a "very warm" backup for 149 (at last count) databases. We have two identical machines, and use the high performance (not synchronous) mode. I don't think it would have mattered if we had used synch mode, the network bandwidth and CPU use on the mirrored machine is very little, but I didn't want the users on the primary machine to have to wait for the commit.

    These are not huge databases, they total a little over 100 Gig, but the largest is about 20 Gig, and most have several simultaneous users. Our only problem was that we quickly ran out of worker threads on the mirrored machine, (mirroring takes 5 threads per db on the mirror, 2 threads per db on the primary). Setting the worker threads to 1000 on the mirrored machine took care of that with no observable effect.

    64 bit, 16 gig ram, dual Xeons in case you care. Network hub is only 100 Mbit.


    Student of SQL and Golf, Master of Neither

  • July 20, 2009 at 10:46 am

    #1027146

    Great

    Thanks for your response, nice to have a 'real' example.

    Well I have given in to clustering again and changed the windows edition to Datacentre, the costs between standard and datacentre are not that much. Suprisingly enough enterprise is more!

    However I may also incorporate mirroring in to the cluster as well to get the best of birth worlds if it is possible. Just got to ensure the timeouts are set quite high on the mirroring so a cluster fail over does not force the mirror to take over.

    Thanks for your help

    Shane

  • July 20, 2009 at 10:54 am

    #1027149

    I had similar fears about the failover. We went with NO automatic failover, and thus, no witness server. We wanted a very near real time backup, and the customers are willing to put up with a delayed manual failover once a real person has investigated and determined that it is actually needed. The external clients were written to know where the primary and the mirror live, and we simply have to issue the failover command to get them up and running.


    Student of SQL and Golf, Master of Neither

  • July 30, 2009 at 10:46 am

    #1032530

    I have four DB's that are mirrored up in Synchronous (High Safety) that have hundreds of clients streaming data in at a rate of thousands of inserts per second. My setup is a little more beefy, though, with 4-proc dual cores, 32 GB RAM, teamed NIC's w/ 2GB pipes...

    I have absolutely no lag. In a previous job, I had 16 DB's set up in sync mode, dozens of users, no issues whatsoever. Mirroring is definitely a good way to provide for accessibility, if the apps are coded correctly to work with it.

    Overall, I think it really depends on the number of users, transaction rates, hardware capabilities, as well as performance throttling on the backend.

    -Ken

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply