Mean Old DBA

Migrating SQL Servers

Derrick Leggett — Sun, 04 Dec 2005 19:40:00 GMT

I see a lot of question on the forums about migrating to new servers. At all the companies I have worked at, we have implemented server build processes to help with this process and also document needed DR procedures. For this post, I’m including a generic SQL Server Build Process and some nice scripts I keep on all the servers to assist in the process of migration.

SQL Server Build Process

Policy Statement

This document will be used to build all SQL Server Servers and instances at . All new SQL Server installations must follow this process and be documented accordingly.

Scope

This policy will cover the entire enterprise in regards to SQL Server installations. No installations of SQL Server will be completed without following this process.

This document does not cover the following:

1 Processes and policies for acquisition of hardware and/or software.

2 Process for physical server build or installation of the OS, drivers, etc. IT has a process in place covering physical server builds. All servers with SQL Server should be built according to the IT process coving SQL Server builds.

Build Process

Installation

Remote into the server to which the new instance of SQL Server will be added

Go to \\filshare\directory\SQL Server 2000 - Enterprise Edition (or CD/DVD) and run the autorun.exe

1 Install Components

2 Install Database Server

3 Install onto the local computer

4 Create a new instance of SQL Server

5 Install server and client tools

6 If creating a named instance, disable the default instance and rename

7 Choose to run a custom install only if the instance should have a collation different than standard collation. Use custom install if replacing or moving from an instance that had any other collation setting and use same collation.

8 Select to use the same account for each service (might vary by enterprise)

9 For Service Settings, select to use a Domain User account and provide the user account and password that will be used for SQL Server

10 For Authentication Mode, select Mixed Mode and provide the sa password if using mixed mode authentication. It's preferable not to use this at all.

11 If doing a custom install then disable named pipes and enter the properties for TCP/IP and change the default number to the desired port when prompted (if doing a typical install then go into Programs>Microsoft SQL Server>Server Network Utility to perform this step)

12 For Licensing Mode, select Processor License for x number of physical processors on box (might vary by enterprise if your company uses CAL licenses)

13 Install the latest service pack which can be found at \\fileshare\directory\sql2ksp3\setup.bat for each instance that has been installed.

14 If installing SP3a instead of SP4, install mdac2.7 – sp1 refresh which can be found at \\fileshare\directory\mdac2.7-sp1refresh {This needs to run once only, this is a server patch not a per instance refresh}

15 If installing SP3a instead of SP4, install the SQL Server 2000 818, \\fileshare\directory\SQL2000 – KB815495-8.00 {This needs to be run for ever instance that has just been installed}

If you do not already have this software installed on your fileshares, the downloads can be obtained at: http://www.microsoft.com/sql/downloads/default.mspx

configure logins

1 In Enterprise Manager, go to the Security>Logins under the Instance that was created

2 Add the \ADGroup group that will be used by the DBA staff to administer SQL Server and grant "System Administrators" access listed in the Server Roles tab {Once per instance}

3 Remove the BUILTIN\Administrators group. DO NOT do this before following step 2.

For additional security measures, www.sqlsecurity.com has a hardening document for restricting public access, tightening up network layer, etc.

Configure Server Administrators

3 Go to Computer Management>System Tools>Local Users and Groups> Groups

4 Open up the Administrators dialog box

5 Add the \ADGroup group that will be used by the DBA staff to administer SQL Server

COnfigure SQL Server to use Outlook

1 Install Outlook on the server if it is not already there

o \\fileshare\directory\SETUP.EXE

o Custom Install

o Deselect everything but outlook

2 Configure Outlook on the server to use the appropriate exchange server and the mailbox that was assigned by exchange group for SQL Server usage

3 Select “yes” when prompted to register Outlook as the default and close Outlook

4 In Enterprise Manager, go to properties of the new SQL Server Instance and click on the Server Settings tab

5 Click on the “change” button under SQL Mail and in the dialog box change the mail profile to “MS Exchange Settings” and click OK {SQL Server agent may need to be started before continuing on to the next step.}

6 Go to the properties of SQL Server Agent and set the mail profile to “MS Exchange Settings” and select “yes” when prompted to stop and start the agent

Configure DTC for network access (for windows 2003 servers)

1 In Component Services, go to the Properties of My Computer

2 On the MSDTC tab, go to Security Configuration and select the Network DTC Access check box

3 Under Network DTC Access, click Network Transactions

4 Make sure that the DTC Logon Account is set to NT Authority\NetworkService

5 Reboot the server for the changes to take effect

6 See the Knowledge Base article: http://support.microsoft.com/default.aspx?scid=kb;en-us;329332

SET UP NEW PAGING FILE on the server

1 Go to My Computer>Properties

2 Under the Advanced tab, click on the Settings button under Performance

3 Go to the Advanced tab on the Performance Options dialog box and click the Change button under Virtual Memory

4 Select Custom Size and set up the initial and maximum size of the D drive to be like that of the C drive (this will vary by Enterprise)

Move system databases

1 Be sure to remove the pubs and Northwind databases from the new instance

2 First create a directory structure under both the C and E drives for each new instance like the following:

C:\mssql\\data

C:\mssql\\log

Move tempdb data and log files under the directory structure just created on E and the rest of the system databases to the directories under C. Follow the directions for moving sql server databases to a new location with detach/attach from http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql

INSTALL SQL LITESPEED

1 Install SQL LiteSpeed from \\fileshare\directoryLiteSpeed CD

This is specific to enterprises with LiteSpeed. You will need appropriate license keys.

use dts to add extra objects to the master database

1 Use the import/export features in EM to import the objects from the master database on \ to the new instance’s master database

2 Copy objects and data

3 Deselect all options

4 Select all objects

You can also use the export or import features in EM or script out objects from the source control tool.

Restore User Databases

1 Restore the user databases to the new location from backups. It’s also possible to replace the user databases from the previous server using attach/detach. Do NOT use this for step for the system databases though.

Restore jobs, operators, and dts packages

1 When creating one instance from one instance

o Restore the msdb database

o Run the following:

USE msdb

UPDATE SYSJOBS

SET ORIGINATING_SERVER = ‘’

2 When creating one instance from 2 or more instances

o Restore the msdb database from one instance

o Script out all jobs and operations from the other instance and run them as well

o Copy the DTS packages from the second instance

restore linked servers and logins

1 Use “linked servers scriptor.sql” to create a script for adding the linked servers which can be run on the new database

2 Restore the logins by running sp_help_revlogin from the source server and using the output script on the destination server

NOTE: You will need to install any local users you have on the SQL Server with access to your instances. These should be documented in a security document somewhere. If not, you should create one.

Additional Instructions for Specific Databases

1 If you have databases that have extended stored procedures, full-text indexing, or other special features, then add a section in for each of these exceptions. The document will then be complete. Test thoroughly on a development or lab server using personnel not familiar with the initial authoring of the document to test.

Supporting Scripts

The first two scripts, or variations of them, can be found on numerous forums and the MS website. The are sp_hexadecimal and sp_help_revlogin. By using these to script the logins in the master database of the source server, you have a script you can apply to the destination server AFTER the user databases are restored that will seamlessly setup your user accounts without having to worry about mismatches on the SIDs.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_hexadecimal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_hexadecimal]
GO

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_revlogin]
GO

CREATE PROCEDURE sp_help_revlogin @login_name
sysname = NULL
AS
--Declare needed variables.
DECLARE
@name    sysname,
@xstatus int,
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (256),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@dbname varchar(255)
--Determine whether to process one login or all. Set up cursor accordingly.
IF (@login_name IS NULL)
BEGIN
  DECLARE login_curs CURSOR FOR
     SELECT
    sxl.sid,
    sxl.name,
    sxl.xstatus,
    sxl.password,
    sd.name AS dbname
   FROM
    master..sysxlogins sxl
    INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
     WHERE
    sxl.srvid IS NULL
    AND sxl.name <> 'sa'
END
ELSE
BEGIN
  DECLARE login_curs CURSOR FOR
      SELECT
    sxl.sid,
    sxl.name,
    sxl.xstatus,
    sxl.password,
    sd.name AS dbname
   FROM
    master..sysxlogins sxl
    INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
     WHERE
    sxl.srvid IS NULL
    AND sxl.name <> @login_name
END
OPEN login_curs
FETCH NEXT FROM login_curs
INTO
@SID_varbinary,
@name,
@xstatus,
@binpwd,
@dbname
--If no logins found, exit the procedure.
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SELECT @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SELECT @tmpstr =
'** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
   BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
     BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
       BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr
       END
      ELSE
       BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr
       END
     END
    ELSE
     BEGIN -- SQL Server authentication

      IF (@binpwd IS NOT NULL)
       BEGIN -- Non-null password

        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
         BEGIN
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
         END
        ELSE
         BEGIN
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
         END

        PRINT @tmpstr
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr =
         'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
       END
      ELSE
       BEGIN
        BEGIN

         -- Null password
         EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
         SET @tmpstr =
          'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
        END
       END
      IF (@xstatus & 2048) = 2048
       BEGIN
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
        PRINT @tmpstr
       END
      ELSE
       BEGIN
        SET @tmpstr = @tmpstr + '''skip_encryption'''
        PRINT @tmpstr
       END
     END
  --Add the default database.
  SET @tmpstr = 'EXEC master..sp_defaultdb ''' + @name + ''',''' + @dbname + ''''
  PRINT @tmpstr
END
FETCH NEXT FROM login_curs
INTO
  @SID_varbinary,
  @name,
  @xstatus,
  @binpwd,
  @dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The next script is used to script out linked servers. It was found on the internet and modified by a DBA working for me. You will need to know the passwords for SQL Server users in each linked server. It doesn’t script out passwords for you. J

use master
SET NOCOUNT ON
DECLARE @this_server VARCHAR(255),
  @server_ct INT,
  @server VARCHAR(255),
     @srvproduct VARCHAR(255),
     @provider VARCHAR(255),
     @datasrc VARCHAR(255),
     @location VARCHAR(255),
     @provstr VARCHAR(255),
     @catalog VARCHAR(255),
  @rpc INT,
  @pub INT,
  @sub INT,
  @dist INT,
  @dpub INT,
  @rpcout INT,
  @dataaccess INT,
  @collationcompatible INT,
  @system INT,
  @userremotecollation INT,
  @lazyschemavalidation INT,
  @collation VARCHAR(255)

CREATE TABLE #outputLog(
rowId INT IDENTITY(1, 1),
outputData VARCHAR(1000))

CREATE TABLE #srvLogin(
   rowId  INT IDENTITY(1, 1),
   linkedServer VARCHAR(255),
   localLogin VARCHAR(255),
   isSelfMapping INT,
   remoteLogin VARCHAR(255))

SELECT @this_server = srvname FROM sysservers WHERE srvid = 0
SELECT @server_ct = 1

WHILE @server_ct <= (SELECT max(srvid) FROM sysservers)
BEGIN
select
  @server = srvname,
     @srvproduct = srvproduct,
     @provider = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE providername END,
     @datasrc = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE datasource END,
     @location = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE location END,
     @provstr = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE providerstring END,
     @catalog = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE catalog END,
  @rpc = rpc,
  @pub = pub,
  @sub = sub,
  @dist = dist,
  @dpub = dpub,
  @rpcout = rpcout,
  @dataaccess = dataaccess,
  @collationcompatible = collationcompatible,
  @system = system,
  @userremotecollation = useremotecollation,
  @lazyschemavalidation = lazyschemavalidation,
  @collation = collation
from
  sysservers
WHERE
  srvid = @server_ct

INSERT INTO #outputLog
SELECT 'EXEC sp_addlinkedserver ''' + @server + ''', '
   + CASE WHEN @srvproduct IS NULL THEN 'NULL' ELSE '''' + @srvproduct + '''' END + ', '
   + CASE WHEN @provider IS NULL THEN 'NULL' ELSE '''' + @provider + '''' END + ', '
   + CASE WHEN @datasrc IS NULL THEN 'NULL' ELSE '''' + @datasrc + '''' END + ', '
   + CASE WHEN @location IS NULL THEN 'NULL' ELSE '''' + @location + '''' END + ', '
   + CASE WHEN @provstr IS NULL THEN 'NULL' ELSE '''' + @provstr + '''' END + ', '
   + CASE WHEN @catalog IS NULL THEN 'NULL' ELSE '''' + @catalog + '''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'rpc'', ' + CASE WHEN @rpc = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'pub'', ' + CASE WHEN @pub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'sub'', ' + CASE WHEN @sub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'dist'', ' + CASE WHEN @dist = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'dpub'', ' + CASE WHEN @dpub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'rpc out'', ' + CASE WHEN @rpcout = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'data access'', ' + CASE WHEN @dataaccess = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'collation compatible'', ' + CASE WHEN @collationcompatible = 1 THEN '''TRUE''' ELSE '''FALSE''' END

-- INSERT INTO #outputLog
-- SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'system'', ' + CASE WHEN @system = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'use remote collation'', ' + CASE WHEN @userremotecollation = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'lazy schema validation'', ' + CASE WHEN @lazyschemavalidation = 1 THEN '''TRUE''' ELSE '''FALSE''' END

-- INSERT INTO #outputLog
-- SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'collation'', ' + CASE WHEN @collation IS NULL THEN 'NULL' ELSE '''' + @collation + '''' END + ''

INSERT INTO #srvLogin
EXEC sp_helplinkedsrvlogin @server

INSERT INTO #outputLog
SELECT 'EXEC sp_addlinkedsrvlogin @rmtsrvname = '+ CASE WHEN linkedServer IS NULL THEN 'NULL' ELSE '''' + linkedServer + '''' END
   + ', @useself = ' + CASE WHEN isSelfMapping = 1 THEN '''TRUE''' ELSE '''FALSE''' END
   + ', @locallogin = ' + CASE WHEN localLogin IS NULL THEN 'NULL' ELSE '''' + localLogin + '''' END
   + ', @rmtuser = ' + CASE WHEN remoteLogin IS NULL THEN 'NULL' ELSE '''' + remoteLogin + '''' END
   + ', @rmtpassword = ' + CASE WHEN isSelfMapping = 1 THEN 'NULL' ELSE '''ENTER_PASSWORD_HERE''' END

FROM #srvLogin

DELETE #srvLogin

SELECT @server_ct = @server_ct + 1
END

SELECT outputData from #outputLog
ORDER BY rowId

DROP TABLE #outputLog
DROP TABLE #srvLogin

Ending Notes:

· In addition to the scripts contained above, attach and detach scripts are maintained for each instance.

· There is also an Excel spreadsheet maintained that has network, windows, and SAN specific checklists. Before following the SQL Server Build process, the checklists are completed to insure the server is setup correctly.

· Finally, a comprehensive environment spreadsheet in maintained which included exact LUN configurations, network ports for each server, network segment setup, etc.

Proper documentation can be a lot of overhead initially. In the end though, it produces faster turnaround on processes like server migration, disaster recovery, and consolidation. Most importantly, it could save your job and the company in certain circumstances.

Hopefully, this will help people see the importance of planning and make your migrations smoother in the future. If you would like to see anything added, or see mistakes, let me know by adding a comment to the blog.

Kristen mentioned an alternate way to do this that would allow you to have better uptime. It's not appropriate in all instances (database server being completely down for example). We wouldn't use it just because we have the SAN and can easily switch LUNs in the most likely scenario. Check it out though and see if you can use it. It might also have some things I should incorporate into our process. I will need to fully read it later. Here is the links:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=44537

Have I mentioned???????

Derrick Leggett — Wed, 26 Oct 2005 02:44:00 GMT

That EMC sucks!!!! :) We lost yet another hard drive today on the CX-700. I would say I'm surprised, but lightning would strike me dead on the spot.

We are buying EMC Replication Manager, which will allow us to save a LOT of disk space. We currently have multiple copies of production for QA, UAT, Release, Enhancement Release, Mirror for parallel testing, Development, Backup, and Testing. That consumes a considerable amount of space. The SnapView technology from EMC lets you make a copy of production. You can then have one of the other environments look at a “snap” of that copy. The snap takes up about 15% of the original copy size, AND you can refresh it in a few seconds to make it look like the copy when you originally refreshed it. The advantages of this are obvious in a rapid development environment.

Adding Replication Manager will allow us to mount multiple snaps of the same LUN or copy onto the same host. This will allow us to save space on consolidated QA, UAT, Mirror, ER, and Test server because instead of each environment having it's own copy of production, we will be able to maintain one or two copies and all the environments snap off those. The cost in disk space will decrease by thousands of dollars, paying for the product in the first six month. In addition, the flexibility to add additional testing and training environments will increase as disk space is freed up.

Anyway......just thought the world might want to know. We have several terabytes of SQL Server disk space. Anytime, we can find a way to add flexibility to the environment and lower the overall space cost, it's huge.

SQL Server 2005 September CTP

Derrick Leggett — Wed, 26 Oct 2005 02:32:00 GMT

We are now running full-speed with SQL Server 2005 at work. I have my team testing the new features and spending part of each week learning the technology. I've been telling everyone how much there is to learn. If you are a database manager with SQL Server and do not have your people ramping up, you should be shot (2 cents thrown in there). They are currently focusing on reporting services, SSIS, and analysis services, as those represent the major push my department will be making over the next 15 months. It'll be interesting to see how we can effectively leverage the new technology to replace our current reporting systems. I'm also looking forward to the operational and maintenance improvements that will make it easier to identify performance needs, track deprecated stored procedures, etc.

SQL Server 2005 Install Notes

Derrick Leggett — Fri, 17 Jun 2005 05:20:00 GMT

I've now installed SQL Server 2005 about 18 trillion times since the first beta came out. :) Seriously, since I have been trying to take all the free webcasts, training, etc that's out there and ramping up my skillset on the new technology, I have completed around 25 installs of the product. I am currently installing three more instances of the June CTP on various laptops and computers at home. So.......I thought I would start recording notes on the installs here.

This will probably end up like all my other blogs (rarely updated and very seldom read). lol But, in the interest of the great and almight blogosphere, let's begin.

I had a brilliant idea of setting up one of my virtual server instances as a DC, so I wouldn't have to use multiple computers to have my domain environment. I also installed SQL Server 2005 on there. This was brilllllliant. It didn't work though. lol There is no NT Service\Network Service blah, blah account on a DC, so Integration Services wouldn't install. I learned that the second time this little scenario failed. The first time it failed because I tried to install with the domain\administrator account. It didn't have permissions to one of the local .msi files and things kept failing along the way. I would try this scenario out again to see if I can replicate, but I'm not that bored.
The MSDN site has now very nicely placed the April CTP inside the Visual Studio 2005 section of downloads. There's a really good reason for this. The .NET Framework used by the June CTP and the Visual Studio 2005 Beta 2 are DIFFERENT. I, of course, had to learn this the hard way. Just to prove the point, I tried it twice. It failed twice. BRILLIANT!!!
Do yourself a favor. If you insist on installing both VS 2005 AND SQL Server 2005, install SQL Server 2005 first. It works MUCH, MUCH smoother. Be sure to use VS 2005 Beta 2 and the April CTP. Together, they work a charm.
Use Virtual Server, VMWare, or whatever you want to use. It's kewl. It's awesome. I love it. I think it's the best thing since sliced bread with bananas, peanut butter, and bologna all mashed together and topped with mint ice cream. Setup a Virtual Server instance with just Windows 2003 and all the stupid updates that take 1800 hours to install. Copy the file. Hide it. Protect it. You'll need it. After you've done that, embarc on the great voyage of beta testing. May Gates rest your merry little soul.
Forgot a fun one (I woke up). I installed the April CTP after VS 2005 Beta 2 once (I think this was the order....it was like 3:30am again). Under the stupid MS SQL Server 2005 menu I didn't see anything. IF you INSIST on installing in this order, uninstall the stupid Express edition FIRST. Otherwise, your tools are ummmmmmmm missing in action. AWOL tools are a bad thing.

I might add to this. I might not. It depends. I'm doing this one at midnight. It shows. My 2803419840019841 installation (June CTP) is almost finished. I can't wait. My journey to conquer the world has began.

Yawn. Yawn. Yawn

Snore

......

Virtual Server 2005 Woes

Derrick Leggett — Sun, 08 May 2005 23:47:00 GMT

I’m using Virtual Server 2005 to setup all my labs and training sessions that I’m trying to write for SQL Server 2005. I ran into a little problem when I tried to install Visual Studio 2005 on the virtual machines though. Apparently, VS has a 2.2gb limit when you mount .iso files as a CD/DVD that’s not very well documented. :) You basically have to find out about it by using Google and reading people’s blogs.

After finding the issue, I started looking for ways around it. I tried using the Virtual CD application by Microsoft. It unfortunately is a piece of CRAP!!! It locked up my laptop a couple times really nice. When it did work, it presented an empty DVD drive instead of actually showing me the data. I finally found a free tool called Virtual DAEMON Manager, which works great.

I wanted to throw this out there for anyone else who likes just using the .iso files. I’m building quite a library of them, and I don’t want to actually have to burn a physical DVD for every single one over 2.2GB.

SQL Server SP4 Rollout

