June 13, 2012 at 7:06 am
Hi All,
I'm currently working on a project to scale out data to an additional new server in order to allow an analysis team an area to perform complex ad-hoc queries without impacting normal production activities.
This covers about ten different databases, with a varying range of tables in each and not all the tables in the source will be required to exist on the second server.
The data itself only needs to be accurate to the previous day, and the frequency of update of the source databases varies from very infrequently, through to monthly updates and finally to normal OLTP processing.
I'm trying to decide on the best methods to perform the update of the data on the new server.
To me, and this may be wrong, the two methodolgies that look to be the most sensible to use are Snapshot replication or the SSIS Transfer SQL Objecst Task. I was just wondering if anyone has any pros and cons for each method, or any other comments that may help in selecting a method to carry out the transfer of data?
Regards
Matthew
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 13, 2012 at 9:52 am
Can I ask, why you are considering snapshot replication and not transactional? The concern around snapshot being that your data will be completely unavailable on the subscriber as the destination object will get dropped and then rebuilt from the ground up. It's also very inefficient to frequently transfer large amounts of data when you can just feed the insert/update/deletes that happen.
June 13, 2012 at 10:14 am
Thanks for the reply!
Some of the reasons for not using transactional replication is that a number of the databases that are only updated monthly use the simple recovery model (should have mentioned this in my initial post, sorry) as they are only updated in bulk once a month.
For those that aren't, if I'm only intending on updating the replicated data once overnight and not continually throughout the day, how efficient would it be to set up the distribution agent to only apply the transactions on a 24 hour basis? Will that result in a quicker synchronisation than snapshot, or, like many things, does that come with a caveat of it depends on how many updates take place?
So to clarify, I pretty much have three types of dataset that will need transferring over:-
1) Completely static databases.
2) Databases that are in the Simple model, that have once a month bulk updates.
3) Regular OLTP data, from databases in the Full recovery model.
For situation 1, I'm thinking a simple restore of the existing databases on the new server and drop any objects that are not required.
For situation 2, I'm considering the restore as above, but then duplicate the bulk load process on the new server.
I think it's the third section that I'm unsure of at this point, but if using transactional replication with a once a day distribution works, then that may be the option?
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 13, 2012 at 10:24 am
You can certainly schedule replication to only apply updates at certain times of the day, just be aware if you do this that it will most certainly cause your transaction log to grow as replication will need to mark transactions replicated. If you want to provide point in time it may be worth replicating the data constantly and then performing a nightly snapshot on the subscriber that users can use to report against.
Don't forget, you can use transactional replication with databases in simple recovery, so that's always an option.
June 13, 2012 at 10:31 am
Ah I didn't realise you could use transactional replication with the simple model.
Unfortunately, we have the Standard edition of SQL Server, so snapshots are unavailable. 🙁
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 13, 2012 at 11:11 am
Gah, hate it when that happens.
Yes, replication is recovery model agnostic. Just be aware that non-replicated transactions will prevent log reuse so can lead to log growth problems.
June 13, 2012 at 11:57 am
Heh, it's just yet another thing on my list of why upgrading to enterprise would be advisable.
I may just float the idea that regular transactional replication is effectively what they are querying against currently on the one server, and is probably going to work out a better option due to log growth issues.
Thanks for the suggestions, I'll post back with how I get on.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 13, 2012 at 2:56 pm
Good luck
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply