May 21, 2013 at 10:00 pm
I am involved in project of upgrading database servers to SQL 2012 using AlwaysOn functionality. It is still in planning phase and i am currently working on capacity planning.
My question are
1. Is there any restriction on number of databases in one availability group?
2. Current SQL 2008 R2 instance is hosting 600 databases, has anybody experienced any issues with these number of databases running on 1 SQL instance? I know technically i can have 32k databases on 1 instance but in reality considering transaction log backups, agent jobs running on each database and other DB maintenance tasks, it will be a challenging task for DBA maintaining these databases. Question is, has anyone worked with these number of databases before and what were their experiences.
3. Few databases are being replicated to other database servers, what special care do i need to take in order to setup replication with AlwaysOn?
May 22, 2013 at 10:59 am
Anam Verma (5/21/2013)
I am involved in project of upgrading database servers to SQL 2012 using AlwaysOn functionality. It is still in planning phase and i am currently working on capacity planning.My question are
1. Is there any restriction on number of databases in one availability group?
2. Current SQL 2008 R2 instance is hosting 600 databases, has anybody experienced any issues with these number of databases running on 1 SQL instance? I know technically i can have 32k databases on 1 instance but in reality considering transaction log backups, agent jobs running on each database and other DB maintenance tasks, it will be a challenging task for DBA maintaining these databases. Question is, has anyone worked with these number of databases before and what were their experiences.
3. Few databases are being replicated to other database servers, what special care do i need to take in order to setup replication with AlwaysOn?
1) the things you are looking to/needing to do will almost certainly be a disaster if you attempt this type of stuff without qualified professional help.
2) I doubt very much that you can mirror 600 databases from one server to another. that is a TREMENDOUS amount of activity/work to be done on both sides. And I say mirroring here because fundamentally that is what "alwayson" is.
3) I have a long-time client that made it up to 7400+ databases on one server. I had to jump through quite a few hoops to get it to work properly, including building my own log shipping system to restore them over to a reporting/DR server. But it was completely functional, and saved a lot of cost AND complexity having to manage databases across multiple servers that would all need to connect up to some "global" databases that could only exist on one server. I note that once you get above a certain number of databases, 3rd party tools start to fall over and die. The ONLY tool I could connect to that monster server was SSMS believe it or not. RedGate, ApexSQL, Embarcadero, Idera, etc. all died a horrible death (and/or exposed serious product bugs) when trying to work with the metadata that comes with 7000 databases on one server.
4) replication with any form of "failover" (clustering, mirroring, tlog shipping) can be an absolute nightmare. See 1) above.
5) Feel free to ping me if you want any further assistance. Anything more than the above is well beyond a forum scenario.
Best of luck in any case!! Sounds like a REALLY fun project!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply