Guide to Installing SQL 2000 and SQL 2005 on the same machine

You can install SQL 2000 and SQL 2005 on the same machine.  I always recommend that they be installed in this order to try and get everything working as best as possible.

  1. Install SQL 2000
  2. Install SQL 2005
  3. Install SQL 2000 SP4 (Or the latest service pack)
  4. Install SQL 2005 SP2 (Or the latest service pack)

Denny

Contact the Author | Contact DCAC

Virtualizing SQL Server? When to and when not to.

Virtualizing servers is huge right now.  And thanks to VMWare it is very easy to build virtual machines.  In most of IT virtualizing servers is a good think.  In the database world, that isn’t always the case.  As we know SQL Server uses a lot of memory and CPU resources.  If you try to virtualize a high load SQL Server you could end up with all the resources of the virtual server being used by the single virtual server.

 Now don’t get me wrong.  If you have a small server with low load which has a SQL Server on it, such as a Blackberry Enterprise Server, that may be an excellent server to virtualize.  Your production CRM database, probably wouldn’t be a good server to virtualize as it probably needs more CPU and memory resources than your Virtual server would be able to afford to give it.  Now that said; if your VM Servers are huge servers with hundreds of Gigs of RAM and lots of CPUs (such as the new HP DL 580 and 585 models) and rack space is tight, then virtualizing SQL Servers might be a good plan provided you can deal with any disk IO issues.

In addition to the CPU and memory concerns there are the disk performance issues to look into.  Typically when setting up a virtual machine, the disk drives are also virtualized.  This means that to the virtual server host the virtual machines disks are simply files stored on the file system.  This probably isn’t the best way to store SQL Server databases (or any system which uses the disk subsystem a lot).  If you can setup your disks to be mapped directly to physically SCSI arrays you can remove this potential problem.  Check your VM solution to see if this is supportd (VMWare does, I don’t know about Microsoft Virtual Server).  If you can provide information about this feel free to post as such in the comments.

 Denny

Contact the Author | Contact DCAC

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='\backupsqlBackupFoldernorthwind.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:RestoreFolderNorthwind.2.log
REM /*This removed the last file we processed.*/

Step 3 (OS Command):

move d:RestoreFolderNorthwind.log d:RestoreFolderNorthwind.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:RestoreFolderNorthwind.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:RestoreFolderNorthwind.2.log'
        WITH FILE = @j,
            STANDBY = 'F:MSSQLBackupRMDBArchive.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.

Contact the Author | Contact DCAC

Temp Tables, Table Variables, and CTEs

There are some major differences between temp tables, table variables and common table expressions (CTEs).  Some of the big differences are:

Temp Tables vs. Table Variables

  1. SQL Server does not place locks on table variables when the table variables are used.
  2. Temp tables allow for multiple indexes to be created
  3. Table variables allow a single index the Primary Key to be created when the table variable is declared only.  There is an exception to this, that if you can create the index inline, for example by creating a unique constraint inline as shown in the comments.  However these indexes (and the table variable in general) will always be assumed to have 1 row in them, no matter how much data is within the table variable.
  4. Temp tables can be created locally (#TableName) or globally (##TableName)
  5. Table variables are destroyed as the batch is completed.
  6. Temp tables can be used throughout multiple batches.
  7. Temp tables can be used to hold the output of a stored procedure (temp tables will get this functionality in SQL Server 2008).

Table variables and Temp Tables vs. CTEs

  1. CTEs are used after the command which creates them.
  2. CTEs can be recursive within a single command (be careful because they can cause an infinite loop).
  3. Table variables and Temp Tables can be used throughout the batch.
  4. The command before the CTE must end with a semi-colon (;).
  5. As Temp tables and table variables are tables you can insert, update and delete the data within the table.
  6. CTEs can not have any indexes created on them, source tables much have indexes created on them.

If you can think of anything that I’ve missed, feel free to post them in the comments.

Denny

Contact the Author | Contact DCAC
1 2 3

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers