Geek Answers Handbook

Active - Active DR Strategy for SQL Sever 2005

We are trying to develop an Active-Active DR strategy for our 6 TB data warehouse. Our data warehouse has 40 databases, and everything must be replicated on a real-time basis.

Site 1: it is necessary to process all ETL; Site 2: will process all reporting requests.

  • Database mirroring (cannot allow to refuse and create snapshots, since we cannot kill any connections)
  • Replication
  • Magazine Delivery

Migration to SQL Server 2008 is possible.

What is the best way for performance and availability?

Regards, Nagy

+4
source share
4 answers

Since you cannot afford to refuse active connections, sending a log is also not an option. To restore the log, you need to get exclusive access to the database. Hardware support (SAN) will be very helpful. I would really like to see you ETL on one server, and then bind to the active server for reporting and use another server for ETL. So you have a report server without an ETL process and an ETL server without any reports, but do you change them at night? the basis.

+2
source

You need to talk to your hardware vendor — especially the storage — to see if they provide any kind of hardware replication. Looking at the amount of data, I do not think that a software-based solution would be optimal.

This is how I process it for 3 databases (11, 17 and 23 TB) right now.

  • We host the database on the EMC SAN.
  • Every 12 hours, the databases are cloned on different holes located on the same SAN, and then mounted on different servers. This is a backup if the primary servers are shut down. These databases are usually 12 hours behind the main databases. We use them for reporting where we can live with 12-hour data.
  • Every 24 hours, clones of 2 are copied to another SAN in another building and mounted. This is a secondary backup. In these databases, we run diagnostics, DBCC checks, etc.
  • In total, we are launching a total of 9 instances of SQL Server Enterprise Edition (3 products, 3 first rows of DR and 3 second row of DR).
  • We decided to go this route, since we could live with a 24-hour data lag.

This is certainly doable, but it will require fair planning as well as investment in your role. For us, the cost of a license for 9 EE was slightly less than the price of two SANs and interconnections between them.

0
source

Peer-to-peer replication is probably the best option for you if you do not want to go the expensive way to replicate SAN equipment.

It offers almost real-time, so it should be good enough for reporting.

0
source

To a large extent, SQL Server replication or some kind of client solution using SQL Service Broker will be your best bet. If your tables are static and all data changes are made on one site, then transactional replication may be your best bet. You will need a large WAN channel to handle replication, as transaction consistency is maintained even when using multiple threads.

SQL Server 2008 has some replication performance improvements since it allows multiple distribution threads, which may help you.

0
source

More articles:


All Articles