Sync a single database on another server

Question

Post reply

Sync a single database on another server

mwinn

Old Hand

Points: 332
More actions
June 15, 2011 at 3:27 pm

#242123

I don't know if this is possible but I'm going to ask anyway. Right now we have an SQL database (SQL Server express 2005) that is used for our Cencon locks. This is a mission critical data base for us.
What I need to know is, is it possible to sync this database with another database I created on a different server as a backup? I can always do a restore from a backup copy of the database, but I was hoping that there was a way to automatically sync them at regular intervals.
I looked at doing a cluster server but this is the only thing that we need to sync, so the expense out weighs the benefit.
Thanks,
Michael D. Winn

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Log shipping

Or create an ssis export package that copies the data over to the secondary server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

SQLRNNR (6/15/2011)
Log shipping
Or create an ssis export package that copies the data over to the secondary server.

Unfortunately neither of those is available to you using SQL Express. Without either of those this gets a lot messier.. Or you can do restores..

CEWII

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

It reads to me that one server is Express and that the other is a server.

Either case, you should be able to get SSIS to work by installing from a SQL Server install and installing BIDS.

Another option is to check Codeplex for a data compare utility or use that of RedGate.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

SQLRNNR (6/15/2011)
It reads to me that one server is Express and that the other is a server.
Either case, you should be able to get SSIS to work by installing from a SQL Server install and installing BIDS.
Another option is to check Codeplex for a data compare utility or use that of RedGate.

I didn't read it that way, but ok..

Here is an article on log shipping with express, sort of..

http://blog.willbeattie.net/2009/07/log-shipping-in-sql-server-express-2008.html

CEWII

mwinn Old Hand Points: 332 More actions · Answer 5

Thanks for the info, the last option only works on server express 2008 and according to the blog is not backward compatible. If I upgrade from Express (I have express on both servers) to a full server I should be able to do the type of sync your suggesting, right?

Thanks,

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

mwinn (6/15/2011)
Thanks for the info, the last option only works on server express 2008 and according to the blog is not backward compatible. If I upgrade from Express (I have express on both servers) to a full server I should be able to do the type of sync your suggesting, right?
Thanks,

Both SSIS and log shipping are available in standard edition, per:

http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

CEWII