Advice on replication

Question

Post reply

Advice on replication

davidgray

SSC Eights!

Points: 860
More actions
May 25, 2012 at 6:12 am

#257037

Hi,
We currently have 70+ customer databases which need to be kept in sync with a master database. When I say in sync I mean the
metadata and some of the tables which contain reference data such as country, currency etc. At present this sync is handled by various
sql scripts which are run against the customer databases at periodic intervals. It's a mess!
I have dabbled with replication some five years ago and started to wonder if this could help solve the problem of keeping these all in step.
I was thinking that the master database be setup as a publisher and that the customer databases be setup as subscribers using the pull model,
I don't want this to be fully automatic as there are times when a customer needs to be on an older version.
It seems like this would all be do-able in Sql2008 (or 2012), however there is one issue that may cause problems, The customer databases are
backed up and moved to remote (inaccessible) site, used by the customer for the duration of an event and then restored back on the server here.
Would this be a problem for replication?
The data in the customer databases *never* needs to be pushed back to the master.
Any thoughts good or bad on this scenario will be most welcome.
Thanks
Dave

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

I'm a fan of replication but my initial thoughts are that replication could cause some problems here.

Reasons:

a) You'd need to set up a distributor, and a network share for all 70 databases to have access to (plus the master). If these are separate sites or domains then this is going to be a problem - you might find half your maintenance problems will be due to access to this share.

b) You need some databases to remain on the older versions and some up-to-date - you could use transactional replication (or snapshot replication) to achieve this by simply not including some databases as subscribers, and adding and removing these as you see fit. But this seems like a lot of manual work.

3) I'm not sure what effect modifying a schema 'offline' then re-adding it as a subscriber will have on replication - I suspect if the schema / tables within it change shape (# of columns, constraints etc) then you'll get errors. I might be wrong here.

Perhaps you could set up replication for those customer dbs that NEVER need to be taken 'offline' and changed and that must be kept up to date, then modify your existing scripts to cover the rest?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

davidgray SSC Eights! Points: 860 More actions · Answer 2

Hi,

Thanks for the response. Yes I was considering snapshot replication as I would want to control

a) when the snapshot was taken, usually once per month.

b) I want to manually kick off the subscriber process.

I neglected to say that the only changes to the database when it's offsite

is that rows will be updated/added/deleted. There will be no change to metadata.

The 70 databases are all within the same domain when not offsite at a customer event.

derek.colley (5/25/2012)
I'm a fan of replication but my initial thoughts are that replication could cause some problems here.
Reasons:
a) You'd need to set up a distributor, and a network share for all 70 databases to have access to (plus the master). If these are separate sites or domains then this is going to be a problem - you might find half your maintenance problems will be due to access to this share.
b) You need some databases to remain on the older versions and some up-to-date - you could use transactional replication (or snapshot replication) to achieve this by simply not including some databases as subscribers, and adding and removing these as you see fit. But this seems like a lot of manual work.
3) I'm not sure what effect modifying a schema 'offline' then re-adding it as a subscriber will have on replication - I suspect if the schema / tables within it change shape (# of columns, constraints etc) then you'll get errors. I might be wrong here.
Perhaps you could set up replication for those customer dbs that NEVER need to be taken 'offline' and changed and that must be kept up to date, then modify your existing scripts to cover the rest?

davidgray SSC Eights! Points: 860 More actions · Answer 3

Hi,

Thanks for the info.

I tried this at home over the weekend using snapshot replication, seemed to work just fine for meta-data and some reference tables.

I backed up the database and restored as in a different instance and added/changed some data. I then restored this changed database

to it's original location using the 'keep replication' and it syncronised just fine.

Thought I was in business until I came back in this morning and re-started the laptop only to fine replication was not enabled and the publications/subscriptions had vanished. I did however 'cheat' when setting this up and use the built-in NTAUTHORITY and impersonate accounts - bad practice I'm guessing?