Best practise for large SQL database Always On

Copper Contributor

Hi

 

We are planning to implement SQL server Always ON AG to achieve HA for 2 DBs, however the databases are particularly large (Approx 1TB), the bandwidth between two sites is around 50Mbps, there will be very little changes made to the databases comparing to the sizes of them, what is the best practise to implement it

 

Thank you so much and Regards Ben 

3 Replies

@cuguru 

 

Hi, Ben.

 

The short answer is that there's plenty of information on learn.microsoft.com around settings up availability groups.

 

It's not like there's alternative ways in which to set up availability groups, so I wouldn't go so far as to say there's a "best practice", but SQL AlwaysOn depends on Windows Failover Clustering Service which is a lot more complicated and for which there are recommended practices - particularly around how the networking and DNS is configured.

 

There are are lot of individual decision points to navigate - such as how many replicas you have and their synchronisation mode, but this isn't a best practice topic. It's simply about choosing the best configuration for your needs.

 

Getting to the heart of your question though, it's not about the size of the database but the rate of change (i.e. transactions per second). Because you've stated that the rate of change will be quite low, 50 Mbps really should be ample - depending on how many other services are competing for that bandwidth.

 

Again, there's a lot of comprehensive documentation for AlwaysOn and Windows Failover Clustering Service on learn.microsoft.com. The following article might be a good place to start reading given the nature of your question:

 

 

Cheers,

Lain

@LainRobertson 

Thank you so much for your reply. I have concern regarding hte initial DB sync as the DB sizes are large considering the bandwidth between the two site for SQL alaways on AG if auto seeding is used

cuguru_0-1681628898164.png

What shall be the best option in our scenario please

 

Thanks and Regards Ben

@cuguru 

 

That's entirely your choice.

 

If you're patient, I'd stick with the automatic seeding option. But if you don't want to, then send a full back-up over to the secondary site, restore it and then use the join option.

 

But ultimately, there's no absolutely right or wrong option - which is why SQL Server gives you so many to choose from.

 

If I were doing this, I'd probably use the automatic seeding and run the process after hours, but as I say, it's your decision.

 

Cheers,

Lain