November 6, 2020 at 8:53 pm
Hello,
We've set up a new SQL Server instance on a new server to be used strictly for reporting purposes. We will need to be able to replicate the database from our production environment onto the reporting server on a nightly basis. This database can be read-only, since it will only be used for reporting.
I'm having a tough time finding the ideal solution for this. Here's what I've looked at so far:
Log Shipping - Biggest issue here is that the database is unavailable for a while. Pretty sure we can't afford to have this happen, even in the middle of the night.
Availability Groups - Sounds great, but Enterprise edition is needed. We don't have that.
Snapshot Replication - We currently do some of this, and we're not very fond of it. It is a pain to set up, it has to be recreated after schema changes, and a current replication process we already have in place for this database takes 15 minutes for the snapshot to get created and 1.5 hours for the actual replication. We have nightly maintenance processes running against the production database throughout the night already, so I'm concerned about performance issues.
Database Snapshot - Sounded perfect, until I learned that SQL Server can't place a snapshot onto another SQL instance.
I would greatly appreciate any thoughts on how this can be done that would minimize performance impact on the production database, would be easy enough to set up, and require minimal maintenance and overhead.
November 7, 2020 at 3:22 am
If the boxes use the same SAN, check with the SAN folks or vendor. There is such a thing as "SAN Snapshots". My first exposure to it was way back in 2003. It took 6 seconds on 32 bit equipment on spinning rust for a database that would be considered "small" by today's standards. You can probably do a 2TB database in about the same time on today's equipment.
We don't have the need anymore but we were doing similar with our 4TB server with about 60 databases on it. It took about 30 seconds. The reason it took "so long" was because it first had to detach all the databases, then do the SAN snapshot, and then reattach all of the databases because it was slightly different technology that what the other company used back in 2003.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2020 at 3:54 am
4T in a few seconds !?!?
November 7, 2020 at 10:53 am
with newer technology it is possible on the SAN side - look for example at Dell XtremeIO - its snapshot technology means you can just click the button and you have a "copy" of the data available on another server for use.
setting up a copy of a SQL server is as easy as
and with the added benefit that from a SAN point of view only a copy of the data exists - once source is updated the "old" page is duplicated - but as long as pages remain as they are then a single page of data serves both replicated and source database
November 7, 2020 at 5:41 pm
It all depends on the SAN though - so check with the storage team on what is available. It may require reconfiguring your instance - as you really do not want your system databases on the same volume/lun as the user database and log files (generally cannot freeze SQL for the snapshot if that is the case).
Other options - backup/restore...and here you can also use SAN snapshots to help. Snap the backup drive, split the drive and present to the secondary server, restore from backups. Prevents you from having to 'copy' large backup files across the network before restoring - and restores from local drives which will be faster.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 8, 2020 at 3:28 pm
4T in a few seconds !?!?
Yes. No typo's there.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2020 at 8:00 pm
is nightly replication good enough or more frequent replication is not allowed? If more frequent replication is ok, you may try transactional replication or merge replication, both were supported by non-Enterprise Edition. More frequent replication will make the impact of data unavailability much, much less noticeable. Merge replication supports updates on subscriber side, but you don't need to update on subscriber anyway.
November 8, 2020 at 8:19 pm
I can't speak for others but I find that anything that relies on log files for replication is a huge PITA, especially when it comes time to doing index maintenance.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2020 at 5:25 pm
Here's a method I often use. "Ideal", don't know.
Use full backups and differentials. Generally a differential restores very quickly (you can do the "base" restore of the full backup ahead of time). The hardest part is being able to replace the original db with the new version because of on-going activity against the db (although presumably you're dealing with that issue already).
Here's the essentials of the method, assuming the main db name is:
reporting_db
-1- Do a full backup once a week (or whatever other time frame works best for that db)
-2- Restore it WITH NORECOVERY to a temp name on the reporting server.
RESTORE DATABASE reporting_db_copy1 FROM DISK = '...' WITH NORECOVERY;
-3- If needed, apply the latest differential WITH NORECOVERY.
-4- Swap out the main db and the restored one (the hard part sometimes)
Either:
ALTER DATABASE reporting_db MODIFY NAME = reporting_db_copy2
RESTORE DATABASE reporting_db_copy1 WITH RECOVERY;
ALTER DATABASE reporting_db_copy1 MODIFY NAME = reporting_db
Or: If the renaming can't complete, then do something like this:
ALTER DATABASE reporting_db SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE reporting_db SET ONLINE, SINGLE_USER;
RESTORE DATABASE reporting_db FROM DISK = '...' WITH REPLACE, NORECOVERY, /*, MOVE '...' TO '...', /*if needed*/*/
RESTORE DATABASE reporting_db WITH RECOVERY;
ALTER DATABASE reporting_db SET MULTI_USER;
-5- Pre-restore the full db again to a "working" copy of the main db WITH NORECOVERY (this saves time for the next restore/recovery).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 9, 2020 at 9:51 pm
Thank you everyone for your suggestions!
With a fairly short timetable with which to implement a solution, I may not be familiar enough with some of the implementation details of the SANS-related solutions to be able to roll with those in the early going, but I'll definitely keep them in mind as I'm searching for the most optimal approach. I was hoping to avoid transactional replication, but that would be another possibility. I may start by experimenting with Scott's approach, assuming this can be entirely automated through SQL agent jobs with T-SQL, as I'm thinking I might be able to put a solution like that in place more quickly, and if I can configure it to run once daily in the early morning hours, I'm thinking I can avoid adverse performance during the day that might come from transactional replication.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply