Mirror Image

[jdam]

I have 1 online sql server hosting about 30 different DB's and 40 to 50 logins.

I want to have a second SQL server as a mirror (or close to) image of this server for failover. Maybe update the second one hourly etc.

What is the best way to do this?

Also, I have identity data in tables in the online DB's and need to maintain the ordering etc of this data when it comes across (append an identity field without loosing the identity?)

ANy suggestions.

 

[mrdenny]

Log shipping will probably be your best bet.

Microsoft incldues there's with SQL Server. You can also check out the FAQ I wrote which covers writting your own log shipping faq962-5754.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jdam]

I am currently using Distribution and Subscription services scheduled to run every 12 hours.

The next issue is Increment data (autonumbers)

Replication converts the autonumbers to INT. I need to maintain the identity data or have a script to re-seed the column back to identity field.

Any suggestions?

 

[mrdenny]

When you use replication SQL will assign blocks of numbers to each server in the replication schema so that the numbers aren't reused.

If you use Log Shipping this isn't an issue.

Replication shouldn't modify the data types of your identity columns.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jdam]

I'm trying to get the log shipping to work using the code from mrdenny's FAQ.

2 things

on the primary server the step 2 command fails saying the secondary server is not found or failed to authenticate.

second thing

the restore log code generates errors:

Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 1873000000019800001, which is too late to apply to the database. An earlier log backup that includes LSN 1822000000032500001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

ERROR MESSAGES WHEN RUNNING THE RESTORE OF THE LOG FILE:

/*This first part of the code ensures that no one is using the database that we are about to restore. If we don't do this then the restore will fail.*/
declare @spid as varchar(10)
declare @CMD as varchar(1000)
declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid =
(select dbid from sysdatabases where name = 'DOWWEST')
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
set @CMD = 'kill ' + @spid
exec (@CMD)
fetch next from cur into @spid
END
close cur
deallocate cur
go
exec xp_CMDShell 'del b:\BACKUPFOLDER\DOWWEST.2.log', no_output /*This removed the last file we processed.*/
exec xp_CMDShell 'move b:\BACKUPFOLDER\DOWWEST.log b:\BACKUPFOLDER\DOWWEST.2.log', no_output /*This moves the current file into place for processing.*/
declare @i int
declare @j int
set @j = 1
set @i = 0
restore headeronly from disk='b:\BACKUPFOLDER\DOWWEST.2.log' /*This tells us how many transaction log backups there are in the file that we need to restore.*/
set @i = @@ROWCOUNT
while @i+1 > (@j) /*This loop runs us through the file restoring the logs in order.*/
BEGIN
restore log DOWWEST from disk='b:\BACKUPFOLDER\DOWWEST.2.log'
WITH FILE = @j,
STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL$OFFLINE\BACKUP\DOWWEST.sby' /*This keeps the database in standby mode ready for more restores.*/
set @j = @j + 1
END

Any suggestions?

Or does this only work with Enterprise edition (i've got standard edition)

Other than that the rest of the process is working.

 

[mrdenny]

Sounds like someone or something else took a transaction log backup.

My log shipping code will work on MSDE, Standard, or Enterprise.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jdam]

I took some time off of this issue and am back at it again.

I'm trying to implement the Log Shipping as outlined in the FAQ from Mr Denny. I'm running into a snag... error message reads:
BackupDiskFile::CreateMedia: Backup device '\\Emergency\BACKUPFOLDER\repltester.log' failed to create. Operating system error = 1385(Logon failure: the user has not been granted the requested logon type at this computer.).

Looks like a permissions issue on the BACKUP server. If I change the machine path to the IP address IE \\192.168.2.54\backupfolder\repltester.log I get a more precise error message regarding authorisation.

Any suggestions?

Both machines are 2003 standard edition with SQL 2000 standard SP3a

 

[mrdenny]

OS Error 1385 is just as it says. The account that you are trying to use to get to the network share doesn't have the right to connect to that machine over the network.

This can be an inhearitated setting from a GPO on the domain or the OU or a local security setting.

Since the standard policy is for all domain users to be able to connect to a network share, I'd have to assume this policy setting was comming to your server emergency via the domain or an OU policy.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jdam]

Thank you.

Restarted both SQL servers under the same login. Now all good... EXCEPT:

On the backup SQL server when the Restore Log job tries to run I get the following error:

sysdatabases is an invalid object.

if I change the code to master.dbo.sysdatabases I get the following error:

Executed as user: FOREFRONT\pvdowwest. Cannot use KILL to kill your own process. [SQLSTATE 42000] (Error 6104) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed.

Any Suggestions?

 

[jdam]

Fixed this up now the next problem.

When the restore operation runs I get a failure error.

Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 1873000000019800001, which is too late to apply to the database. An earlier log backup that includes LSN 1822000000032500001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

How can I confirm the log is good before trying to load it?

 

[mrdenny]

The job steps should be setup to run in the master database not the database you are restoring.

Sounds like a log file got deleted while you were dealing with some of the issues.

You'll need to apply a new full backup then start shipping the logs.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jdam]

thanks I'll give that a try.

 

[jdam]

Is there also a way to shrink only the log file on the main server after it is exported over to the backup server? Just want to make sure the log's don't get to big.

 

[mrdenny]

No. If you shrink the log file on the main server, when the next log file is shipped to the backup server that log will enclude the command to shrink the file on that server as well.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com