guidance for standby server

guidance for standby server

Post by kalpa » Wed, 06 Feb 2002 13:47:06



hello

My company using standard SQL Server7.now we want to create
standby server for any emergency that will be in dfferent
location.i wanted to know which is method better
transection replication  or standby server.
i have tried transection replication in that
subscription server i have to remove all constrain
of prymary & foreing key then only it work.
i wanted know that in transection replication we
have to remove this contrain or i am somewhere wrong.
for this reason i am not using this option.
so other is standby server if you help by guiding me how
to create standby server then it will be great help for me.

Thank you

kalpaj

 
 
 

guidance for standby server

Post by Keith Kratochvi » Thu, 07 Feb 2002 00:56:59


Transaction Log replication (log shipping) is the method that you use to
create a warm standby server.

Here is my "canned" response.  I am using this method with much success.
Others are also using this method:

It is a common misconception that you need SQL2k or the Back Office Resource
Kit to use log shipping.
If you read up on warm standby servers within Books Online, you will get
lots of good information.

It is easy to "roll your own" log shipping
the basics are
1. full database backup on primary server
2. restore database to secondary server WITH STANDBY
3. backup transaction log on primary server
4. restore transaction log to secondary server WITH STANDBY.

repeat steps 3 and 4 as often as needed.
You can bring your secondary server online if you restore WITH RECOVERY.

You can also automate the entire process.
This is the method that I used:

On my primary server, I have set up a linked server to the secondary server
(I replaced the server names with Primary and Secondary here)

Primary server:
--backup database job (nightly)
BACKUP DATABASE foo to dmp_foo WITH INIT


GO

The job on the primary server starts this job on the Secondary server:
--restore the database
exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
database
go
RESTORE DATABASE foo
FROM  DISK = '\\Primary\D$\dmp_foo.BAK'
WITH  FILE = 1,  NOUNLOAD ,  STANDBY = 'E:\mssql7\logs\undo.ldf', STATS =
10,  REPLACE ,
MOVE N'foo_Data' TO N'F:\MSSQL7\data\foo.MDF',
MOVE N'foo_Log' TO N'E:\mssql7\logs\foo.ldf'
go

Now, throughout the day, I backup the logs on the primary server:
BACKUP LOG foo to dmp_foo_LOG WITH INIT


/*I also zip and copy the log file to another server at this point.*/

The job listed above starts this job on the secondary server:
exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
database
go
RESTORE LOG foo
FROM  DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
WITH FILE = 1, STANDBY = 'E:\mssql7\logs\undo.ldf'

If you ever want to bring your secondary server online, you need to restore
WITH RECOVERY
RESTORE LOG foo
FROM  DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
WITH  FILE = 1,  NOUNLOAD ,  RECOVERY

/*
an article written on the subject that holds your hand a bit more than my
simple write up does
http://www.sql-server-performance.com/sql_server_log_shipping.asp
*/

--
Keith, SQL Server MVP


Quote:> hello

> My company using standard SQL Server7.now we want to create
> standby server for any emergency that will be in dfferent
> location.i wanted to know which is method better
> transection replication  or standby server.
> i have tried transection replication in that
> subscription server i have to remove all constrain
> of prymary & foreing key then only it work.
> i wanted know that in transection replication we
> have to remove this contrain or i am somewhere wrong.
> for this reason i am not using this option.
> so other is standby server if you help by guiding me how
> to create standby server then it will be great help for me.

> Thank you

> kalpaj


 
 
 

1. Standby database setup - Primary SQL7 SP2 Standby SQL2k.

All,

I am setting up a primary/standby database scenario and am hitting some
problems.

The primary database is SQL7 SP2 running on W2k advanced server (clustered)
The secondary database will be SQL2k running on W2k advanced server.

When I try to restore the database on the standby server using

restore database <different database name>
fom disk = '..'
with move ...,
replace, stats=2,
standby = '....'

I get an error saying that it cannot be restored and I have to use the
norecover option.

I have tried setting the dboption 'DB Name','pending upgrade' to true but
now I get the message

2. Memory problem with Clipper

3. Linked Server, Standby Server & sysservers table

4. graphics and text

5. Using a standby server for an events forwarding server

6. US-NC Paradox for Windows Programmer-6 month contract

7. syncing two database (standby-server / report-only server)

8. pgsql/src/backend/parser gram.y

9. Standby Server and Primary Server Tasks

10. Lagato Server Co Standby Server

11. Standby Oracle 8i server as report server - Error!

12. Oracle Parallel Server vs Partioning for Standby server