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.
- Transactional Replication
Allows you to replicate all or required data by adding condition while adding an article to the publisher.
We can create indexes on the subscriber (S3/S2) according to the requirement of reports queries to improve report performance.
- We need to run index maintenance script on both servers separately.
- Separate FULL & T-Log backup of the subscriber database maxes redundant backup of same data.
- 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.
- For large OLTP database, replication performance becomes a challenge.
- Above all handling, replication is quite tough.
- Read-Only secondary database.
- All database maintenance script will be executed on the primary database.
- You can choose the preferred database server to execute a backup.
- No need to execute T-log backup of the database across all nodes.
- Easy to manage.
- 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.
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
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.