0

Currently they have a single SQL 2000 Std install on a server that needs to be retired. They run an app in terminal services to manipulate the data.

So I'm looking at two new servers with SQL 2008 (Workgroup I hope..) one on site, one off connecting via an IPsec tunnell. The connection is not in place yet. Each server will run terminal services and the locally installed app will be setup to use it's local SQL instance.

So someone can log on to Server1 via TS and access the primary SQL server. Or log on to Server2 via TS and access the backup SQL server. They simply launch the app same as they always do.

So I need to keep server2 up to date so server1 can blow up and I just need to tell the users to connect to Server2. Then they can go on with their day.

When server1 was repaired I would bring it online probably manually verify the changes from server2 came across. And move everyone back over to 1 again.

Well in a perfect world that would happen lol How close can I get?

While I do need 2-way synch it's not an everyday thing and changes should generally be limited to one side a time plus the user base is small.

I'm not a SQL expert, so I really want to avoid an massively complex process everytime we have to cut back and forth.

sysadmin1138
  • 133,124
  • 18
  • 176
  • 300
Kenny M.
  • 1

2 Answers2

1

If you require both servers to be accessible at all times and to replicated records back and forth I would encourage you to look into Merge Replication.

There is no downtime with that setup but it does pose some other challenges.

How Merge Replication Works: http://msdn.microsoft.com/en-us/library/ms151329.aspx

Merge Replication Overview http://msdn.microsoft.com/en-us/library/ms152746.aspx

artofsql
  • 396
  • 1
  • 3
0

I think Log Shipping is what you are looking for here. Edwin Sarmiento wrote a very good chapter (#35) on this very topic in the SQL Server MVP Deep Dives book bbased on this blog post listed below.

http://bassplayerdoc.blogspot.com/2007/10/your-poor-mans-sql-server-log-shipping.html

http://www.thirdtier.net/2010/05/your-poor-mans-sql-server-log-shipping-powershell-version/

It may look a bit daunting at the beginning but once you understand the concepts of Log Shipping, it is very straight forward. Edwin did a very good job in that chapter and you will find that very useful.

Sankar Reddy
  • 1,374
  • 8
  • 8
  • Thanks I'll read the first url in more deatil later but looks like a great read. But in the event of an outage Would I need to take the second server out of a read only state? And then I assume reverse the log ship before bring the repaired server back on the network so the primary can catch the changes? – Kenny M. Apr 20 '11 at 19:59
  • Yes, your understanding is correct. – Sankar Reddy Apr 20 '11 at 20:32
  • Is full auto 2 way log shipping possible possible? or am I just asking for trouble? – Kenny M. Apr 20 '11 at 21:27
  • You have database Mirroring to do that but its Standard & Enterprise Editions only. http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx – Sankar Reddy Apr 20 '11 at 21:46
  • I spoke to them today, so I got the money out of them to sql 2008 std here's a quick layout I put together based on a digram from MSDN. http://img541.imageshack.us/img541/8736/keltdblayout.jpg – Kenny M. Apr 26 '11 at 02:56