September 16, 2008 at 1:55 pm
I have 2 SQL 2005 Enterprise servers running in a cluster located in Ohio and I have a Standalone 2005 SQL 2005 Enterprise server running in Florida. All the systems are running on Windows 2003 R2 servers. I am new to setting up replication in SQL and I wanted to know what I need to do to set this up. The Ohio cluster servers are my PRD systems and the Florida standalone system is my failover system. I want to just have the databases copying from the Ohio server to the Florida server. One way data transfer. This way if any changes are made to the Ohio Production systems it will sync the data to the Florida server. Can anyone assist me in finding help in setting this up. I also wanted to know if the database syncing only sync's the databases or does it also do the schemas as well?
Thank you
September 17, 2008 at 3:47 am
there are several technologies to choose from
replication (eg Snapshot, Transactional)
mirroring (supported SQL2005-SP1 or later)
log-shipping
and your choice should be driven by business SLA's and risk assessments
Snapshot replication would ship everything (e.g. once/day)
transactional would ship all changes (after initial snapshot to sync)
mirroring also ships changes [option to be synchronous]
log-shipping is most efficient as is single file (say changes every 15 mins) meaning good for WAN (not round-tripped per row) and applied as one restore log operation.
L-S also gives you some better recovery potential (point-in-time), but suggest you arrange to copy across the *.BAK files too (e.g. daily db-dump by maintplan)
changes [ie not Snapshot] would normally ship DDL changes too
- see sp_addpublication @replicate_ddl in BOL for transactional repl
[font="Times New Roman"]Indicates if schema replication is supported for the publication. replicate_ddl is int, with a default of 1 for SQL Server Publishers and 0 for non-SQL Server Publishers. 1 indicates that data definition language (DDL) statements executed at the publisher are replicated, and 0 indicates that DDL statements are not replicated. [/font]
HTH
Dick
September 17, 2008 at 6:33 am
Dick.
Thanks for all the information.
I was wondering what you would recommend for my scnario? All I need to do is have the server in Florida pull the data from the server in Ohio and nothing more. I was thinking Transactional would be sufficient for this.
Any Suggenstions?
September 17, 2008 at 10:17 am
my steer is to use Log-Shipping which has these components
Source (Ohio)
normal tranlog job "LSBackup_db1" (say every 15 mins)
- write the *.TRN to a share (eg \\OHIOS1\SqlDumps)
Secondary (Florida)
copy job (say every 15 mins)
- pulls from source onto local volume
restore job (say every 15 mins)
- eg from local volume to local db (will disconnect any Klingons)
Monitor (eg NYC)
check job "LSAlert_NYC" (say every 45 mins)
- verifies that other jobs are working
so in your case Florida is doing the pulling and applying, and you main Ohio cluster is not significantly impacted (especially if you use a fileserver other than the SQL boxes)
Dick
September 17, 2008 at 12:31 pm
I spoke to a Microsoft SQL support guy today and he said that I need database mirroring setup. This way if I failed over to Florida and them I had to fail back to Ohio after a week the data will be in sync on both servers. Does this sound right? If yes can you please send me any links you have regarding Mirroring this way I can attempt to get it working on my test servers?
Thanks again
September 17, 2008 at 4:26 pm
try these URLs for starters [no, I don't necessarily agree with posters]
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=227&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=263505&SiteID=1
you should consider
volume of data sent (and speed of your WAN)
are changes synchronous [primary waits for secondary before can continue]
automatic failover/failback
your business SLA's
suggest YOU should do the evaluation and MS should not be telling you what YOU need to do! there are several alternatives and you should decide which is best (least bad?!) for your requirements
Dick
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply