Sql-server – Questions around migrating to a live replicated database from an existing log shipping database in SQL Server 2016 SP1

replicationsql servertransactional-replication

I have two large databases, one on my production server named S1 and the other on my reporting server named S2.

S1 is the live database used for our customer orders and accounting, and S2 is the reporting database, which is a one-way, read-only copy of S1.

Every 2 hours, S2 is taken offline, then a log shipping job runs on S1 and updates the last 2 hours of transactions into S2 on the reporting server. It works, but isn't quite up to date enough any more for my users' needs, and having it go offline every 2 hours and interrupt any queries running at that point is less than ideal, so they end up running queries on S1 and causing untold problems.

I need to set up simple replication such that S2 is updated as soon as there are any changes in S1. I've never done this before so I'm quite nervous.

I can set up a new database on my reporting server, there's plenty of capacity, so I'm in the process of setting up a live read-only transactional replication named S3. When I'm happy it's working and matches S2, I just want to do a straight swap and start using S3 for all my reports etc.

Is it as simple as just detaching S2, then renaming S3 to S2?

Provided the tables and indexes etc were all identical, Would all my users' queries just work at that point?

Instead of doing this, is it a better idea to reconfigure the existing DB from a log shipping model to a transactional model, or is that painful?

Best Answer

Your requirement is real-time data synchronization from S1 to S2 for users requirement of realtime reports.

It works but isn't quite up to date enough anymore for my users' needs, and having it go offline every 2 hours and interrupt any queries running at that point is less than ideal, so they end up running queries on S1 and causing untold problems.

So you have 2 options other than Log-Shipping.

  1. Transactional Replication
  2. AlwaysOn

Transactional Replication

Advantages:

  1. Allows you to replicate all or required data by adding condition while adding an article to the publisher.

  2. We can create indexes on the subscriber (S3/S2) according to the requirement of reports queries to improve report performance.

disadvantage

  1. We need to run index maintenance script on both servers separately.
  2. Separate FULL & T-Log backup of the subscriber database maxes redundant backup of same data.
  3. Distribution database maintenance like index rebuilding/reorganizing, a database full & transaction log backup to make sure transaction log don't grow to consume available space.
  4. For large OLTP database, replication performance becomes a challenge.
  5. Above all handling, replication is quite tough.

AlwaysOn

Advantages:

  1. Read-Only secondary database.
  2. All database maintenance script will be executed on the primary database.
  3. You can choose the preferred database server to execute a backup.
  4. No need to execute T-log backup of the database across all nodes.
  5. Easy to manage.

Disadvantage:

  1. You can't create additional indexes on the secondary replica according to the requirement of report queries.

Is it as simple as just detaching S2, then renaming S3 to S2?

If all SQL Server credentials, Security, and settings are same, then yes you can simply detach S2 and rename S3 to S2. make sure computer name/object name (S2) is updated at DNS server and redirecting to the IP address of new S2(renamed from S3) server.

Or

You can also CName a CName connecting to current S2. Once S3 is ready you can redirect the CName to connect S3.

This way wouldn't have to make any changes in the applications connection string whenever you'll be migrating DB server. Only you'll need to update CName on the DNS server.

Provided the tables and indexes etc were all identical, Would all my users' queries just work at that point?

If you have all database objects (tables, views, type, function, stored procedures and triggers) on S2 identical to S1, Yes it will work properly.

Instead of doing this is it a better idea to reconfigure the existing DB from a log shipping model to a transactional model, or is that painful?

I would say Yes it would be better to reconfigure Log-Shipping secondary DB to the subscriber of transactional replication.

Reason: DB on S2 is already in sync so if you initialize subscription from backup, subscriber database will take very less time to sync in comparison to the lengthy process prepare S3 server.

Thanks!

Related Question