January 19, 2018 at 12:44 pm
I would like to know what would be an alternative solution to mirror tables from one sql instance to another without the use of mirroring or replication and have it synched every 4 hrs. Also, is there a free or paid 3rd party tool or anything that can be scripted and run through a SQL job which can have minimal impact on prod (please exclude SSIS). Just to clear the air replication is not needed as it would lock the tables during the snapshot process whereas mirroring is not needed as it will bring in lot of extra stuff with it.
January 19, 2018 at 12:50 pm
Log shipping is easiest, BY FAR. But like mirroring it does everything.
Since it sounds like you want a "SMALL" subset of tables, I would just create simple triggers that capture the key field(s) of any changed rows and then run a simple UPSERT script from one server to the other on your frequency. VERY simple to do, can be scripted to be generic, and exceptionally light (and controllable) load. I have done this MANY times for clients over the years.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 22, 2018 at 11:13 am
Have to say if it's just a subset of tables then log shipping is over kill and Kevins solution is by far the best
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2018 at 3:38 pm
I'd use transactional replication and schedule the distribution agent to run every 4 hours. That way you snapshot once (when you set it up), not every 4 hours.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2018 at 9:39 am
GilaMonster - Monday, January 22, 2018 3:38 PMI'd use transactional replication and schedule the distribution agent to run every 4 hours. That way you snapshot once (when you set it up), not every 4 hours.
Are there any drawbacks of having the distribution agent run every 4 hrs. instead of running continuously...log hold up etc?
January 23, 2018 at 9:40 am
TheSQLGuru - Friday, January 19, 2018 12:50 PMLog shipping is easiest, BY FAR. But like mirroring it does everything.Since it sounds like you want a "SMALL" subset of tables, I would just create simple triggers that capture the key field(s) of any changed rows and then run a simple UPSERT script from one server to the other on your frequency. VERY simple to do, can be scripted to be generic, and exceptionally light (and controllable) load. I have done this MANY times for clients over the years.
Do you have a generic script or template that I can make use of to implement that concept.
January 23, 2018 at 9:58 am
ffarouqi - Tuesday, January 23, 2018 9:40 AMTheSQLGuru - Friday, January 19, 2018 12:50 PMLog shipping is easiest, BY FAR. But like mirroring it does everything.Since it sounds like you want a "SMALL" subset of tables, I would just create simple triggers that capture the key field(s) of any changed rows and then run a simple UPSERT script from one server to the other on your frequency. VERY simple to do, can be scripted to be generic, and exceptionally light (and controllable) load. I have done this MANY times for clients over the years.
Do you have a generic script or template that I can make use of to implement that concept.
I do have such. But it is part of the value I represent for my clients as a SQL Server consultant, so it is one of the things I choose to not make publicly available.
But it is not rocket science and can be scripted/automated using any number of options. Pick your flavor and have fun!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 23, 2018 at 10:02 am
TheSQLGuru - Tuesday, January 23, 2018 9:58 AMffarouqi - Tuesday, January 23, 2018 9:40 AMTheSQLGuru - Friday, January 19, 2018 12:50 PMLog shipping is easiest, BY FAR. But like mirroring it does everything.Since it sounds like you want a "SMALL" subset of tables, I would just create simple triggers that capture the key field(s) of any changed rows and then run a simple UPSERT script from one server to the other on your frequency. VERY simple to do, can be scripted to be generic, and exceptionally light (and controllable) load. I have done this MANY times for clients over the years.
Do you have a generic script or template that I can make use of to implement that concept.
I do have such. But it is part of the value I represent for my clients as a SQL Server consultant, so it is one of the things I choose to not make publicly available.
But it is not rocket science and can be scripted/automated using any number of options. Pick your flavor and have fun!
I was asking for a generic template not a complete solution. Anyways, thanks! for the help
January 23, 2018 at 10:17 am
ffarouqi - Tuesday, January 23, 2018 9:39 AMGilaMonster - Monday, January 22, 2018 3:38 PMI'd use transactional replication and schedule the distribution agent to run every 4 hours. That way you snapshot once (when you set it up), not every 4 hours.Are there any drawbacks of having the distribution agent run every 4 hrs. instead of running continuously...log hold up etc?
Distribution agent, not log reader. You do not want to stop or delay the log reader.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2018 at 1:14 pm
ffarouqi - Tuesday, January 23, 2018 9:39 AMAre there any drawbacks of having the distribution agent run every 4 hrs. instead of running continuously...log hold up etc?
One possible consideration is the transactions would be held in the distribution database so the distribution database should support that.
Sue
January 24, 2018 at 3:27 am
ffarouqi - Friday, January 19, 2018 12:44 PM...Just to clear the air replication is not needed as it would lock the tables during the snapshot process ...
You can also set up transactional replication to be initialized from a backup, which avoids the snapshot process altogether. If you choose to go down that route.
Cheers,
Angus
January 24, 2018 at 5:39 am
Sue_H - Tuesday, January 23, 2018 1:14 PMffarouqi - Tuesday, January 23, 2018 9:39 AMAre there any drawbacks of having the distribution agent run every 4 hrs. instead of running continuously...log hold up etc?One possible consideration is the transactions would be held in the distribution database so the distribution database should support that.
Sue
By default, all transactions are retained in the distribution database for (I think) 30 days. (30 or 14) to allow for disconnects, new subscribers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2018 at 6:34 am
GilaMonster - Wednesday, January 24, 2018 5:39 AMSue_H - Tuesday, January 23, 2018 1:14 PMffarouqi - Tuesday, January 23, 2018 9:39 AMAre there any drawbacks of having the distribution agent run every 4 hrs. instead of running continuously...log hold up etc?One possible consideration is the transactions would be held in the distribution database so the distribution database should support that.
Sue
By default, all transactions are retained in the distribution database for (I think) 30 days. (30 or 14) to allow for disconnects, new subscribers.
On all of the ones I've seen or set up, transaction expiration defaults set to "at least 0 hours and not more than 72 hours". Just set another one up to test and that's what I see.
It's configurable and I've often seen it changed which is why I would see it as a possible consideration.
Sue
January 24, 2018 at 9:29 am
GilaMonster - Wednesday, January 24, 2018 5:39 AMSue_H - Tuesday, January 23, 2018 1:14 PMffarouqi - Tuesday, January 23, 2018 9:39 AMAre there any drawbacks of having the distribution agent run every 4 hrs. instead of running continuously...log hold up etc?One possible consideration is the transactions would be held in the distribution database so the distribution database should support that.
Sue
By default, all transactions are retained in the distribution database for (I think) 30 days. (30 or 14) to allow for disconnects, new subscribers.
One of the concerns that got raise were surrounding downtime if we take the snapshot route (backup route is not possible as the size of the DB is in TB). Also, they don't want to spend money on too much space and also don't want to take a hit in performance as the tables would be locked until the snapshot process is complete. Is there a better way out to balance both space and performance as there are only subset of tables from may be say 10s of DBs that are terabyte sized.
January 24, 2018 at 10:22 am
This is one of the very few times I will disagree with Gail. I would stay away from replication in any case where possible, and I am certain that it is in this case. Too many horror stories with it over the years.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply