March 29, 2009 at 10:14 am
Hello,
I am new to SQL Server replication (well not new but have avoided it since SQL Server 7 SP4) and I have a need to setup replication of some sort and want to make sure I do so without making it an administrative nightmare. I have two SQL 2005 instances to replicate to a single SQL 2008 instance. Our database structure is such that I have countless project specific tables with different schemas and with daily schema modification on at least one of them (don't ask. . .). Fortunately real time replication isn't a need I intend to replicate data once per day at 12:30 AM but it needs to be available by.
Other than being called insane I'd like to hear any constructive approaches you might have, gotcha's to avoid and where I can go to read more.
Thanks in advance!
Jerry
March 29, 2009 at 10:38 am
As always, it depends!
What are your goals for this replication ?
What's the db relationship for your 2 sql2005 instances ?
(duplicates or not) and how are they duplicated at SQL2008 ?
- One way replication ?
- Is backup sql2005 + restore 2008 enough for you ?
That would be the "easiest" way and may avoid the replication hassle.
- are there also being made schema changes at sql2008 level outside of the replication scenario ?
That may give conflicting schema chages.
- Will the sql2008 db be read only copies ?
- Is it only ddl changes you seek ? (Service broker may come in handy) What if those changes also need data modification ?
- are you replicating full user databases or do you only need some objects to be replidated ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2009 at 10:44 am
One way replication will be fine. I want to the SQL2008 instance to only be a replica of the two source instances.
Backup would be an awesome option for me but it takes too long to run. I'm working to try and make this a better option but its not looking good. Overall I've got 1.1 TB of data to restore on the traget.
If prohibiting schema changes on the target would be best I can certainly do that.
Preferably I'd be able to make modifications to the data on the SQL08 target BUT it's not a requirement. Is this something to avoid?
I need to replicate both ddl and data to the target instance.
Thanks for the reply!
March 29, 2009 at 1:11 pm
you could opt to compress your .BAK files (zip or rar).
This way you would end up with about 25% of your backup file sizes.
With that volume (1TB) spread your file copies or you may overload your network to notifiable proportions.
You may even opt for a DBMirroring solution, but that depends on the number of databases and the number db load on your sql2005.
btw I don't know if dbmirroring 2K5 to 2k8 is supported (I didn't test it)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2009 at 2:49 pm
ALZDBA (3/29/2009)
you could opt to compress your .BAK files (zip or rar).This way you would end up with about 25% of your backup file sizes.
With that volume (1TB) spread your file copies or you may overload your network to notifiable proportions.
This is not really a factor its not the size of the DBs as much as the amount of time it actually takes to back them up. I use SQLSafe to backup and compress the files then push them off server anyway. It seems to work well but at last check it takes aoubt 6 hours to backup the data. . .too long.
You may even opt for a DBMirroring solution, but that depends on the number of databases and the number db load on your sql2005.
btw I don't know if dbmirroring 2K5 to 2k8 is supported (I didn't test it)
I was wondering that as well. I've seen a bit on DBMirroring but doesn't it require a witness and automatic fail over of some sort? I won't care about any of that I just want to be able to copy my DBs.
March 29, 2009 at 8:38 pm
Database mirroring would be a good choice EXCEPT that your target is SQL 2008 and your source is SQL 2005. You can't mirror between versions of SQL Server because there are structural differences.
FYI : The RESTORE command handles these but it is one way (i.e. you cannot backup a SQL 2008 database and restore to SQL 2005). Similar scenario if you detach/attach.
And, you do not need to have a witness server unless you are using Standard Edition.
Transaction replication looks like your best option. It is able to replicate schema changes provided that you tell replication to do that (which is the default, anyway) and the schema changes are done using ALTER TABLE commands. New tables are not automatically replicated. You will need to implement code to handle this. Similarly, dropping tables will need code to handle it (on both ends since dropping a subscription will leave the table on the subscriber).
I don't know whether you need to worry about security but replication can replicate permissions to the replica database. However, you do need to make sure that the users exist in the replica database (replication does not do this and will fail if the user doesn't exist). The option is replicate permission changes is configurable so if you don't need it, I would not turn this on.
Snapshot replication would also work except you may run out of time to copy the data around.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply