July 7, 2004 at 10:58 am
Hi,
I have sql server 2000 standard version and win2k server. I want to setup a standby server. Seems the only choice is replication. But replication cannot meet my requirement perfectly (what I want is: updatable subscriptions. But replication will change some fields in the tables for the subscribers.) I can torlarent one-day latency. Database currently is small, though will keep growing. Right now I am thinking about making an exact copy of the database on another machine periodically.
The question is: how's my thinking? how can I make such copy on another win2k server? Is there any special tool I can use?
Thanks.
July 7, 2004 at 11:30 am
You can schedul a DTS job. Just right click on the primary database and select Export Data. Let the wizard walk you through the steps. Then when it asks you to save the package save it to the local server.
In the EM you can look at the package Data Transformation Services folder. And if you right click your package you can then schedule it.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
July 8, 2004 at 2:33 am
You could use a tool to synchronize both databases schema and data which would require little or no maintanence as opposed to the DTS solution. Check out http://www.dbghost.com
July 8, 2004 at 8:55 am
I am not suggesting this is the best approach, but I have a little Dos batch file running on the live server, scheduled to run once a week. It stops the SQL server services on the live server and the warm start server. It also stops a dependent service running on a different server. Then it copies all the databases to the warm start server. I use two free utilities that can be downloaded from the web (part of the Win 2k resource kit): netsvc.exe (stops/starts and queries services on remote servers) and sleep.exe (pauses the batch file).
I don't have SQL Server Agent running on the warm start server as I don't want it running scheduled jobs.
Here is the batch file: Callserver is the live server, callserver2 is the warm start server and svr-sql is the dependent server (running a service that accesses the SQL Server databases):
@echo off
echo This batch file copies the live SQL Server databases from
echo the CALLSERVER pc to the warm standby CALLSERVER2 PC.
echo
echo In order to do this it stops SQLServer running on both PCs
echo temporarily
echo
echo Created 15/04/04 by P.Tillotson
echo It uses free utilities netsvc.exe and sleep.exe that can be
echo downloaded from the internet.
echo -----------------------------------------------------------
echo stopping SQL Server service on CALLSERVER2
"c:\Callserver Backup\netsvc" MSSQLSERVER \\callserver2 /stop
echo stopping Homebank call scheduler on \\svr-sql
"c:\callserver backup\netsvc" CallScheduler \\svr-sql /stop
echo stopping SQL Server service on CALLSERVER
net stop SQLSERVERAGENT
net stop MSSQLSERVER
echo wait 30 seconds for the services to stop
"c:\Callserver Backup\sleep" 30
echo show current state of CALLSERVER2 and SVR-SQL services
"c:\Callserver Backup\netsvc" MSSQLSERVER \\callserver2 /query
"c:\callserver backup\netsvc" CallScheduler \\svr-sql /query
echo now copying the databases from callserver to callserver2
xcopy "c:\Program Files\Microsoft SQL Server\MSSQL\data\*.*" "\\callserver2\c$\Program Files\Microsoft SQL Server\MSSQL\data" /Y
echo starting SQL Server service on CALLSERVER2
"c:\Callserver Backup\netsvc" MSSQLSERVER \\callserver2 /start
echo starting SQL Server on callserver
net start MSSQLSERVER
net start SQLSERVERAGENT
echo waiting 60 seconds for SQL server to start properly...
"c:\Callserver Backup\sleep" 60
echo starting Homebank call scheduler on \\svr-sql
echo which needs SQLServer to be running on callserver
"c:\callserver backup\netsvc" CallScheduler \\svr-sql /start
I also have daily/hourly backup and restore jobs that copy important databases and transaction logs and sets those databases to Read Only on the warm start server.
Hope this gives you some ideas
Peter
July 8, 2004 at 10:04 am
Backup and restore.
July 8, 2004 at 6:59 pm
Hi, Thanks for your reply. I backup the database weekly. What I want is a updatable standby server. My version is standard and replication cannot meet the requirement perfectly (for example, restriction on 'text', 'image' fields). So I am thinking to copy the database to another machine every day. If the server is down, user can switch to the standby server, though we need to do config manually.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply