Is replication an option?
-
Hi, I have a CRM database that I can not add triggers, columns, or anything to. The vendor will not support/tolerate any additions and the firm I work for plays by the rules. We want to warehouse this data and make it available in a data mart but also require quick updates. My basic question: Is this database a candidate for transactional replication? Nothing can be added to it so if setting it up as a subscriber alters it in any way it won’t work. Will replication add tables and or other objects to the database I want to pull from?
SQL 2012 Standard VPS Windows 2012 Server Standard
-
In my experience replication can make changes to the table structure - adding a unique key to some tables if one does not exist.
-
You could always go for the 'cheap' option of copying backups onto a reporting database once/twice a day.....if 100% uptodate is not necessary.
-
Transactional Replication won't add or change anything in the database but it does require that the tables being replicated have a primary key.
It will also install a Distribution database and a few agents for the replication processes and corresponding jobs.
Keep in mind that any updates and/or changes to schema that come from the vendor will need to be applied to the publishing database with replication as a consideration. This may be an issue with the vendor even though technically, you aren't making any changes to their database.
If replication doesn't look like a good fit, Log Shipping to a Standby may be a solution.
Good Luck!
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Thanks for these ideas. I looked into log shipping but something I read made me cringe a bit; when it comes time to apply the logs to the destination database the log shipping process will fail if there are any connections to said database. In my situation there certainly will. The solution is to enable 'Terminate users in database'! That won’t work! I'd lose my job!
I confess there is one aspect I don’t quite understand about log shipping. My book discusses a Database Load State as being either in 'standby mode or No recovery mode. I require Standby mode so that users can read the contents. But is this mode selection independent of Terminate users? I’m guessing yes but am hoping if the database is in standby mode as read only the log shipping won’t kick off users.
Replication is looking more and more like the solution if our vendors DB has the appropriate keys. As long as I set the distributor on a separate server than the publisher I have not read anything other than the primary key requirement indicating the publisher database will be altered.
SQL 2012 Standard VPS Windows 2012 Server Standard
-
No matter which mode, Log Shipping needs exclusive use of the database to load the logs so users can't be connected at that time.
Putting the distributer on a different server is a common and recommended practice. It helps performance on the publishing server.
If you are going to go the replication route, I recommend this book:
"A Guide to SQL Server 2000 Transactional & Snapshot Replication" by Hilary Cotter. You can find it at http://www.nwsu.com
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
I'll take a look. I've found replication to be a bit unreliable (in the sense that it has to be monitored frequently and reset). I admit I have not read a great deal about it, merely gotten through the wizards and lightly read books online. I'll take a look at the book you suggest.
The schema we want to replicate does not have primary keys on all tables so straight forward replication is not an option. But, of course, I have a couple of ideas:
1) Partial Replication - get on the tables with primary keys and manually get the tables without the keys. Not sure how just yet.
2) Log shipping to a copy of the target database and replicate from that. So DatabaseA is the daily operational, DatabaseB is the log shipped target that no on logs onto and also a replication publisher. DatabaseC is the production target and subscriber.
Neither of these sound likely but just curious what you all think.
SQL 2012 Standard VPS Windows 2012 Server Standard
-
"I've found replication to be a bit unreliable" If you stick to the rules, it works quite well.
1) Partial Replication - I think this would be nightmare to keep insync if not impossible.
2) Log shipping to a copy of the target database - sounds plausible but a lot of work.
I realize that management wants data accessible whenever. Although technology has come up with some amazing solutions, it can't yet provide everything on the "wish list"
You are limited here by the vendors restrictions which, in turn, limits your options. Try speaking with whomever to find out what amount of latency is acceptable and work out windows when logs can be appplied. MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
I had to do this once to a vendor database. What I did was confer with the vendor and got an approval to add Identity columns to the tables that didn't have a primary key. We made the Identity column the primary key and then published the database using Transactional Replication. It impacted nothing in our case and solved our issue.
Mark
-
these are some great replies...appreciate it. sometimes at work i feel like i'm the only one representing the limitations of technology in the face of business needs.
the business pays my salary. its my job to serve them not the other way around that's how i see it. But I will do my best to articulate the latency issue. This paticulare system, however, is supposedly a must have. I wonder tho....
Lets suppose for a momment that i introduce a new assumption about the target DB database B; it's a report only system with only DB_A updating it. The only concerns now are latency and end user availability on DB_B? Let's also assume only one source DB is being read for updates driving the changes on B. Why cant the ETL issue NOLOCK UPDATES to solve the availibility issue? Noone will ever be denied access to A at worst case they will read old data. Who cares? by the time they refresh 'said application' it will be updated.
The other concer, latency, is now a function of how quickly changes can be evaluated and made.
I should say that this is a rather small DB and its a fast inranet.
SQL 2012 Standard VPS Windows 2012 Server Standard
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply