DataCenter

How to setup a server to read log files nightly.

So you’ve been tasked with setting up a quick and dirty reporting server.  The goal is to restore the log files from the production server to the reporting server nightly.

 The backups are simple, use the SQL Maintenance plan to backup the logs, and then copy them to the remote machine.  But how do you restore the logs to the reporting server nightly.

 Well I’ve got a two step SQL job which should help you out.

Step 1 kills all current sessions in the database, and step 2 does the actual restores.

The code for step 1 is: 

declare @spid varchar(20)
declare cur CURSOR FOR
select spid
from sys.sysprocesses
where dbid = db_id(’Your Database Name Here’) /*<—Put your database name here*/
and spid > 50
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
exec (’kill ‘ + @spid)
fetch next from cur into @spid
END
close cur
deallocate cur

The code for step 2 is:

create table #Files
(FileName nvarchar(4000),
Depth int,
IsFile bit)insert into #Files
exec xp_dirtree ‘d:\’, 1, 1

I hope this makes your process easier. Now this code only works on SQL Server 2005 and up as the system stored procedures which I use were not included until SQL Server 2005.

Denny

Log Shipping without SQL Server Enterprise Edition

Microsoft’s Log Shipping is pretty good.  But it requires that you have SQL Server Enterprise Edition on both the machines.  This makes the solution fairly expensive.  Because of this I’ve written a replacement which can be used on any edition of SQL Server including SQL Server Express Edition (as long as you use a different scheduler).

I recommend keeping the drive letters the same on the two machines, but this isn’t required.

First backup the database to log ship and restore it to the backup server using the WITH STANDYBY option.  You are now prepped to start shipping the transaction log.

Setup a job on the primary server which backs up the log every few minutes to a network share on the backup server.  I use code alone these lines.

backup log Northwind to disk='\\backupsql\BackupFolder\northwind.log' with NOINIT, NOSKIP, NOFORMAT
go

Add a second step to the job which uses osql to start a job on the backup server.  Something like this.  (The on failure action should be to Quit with Success for this step.)

osql -S BackupSQL -E -Q "msdb.dbo.sp_start_job 'Restore Northwind Log'"

Setup a restore job on the backup server (making sure that the name matches the job name in the second step of the backup job.  The restore job will have four steps in it.

Step 1 (T/SQL):

/*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 = ‘Northwind’)
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


Step 2 (OS Command):

del d:\RestoreFolder\Northwind.2.log
REM /*This removed the last file we processed.*/

Step 3 (OS Command):

move d:\RestoreFolder\Northwind.log d:\RestoreFolder\Northwind.2.log
REM /*This moves the current file into place for processing.*/


Step 4 (T/SQL):

declare @i int
declare @j int
set @j = 1
set @i = 0
restore headeronly from disk=’d:\RestoreFolder\Northwind.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 Northwind from disk=’d:\RestoreFolder\Northwind.2.log’
        WITH FILE = @j,
            STANDBY = ‘F:\MSSQL\Backup\RMDBArchive.sby’ /*This keeps the database in standby mode ready for more restores.*/
    set @j = @j + 1
END

If your folders don’t exist between the two servers you’ll need to add the MOVE parameter to the restore commands.

If you do being using this please post a note to the comments if you can.  I like to know who is using this log shipping code.

Make sure that your environmentals are setup correctly

When you setup your data center, something that seams to be getting overlooked these days is the data center environmentals.  We all know that we need batteries and a generator for the power to keep the servers online, and that we need AC to keep them cool.  But I’ve seen a disturbing number of very large data centers who do not have the AC on the generators.  This creates a problem when the power goes out.  While it’s great that the servers will stay online the data center will quickly get up into the 100+ degree range (F).  This can quickly lead to data integrity issues is the hard drives start to fail.

 Just a couple of days ago a client of mine who hosts there servers at a company (who shall remain nameless) and their power when out for about 3 hours.  The temperature went up to 120 degrees in the data center, because the AC system which they brag about having wasn’t on the generator.  They actually called all their clients asking the clients to power down any systems which aren’t mission critical in order to save heat.  Frankly, I was surprised that the machines didn’t start to power them selves down.  I guess the HP servers are built a little more robust than I thought (not that I really want to try again).

Denny

What is the BACKUPTHREAD wait type?

If you have ever seen the BACKUPTHREAD wait type in the sysprocesses table or sp_who2 output and wondered what it is, I have found the answer.

The basic explanation is “Used when waiting for a backup thread to complete.  Wait time may be very long (minutes, hours).”  Basically what this means is that there is a backup running and something is waiting for it to complete.

When i saw this show up I was running a restore.  That restore had three entries in the sysprocesses table.  The first was the main kpid, with two child kpids.  The parent kpid was the wait type of BACKUPTHREAD while it was waiting for the child kpid to finish processing.  In my case the wait time was short, and it seamed to switch from this wait type to an IO wait type.

Denny