Derrick Leggett — Sun, 08 May 2005 17:53:00 GMT

As we all know, SQL Server SP4 arrived on the scene late last week. It’s been a LONG time since we’ve had a major service pack release for SQL Server 2000, so this service pack is very large. If you look at the release notes, the service pack fix list includes 285 fixes for SQL Server and an additional 90 fixes for analysis services. You can find the complete fix lists, along with release notes and the service pack downloads here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en

Here are some snippets I noticed in the release notes and fixes that are interesting:

SQL Query Analyzer will permit connections to SQL Server 2005. However, some functionality may not be available. That’s still really kewl!!!
FIX: Concurrency enhancements for the tempdb database
- This particular issue caused us a LOT of issues at my current employer, so it’s good to see this as part of the fixes.
There are a lot of issues related to cursors. Imagine that. :) Reading this fix list should give you several more reasons to avoid cursors when at all possible. They SUCK!!!
Profiler (we hope) will finally return the CPU counters correctly. This has always been aggravating. Hopefully, they also fixed the issues with functions not showing up correctly in Profiler when called from stored procedures.

As with any of the service packs they have released for SQL Server, thorough testing should be completed before rolling the service pack to a production system. There have been service packs in the past that didn’t exactly go smooth, if any of you were around in the 7.0 and pre-7.0 days. In addition, they usually catch some things during the first few weeks they missed or didn’t get quite right (SP3a?). Here is the general release roadmap we are currently planning:

Review the release notes and fix lists to determine what needs to be tested at SQL Server and applications levels before releasing service pack to any environment.
1. Review release notes and fix list.
2. Monitor MS and SQL Server forums for issues.
3. Make a formalized test plan from review.
Release to development environment and test for two weeks.
1. Have DBA teams and applications teams test and signoff on the test plan when complete.
2. Work through issues as encountered and document.
Roll to QA and test for an additional week.
1. Have QA team test and signoff.
2. Work through issues as encountered and document.
Roll to UAT, Release and Production.
1. Complete final test of environment and major production applications after release.

When we complete the review and have the test plans created, I will add them to the blog. These are not extensive test plans. They are created just to insure we test all the major components and processes we believe might be affected after our review of the service pack documentation.

As one last item to cover on the service pack, if you haven't gone through the process of upgrading MDAC components throughout the environment, then you need to get a test plan together and get it done. Old MDAC installations caused a host of issues with the latest SQL Server service pack release in our environment. In addition, there have been a lot of important performance, stability, and security fixes throughout the MDAC release cycle. At a minimum, all MDAC components should be at 2.7 SP1 Refresh even before the upgrade. With the upgrade though, you should be up to the latest version. You can find out more about this by reading the release notes for SQL Server SP4 and MDAC 2.8. Here is the download site for all MDAC components:

http://msdn.microsoft.com/data/mdac/downloads/default.aspx

UPDATE #1: (20050516)

Well, I'm glad we didn't install anything yet. :) We're still smoothing out the details of the test plan. We got this little notification from Microsoft about SP4 not working very well with AWE enabled systems though:

http://www.microsoft.com/sql/downloads/2000/sp4.asp

I seem to remember saying something earlier in this post about being careful and service packs not going very well sometimes. We'll keep watching this one and keep you posted. Hopefully, nobody rushed to install it on their production systems with massive amounts of RAM.

Oracle and jhermiz

Derrick Leggett — Wed, 04 May 2005 04:31:00 GMT

Oracle and jhermiz

This has been a test of absolutely nothing.

I do believe this might be the most ridiculously stupid blog post I ever make. :)

SQL Server 2005 Free Training

Derrick Leggett — Wed, 20 Apr 2005 04:00:00 GMT

Microsoft has set up virtual labs for people to train on their new technologies, including SQL Server 2005. You can find them here.

http://msdn.demoservers.com

The labs I have taken so far are really good for an introduction to SQL Server 2005 and Visual Studio technology. These are Windows 2003 virtual servers with the latest CTP of SQL Server 2005, Visual Studio, etc installed. In addition to following the manual, you can play around a little during each session. It lets you experiment with 2005 without actually installing it on your machines, which can be a real treat if you don't have VMWare, Virtual PC, etc.

If you haven't started reading up and learning 2005 yet, you need to get started. People who are content with their current knowledge will find themselves very unprepared for the future. It's coming, so get the reading glasses out, stop procrastinating, and get to studying. If you don't, the Junior DBAs out there are going to take your jobs you old lazy farts. :)

Applications that SUCK!!!!!

Derrick Leggett — Thu, 13 Jan 2005 01:45:00 GMT

In this world of third-party insanity, I'm constantly amazed how much companies spend for third-party applications that are written like crap and have ZERO security. Lately, I've been plagued by a series of third-part applications using the sa username and password. When you ask them WHY, they get angry and explain that's how the applications were designed. When I politely explain to them that they are idiots, they don't seem to comprehend WHY. So, I'll say it again.....YOU'RE AN IDIOT!!!!!

Magic Helpdesk Software (http://www.remedy.com/solutions/magic/)

Websense (http://www.websense.com/)

Focusing on the security of the web. HAHAHAHA

RATA HMDA Compliance software (added 20050517)

This one kind of cracks me up. The default installation creates an account and assigns sysadmin server role rights to it.
HELLOOOOOOOOO!!!!!!!

I’ll add to the list as I think about more. Let me know your personal favorites.

SQL Server Datetimes

Derrick Leggett — Sat, 08 Jan 2005 23:31:00 GMT

I feel like posting today. :) We get asked about datetime formats a lot on the forums. Here is a simple little script to tell you what formats SQL Server supports using the CONVERT function.

SET NOCOUNT ON

CREATE TABLE #results(
conversion INT,
result VARCHAR(55),
code VARCHAR(255))

DECLARE
@min INT,
@max INT,
@date DATETIME

SELECT
@min = 1,
@max = 131,
@date = GETDATE()

WHILE @min <= @max
BEGIN

IF @min BETWEEN 15 AND 19
  OR @min = 26
  OR @min BETWEEN 27 AND 99
  OR @min BETWEEN 115 AND 119
  OR @min BETWEEN 122 AND 125
  OR @min BETWEEN 127 AND 129
  BEGIN
   GOTO NEXT_LOOP
  END

INSERT #results(
  conversion,
  result,
  code)

  SELECT
   @min,
   CONVERT(VARCHAR,@date,@min),
   'SELECT CONVERT(VARCHAR,GETDATE(),' + CAST(@min AS VARCHAR(5)) + ')'

NEXT_LOOP:

SELECT @min = @min + 1
END

SELECT
@date AS datetime_format,
conversion,
result,
code
FROM #results

DROP TABLE #results

SOX Auditing Companies SUCK!!!!

Derrick Leggett — Wed, 27 Oct 2004 04:18:00 GMT

Just gotta rant for a second. (I know it's hard to believe.) There are only a few approved SOX auditing companies out there currently. The law is so broad in scope, yet undefined, that auditing companies really have no idea what they are auditing for. We have internal auditors talking to D&T; and one of the biggest problems we face is the vagueness of responses received back. None of the auditors really agree with each other on what needs to be done. Passing and/or failing an audit will not be determined by the security of the companies data. It will be determined by your auditors interpretation of their auditing companies interpretation of a law the courts haven't yet interpreted.

That should give anyone truly concerned about productive process and data security a really bad headache!

Long live stupid laws......job security for IT people that couldn't keep a real job.

Why do all these SOX auditing companies have IT consulting divisions???? HMMMMMMMMM

Database Security Initiative

Derrick Leggett — Sat, 23 Oct 2004 20:07:00 GMT

Physical Database Security

· Move a SQL Server out of DMZ (this one was ticking me off). --Completed.

· Create new VLAN's for SQL Server and migrate servers. --Completed.

o Created four VLAN's to provide separation of database servers on network.

o Separates production, development, third-party, and back office.

o Allows separate rules governing activity and access security at a group level.

· Implement SQL Server Firewall --Not Started

o Will review security and firewall policy at later date.

o If current security is not sufficient for business owners, will create database firewall.

· Server Consolidation --In Progress.

o Had over 30 servers, which is not manageable. An environment that can't be managed isn't secure.

§ This is down from over 70 installations when I started.

o Consolidating to 4 pairs. Production, development, third-party, and back office.

· Port/Protocol security --Completed.

o All SQL Server use non-standard ports with TCP/IP enabled only.

o Only approved servers are allowed access to specific DB servers on appropriate ports.

· Named Instances --In Progress.

o Only named instances are used in environment.

o All environments have separate instances.

o Sensitive business units such as accounting and HR housed on own separate instances.

· Server Lock down --Completed.

o Only database admin and enterprise admin group allowed in local/Administrators group.

o No login allowed outside of approved group.

· Monitoring of network and server performance -- Completed.

o Look at server closely if unexpected activity levels occur.

· Server installation policy --Completed.

o There are to be no installations of SQL Server outside of DBA group.

o There is to be no purchase of software with a database component without consulting the DBA Group during review.

Logical Database Security

· Administration Lock down --Completed.

o The sa password is insanely long and complicated. Not used. Only a couple people have access to it.

o Database Admin group added in; and the BUILT IN/Administrator taken out of all SQL Servers.

o All server level (System Administrator, etc) membership deleted with exception of Database Admin group.

· AD Security --Completed.

o No individual user access to the database server.

o Everyone is a member of an AD group.

§ Group corresponds to an application or job function.

§ Group defined at time of creation with a real description.

§ A business owner approves permissions and members.

§ Corresponds to a SQL Server role.

· DBA manages role membership and permissions. Performs initial setup.

§ Business manages the AD membership and coordinates with helpdesk to add/delete members.

§ HR insures terminated employees are deleted.

o SQL Server logins only allowed on third-party apps. Well documented and follow normal procedures.

§ Once initial setup is in place, any new permission must follow corporate Access Control Process.

o Developers’ access limited. Only granted access where needed.

§ Have elevated permissions in development only.

§ Release and debug teams have elevated permissions for events.

§ QA Team has elevated access in QA environment.

· Application Security --In Progress.

o Externally addressable applications all exist on standalone servers in the DMZ.

§ Not connected to a network.

o Can only access servers/databases necessary for applications hosted.

o Each application will have a separate login to allow more granular control of permissions.

§ Implemented in phase two.

· Source Control --Completed.

o All dml changes must go through review/approval process including emergency releases.

o All ddl/dml releases documented, in SourceSafe, and follow rollout process.

· Data Level Security --In Progress.

o Minimize access further by having user level access inside of each application.

o Encrypt data where necessary.

Monitoring --In Progress.

· Monitor all user connections.

· Audit any connections outside of server "white list".

· Monitor and audit all dml changes to insure there are no changes outside of process.

· Monitor and trigger notification on all failed login attempts.

SOX has placed an unnecessarily large overhead on companies because irresponsible lawmakers did not define the act sufficiently. Because of this, companies are spending billions of dollars scrambling to meet deadlines and goals that auditors are currently in the process of defining. Many of these goals will be redefined because it is now in the hands of the judicial system to determine how far-reaching this act will be.

The security of data is important though. It's many times one of the most overlooked areas of a company. Many of the items we are now implementing should have been in place years before us. It's unfortunate it takes a badly defined law to make it happen.

If anyone has anything to add let me know. I'll try to update the post on a semi-regular basis, as things are added/subtracted from the overall project.

20041211:

We've made a lot of progress in the SQL Server environment. All systems are now monitored proactively using standard traces across the enterprise. Reports are published every day, giving updates on system performance and security. We are also beginning the final wrap-up of our SOX audit for the data systems group. So far, it looks like we're passing with flying colors.

We still have a lot of work to do in this area though. We need to finish the SQL Server consolidation by migrating a few remaining third-party applications to the third party SQL Servers. In addition, the back office still needs to be consolidated to a clustered pair. By the end of 2005 Q1, we should have the final architecture in place.

PASS 2004 - Part 4

Derrick Leggett — Thu, 07 Oct 2004 01:20:00 GMT

I know this is a little late, but I wanted to finish it up for my own purposes. The final day of PASS was incredible. Anytime you get a chance to listen in on Kimberly Tripp you should take that chance and remember it. This is how a PASS presentation should be.

Very Large Databases with SQL Server 2005 (by Lubor Kollar) -- **GOOD**

The presentation by Lubor was a high-level presentation that led really well into a lot of the presentations at PASS. He did a good job of explaining how the engine works to determine locks. He also explained the threading/fiber technology used by SQL Server and how that ties into the OS. He covered many of the concepts in 2005 that allow it to scale better. One of the main items is the advanced partitioning and online operations in 2005. I'm already setting up databases in the betas using this technology. It's going to be a good learning curve for DBAs not participating or looking at the betas to learn all these new features. You NEED to get started now if you haven't began looking at SQL Server 2005.

Security in SQL Server 2005 (by Girish Chander) -- **Excellent**

This guy should give presentations for a living. :) Girish heads up the security team responsible for rewriting SQL Server Security. They have done just that. With the exception of the CLR, security could be the single biggest change in terms of impact, administration, and skill transition. Here are a few of the highlights:

SQL Server logins have been drastically changed. They now tie directly into the Windows API if you are using W2k3 to enforce the complexity of the passwords. There is only a simple complexity check in W2k since the API isn't available. Blank passwords are off by default, which shouldn't be allowed at all. :) You can now disable logins and worry about deleting them later. We're in the middle of a security audit and redesign right now for SOX. I wish this feature was availabe now. You can turn the password policy and expiration (yes, I said expiration) off. That won't happen where I work though.
Everyone has, or should have heard, of how schemas are now handled. The naming convention for objects is server.database.schema.object. Objects are now owned by schemas. Users own, or are allowed access to, these schemas. This allows deleting of users not previously possible without renaming all their objects, etc. It's a LOT like the Oracle model (shhhhh).
You have new permission levels that are useful. You can now give someone rights to JUST execute procs, making them part of their own schema. You also have a view definition that allows you to see only the metadata. This will be nice for auditing purposes.

Here are a few concerns I have from the presentation:

Deny always takes precedence, regardless of the level it's implemented at. I understand the reasoning for this. This will continue to cause major issues though when you need to grant an exception access to denied objects. There should be an override feature. Oh well....such is life.
You can grant someone without permissions to an object the right to that object by using “EXECUTE AS 'user'“. This is a nice feature, but you need the ability to override it and deny people the ability to execute this. A reporting person in accounting shouldn'be be able to grant a janitor the rights to see sensitive accounting data. A few people recommended this in the session, so hopefully this will be addressed.
There is still no really good solution to database ownership chaining. The need to do this is just a fact of life. We end up granting access directly to tables at times because this has not been addressed correctly. Microsoft needs to recognize the issue and deal with it by making a “super group“ permissions level that can deal with this type of issue without granting cross-ownership of the whole database.

SQL Server 2005 Partitioning - The Rolling Range (by Kimberly Tripp) -- **ROCKS!!!!**

I don't know what to say about Kimberly. She is GOD might work. :) Anyone who doesn't know how to use partitioning or thinks it's “one of those things you might use in a specialized shop” needs to download this presentation, study it, learn it, and realize the impact it can have on their business. We will be implementing it where I work to fix some of the large table hot spots we currently have. SQL Server 2005 does a great job of making this more manageable and useable. Kimberly Tripp not only explains how things work, she gives you a step-by-step demo of how to use. She also addresses many of the challenges faced when implementing partitioning.

Summary

The PASS Conference this year was awesome. I was disappointed they didn't have more presentations giving comparisons between 2000 and 2005, including areas to “watch out for”. That would be a great idea for a presentation though. The sessions I did attend, with the exception of DBA 101, were great though. The PSS group was very helpful in answering questions, including an issue we were experiencing at work. Orlando and the resort were unbelievable.

Can't wait until next year.

PASS 2004 - Part 3

Derrick Leggett — Thu, 30 Sep 2004 21:37:00 GMT

WOW....what an interesting last couple days. I was priviliged to attend a couple incredible meetings. I was unfortunate enough to attend a session that was not so great (cough, cough)!!!!! Such is life in the world of conferences. :)

SQL Server Locking Internals and Troubleshooting - **Good**

This session covered a lot of the tools and techniques used by PSS in troubleshooting issues. He went really into depth on how scheduling, threading, fibers, crabbing work. SQL Server 2005 is changing the way threads are allocated. By allocating them to tasks, we will be able to get better management of available threads. It also now supports the option to dynamically change threads. Right now it requires a reboot.

There were also a lot of good utilities covered. It's good stuff I tell you. Everyone should check out the new read80trace tool available at Microsoft.

DBA 101 – SQL Server 2005: Transferring Existing Skills to a New Platform - **Needs Help**

This session was not so great. The session speaker didn't seem to actually know that much about 2005. I'm not sure how much he actually used it. It would be nice to have a session that compares the two and gives you items to watch out for as a DBA.

Prescriptive Architecture Guidance on SQLCLR - **ROCKS!!!!!!!!** Speaker - Ramachandran Ven Katesh

This guy is now one of my heros. :) He should speak or train SQL Server for a living. He explained the architecture of the CLR, including CLR, best practices, examples, etc. Many of his samples can be found by searching MSDN. If you are a PASS member and didn't get to attend, buy the conference DVD and download his slides. It will be worth it.

He also showed us the Visual Studio database project in 2005. This is a project that allows you to debug and register items automatically in a database. It's much easier than the Beta 1 way of doing things. In addition, it allows debugging of both CLR and TSQL in the same context. It's a great upgrade and tool. One of the things I'm really looking forward to is being able to tell vendors I don't let them put XPs on my server because the SUCK!!! It was nice to hear an MS person say the same thing. The CLR chnages all that.

Relational Data Warehousing with SQL Server 2005 - **Good**

After this, I'm tempted to ONLY attend conferences with MS speakers. They have really done a great job with their presentations this week. We covered the vast improvements to partitioned, indexed views. They have been extended to allow use in more situations. They have also improved the matching algorithms and now allow concurrent processing against multiple segments of the index. Index functions have also been expanded with online index functions and the INCLUDE clause.

Enhancing your SQL career by making a name for yourself - **Good**

I typed my blog in this session. It was great. :) Seriously, the biggest thing it stressted was:

DO SOMETHING!!! Help people out, write a blog, answer a question, start a website. It pays off in the end.

Later....I'm out of here!!!!

PASS 2004 - Part 2

Derrick Leggett — Wed, 29 Sep 2004 19:00:00 GMT

High Availability with SQL Server 2005

This was a great session. It was cut a little short by an EMERGENCY EVACUATION!!! Go figure. :) It focused on the new enhancements that SQL Server 2005 has added to provide for greater flexibility and implementation of high-availability networks. For those who are members of PASS, the slides can be downloaded from www.sqlpass.org when the conference is over. Here are my takeaways from the session:

Covered Database Mirroring.

Can be a long ways away.

i. Have to keep transactions in sync though, which can cost performance.

ii. Don’t need to be on compatability list like clustering.

iii. Should run equal hardware though; however, it’s not a requirement.

iv. No shared disk.

Can pick synchronous/asynchronous.
Terminology

i. A client attached to the “principal” server.

ii. Principal server sends “secondary transactions” to the “mirror” server.

iii. There is a “witness”. Provides vote on who is principal and mirror. Provides the winning vote. Allows failover if the connection breaks between principal and mirror vs. the actual servers failing.

We can use database snapshots on the mirror, put it in read-only, and use it for reporting.
To become the Principal, a server must talk to at least one other server. Can provide some long distance problems.
Big question came up on whether you can mirror the master database.???

i. This is a great question. The answer appears to be no.

ii. Would be a problem.

It’s pretty efficient because it catches the writes in the log buffer and automatically sends the log entries to the other server. There’s two types of commits:

i. Safety mode: Verifies the commit on the other side before it commits. Has automatic failover capability.

ii. Safety off: Just forgets about the transactions after it sends them.

Because it’s just sending stuff, when you rollback a transaction it’s slower. It has to verify the rollback on the other side as well.
DDL Changes are captured.
Is able to handle multiple databases together with cross-database updates.

i. Need to find out more information. This wasn’t covered as the session was cut short.

Can you tell how much latency exists between the mirrors?

i. Would be very efficient in a SAN configuration.

There’s a client library to automatically redirect to mirror. Requires no changes to application code. Client automatically redirected if session is dropped. It’s aware of the principal and mirror servers. The library caches mirror name upon initial connect to principal.
There is a manual failover process if you decide to use asynchronous.

i. ALTER DATABASE db SET PARTNER FAILOVER

Covered replication, log shipping, cold standby.

Not changing log shipping. Lots of sessions on replication.
Good chart on how to choose different types of failover.

Database Snapshots (new)

Allow recovery from user erros by allowing the database to go back in time.
Multiple snapshots are allowed.
It’s read only. To drop the database it’s attached to, you need to drop the snapshot first.
CREATE DATABASE mydbsnap ON (filelist) AS SNAPSHOT OF mydb
DROP DATABASE mydbsnap
You can restore from a snapshot database.

i. RESTORE DATABASE mydb FROM DATABASE_SNAPSHOT = ‘mydbsnap0600’.

ii. Good Question: Can you restore to a different database?