September 1, 2011 at 9:09 am
I have have just become a dba after being promoted and have been left a few solutions that I would like to change the main one being that the reporting server is replicated to but I don't think replication is the correct solution for a reporting server as:
* replication simply doesn't fail gracefully (locks the log file, expands the distribution db)
* prevents the devs from changing primary keys
* have to create a new publication for every table I want to add - etc
* leeches bandwidth
* etc
so I was looking for an alternative and ETL presented itself. I have read about Change Tracking (also CDC but that seems excessive) and have a test scenario set up working well in a virtual machine (Using SQL scripts I wrote last night instead of the Synchronisation Framework which I'm yet to dig into) but I was wondering how others experience had been with it under load, does it slow down data changes much when presented with a stressful environment (it seems to use triggers in the background).
Any help would be much appreciated - thanks.
September 1, 2011 at 9:21 am
I had to smile reading your post - stopping devs changing things!! An extremely good practice to my mind.
Anyway CDC and replication are totally different and I can't really see at any view you could change one for the other.
Replication is often used to maintain a DR copy of your server/databases. You don't indicate why the server is replicated.
I'd really suggest you read up on replication and/or get onto a training course to go with your "Promotion" as your questions show a fundamental lack of knowledge, I'd probaly guess you're coming from a developer /programmer background.
So to your points: Yes replication stops what you say, articles have to be changed - you can do this programatically without having to drop and recreate replication. I've never found replication a bandwidth hog, unless your network is very narrow bandwidth, the distribution database should manage itself - it's a gateway if you like to manage the changes on your database(s) and make sure the other databases receive the data.
CDC this is best described as a way to track changes, or audit activity - it's very useful for DW teams when receiving data from transactional systems to optimise data loads.
Good luck.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 1, 2011 at 9:31 am
Don't reject replication out of hand. It sounds like your environment is changing rapidly so any solution is going to have to be changed to reflect those changes. At least replication has administrative tools to manage it. You could end up coding a solution using change tracking and then every schema change could require you to adjust code.
Don't make work for yourself. Minimise your admin so you can spend more time doing interesting work.
Depending on your environment you could just take a daily snapshot to your reporting server. It worked for me in a role where latency wasn't a concern.
September 1, 2011 at 9:57 am
Replication was used as bandwidth was and still is at a premium between where the live SQL servers are and here where the UAT and reporting servers are. The previous DBA needed to provide a reporting SQL server and a development SQL server (updated daily) as the reporting server needed to be relatively up to date (although not complete) and the Unit Acceptance Testing database needed to be updated once a night and be almost complete (except sensitive info) replication was used as it is capable of both and being reasonable on bandwidth (however not enough for this building and changing that is not an option just yet)
The UAT environment has almost solved itself as I am allowed to move it with the live SQL servers.
The BI team say they want to implement a ETL solution using BCP to transfer the data so that they can develop a custom database for reporting (that is Microsoft recommended) but to enable that they need a way of tracking the changes on a table. The BI Team have presented me with a document that shows how a solution for CDC and that it is the Microsoft suggested solution however I have read the same about Change_Tracking. CDC seems to track a lot more detail than is necessary but Change Tracking holds just the details needed for that. All this works fine in my test environment so I was wondering how others experience was with Change_Tracking and how detrimental it was to the performance of a server?
September 1, 2011 at 10:19 am
Cheers Gary I appreciate how replication gracefully handles new columns etc, however changes needed to be made with the ETL setup would be made by the BI team so thankfully is out of my hands and is what they have requested. The environment is rapidly changing as it was initially built within a short schedule when the company was started but some success has meant that we have call for the infrastructure to handle more bespoke solutions (for a small percentage of our clients).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply