June 3, 2021 at 9:17 pm
Hi,
I've just started looking at using a database snapshot to do an overnight job on a database that is using transactional replication.
The reason being the job creates a table, being new to transactional replication, the job ran fine when the db was a backup restore but now takes much longer . Can I write the table to the original DB using the snapshot?
Thanks and I'm all ears to other options
June 4, 2021 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 9, 2021 at 5:55 pm
I think first you need to understand the difference between a database snapshot and a backup. The snapshot just marks a point in time of the current database state and then captures changes. As far as I know, it cannot be used to change anything on a different database; it can just be used to roll back the current database to a previous, static point in time.
A nice writeup on snapshot vs backup is available here:
https://www.sqlshack.com/understanding-database-snapshots-vs-database-backups-in-sql-server/#:~:text=Difference%20between%20Database%20backups%20vs%20Database%20snapshots%20,snapshot%20%20...%20%207%20more%20rows%20
I think the first step would be to determine exactly what you are doing currently and what you are trying to do. I see you are using terms like "transactional replication" but then you say you are restoring a backup, so I am not sure which it is that you are doing. On top of that, I don't know the "why" behind this. Is this to have a data mart or to make a pre-prod environment closer to prod or just to have a hot-spare available, or something else completely.
If you are using replication, I believe that should only be pushing across the changes that you have selected to push across. You have your primary data source (the publisher), a distribution server (the distributor), and one or more consumers (the subscriber). So when something changes on the publisher, it tells the distributor what changed and the distributor holds that data so the subscribers can request the list of changes. As far as I know, this shouldn't rely on a backup-restore process.
Now, if your method of replicating the data is to backup the source (full backup), drop the destination, restore the destination from the source backup, that is going to get slower and slower as the source gets larger and larger. Depending on the requirements, you may benefit from using SSIS or stored procedures to push across the data you care about that has changed on a nightly basis. Or, if linked servers are set up, you could have the destination server pull data from the source on a schedule. If you need real-time syncing of the data, service broker may be a good option. If you can handle delays and scheduled refreshes, you are on SQL Server 2016, so CDC (change data capture) may be a viable option and then an SSIS job to push that data across.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply