While getting ready for the PASS Community Summit in Orlando this week (where I plan to talk about SQL Server 2005 Partitioned Tables/Indexes), I realized we were close but not quite ready to release the post-beta II whitepaper on MSDN.

So, in preparation for PASS we decided to post a pre-release of the partitioning paper here.

Enjoy!

See you in Orlando... eventually?!

Each year as the weeks approach my September trip to Florida, I hear about hurricane after hurricane. I always wonder why conferences choose September (or August or October for that matter) in Orlando?? However, having said that - I've been to Orlando in September for the last 3 years and the weather's always been perfect! OK - I've probably just jinxed it?!

If you're going to be there, make sure to stop by and say hi! I'm delivering a pre-conf on Tuesday and a SQL Server 2005 session on Wednesday as well as sitting in on the Women in Technology Luncheon on Friday. The luncheon's are quite fun too! I've now done a couple of these and we typically end up chatting about how we got into high tech and how we stay motivated (which quite honestly, doesn't only apply to women!).

I hope to see you there!

I began setting up for Profiler and Performance Monitor by first launching Profiler and then launching Performance Monitor within it (there's an icon for PerfMon second from the Right or you can select Performance Monitor from the Tools Menu). I had already created a Performance Monitor Counter Log so all I needed to do was start it (more details on PerfMon Counter Logs later).

Tips:

·                     If you want to correlate Perf Mon events with Profiler you will need to do this from SAVED Profiler Traces and PerfMon logs. Real-time analysis is not supported.

·                     The correlation is done based on the time of the events so it is important that the times are correct. You should make sure to either:

·                     Run these on the same machine

·                     Make sure that the machines are in sync in terms of time.

·                     The correlation is a bit tough if there are TONs of events as Perf Mon’s granularity for sampling is 1 sec and Profiler can return a large number of events in a single 1 sec range… SO – filtering (and you can do this later) will be a very good idea. Regardless, this is a GREAT feature to see how certain counters (memory, disk, CPU, etc.) look over the course of long running activities.

If you're not familiar with creating a Performance Monitor Counter Log click here.

Ok, so that was mainly setup. For my first real demo I wanted to show Profiler and a few of my favorite new features. Here’s a quick list:

When creating a trace there are ONLY two dialogs: General and Events Selection. The General dialog is similar to the old General dialog where you choose the template, the save to file and/or table options, etc. The Events Selection dialog however, is ALL NEW. The Events Selection Dialog has a ton of new and incredibly useful changes:

• Only data columns that produce values for each of the specific events are shown and only those data columns can be selected (check boxes). See this dialog here: EventSelectionDialog.jpg (56.51 KB). Notice that there are blank spots where an event doesn't produce a data column.. yeah!
• Filters apply at the column level. See this dialog here: EditFilterDialog.jpg (16.74 KB). And you can state whether or not events that do NOT generate a value for the filter are sent (yeah!!!). However, I do remember having a bit of trouble with this one...
• You can pause an active profiler trace, change the events and/or data columns and restart it… all of the additional events go to the same trace file and you do not lose the previously started trace, etc.
• Deadlock graphing – this is an Event – and it produces a graphic display of the spids that were involved in the deadlock. You can “extract” these events (right click on the event “Deadlock Graph“ and choose “Extract Event Data“) and then open this xdl file within SQL Management Studio so you can spend more time analyzing it.

So, once setup I created a deadlock scenario, showed the profiler output for deadlock graphing, paused the trace, removed the deadlock related events (really only because I could J) and then opened another window to generate some simple activity. So – speaking of the simple activity... I created a “demotable” that had defaults for all columns and then I created an insert statement using the DEFAULT VALUES clause. To get this to execute repetitively I could have written a complex (not!) loop such as WHILE 1=1 but instead I chose to use go N (mostly because people don’t tend to know about this one). Here’s the full extent of my “code”

            INSERT DEMOTABLE DEFAULT VALUES

            GO 100000

One of my favorite features of Management Studio is the ability to “script” a step rather than execute it. Within SQL Management Studio, each dialog has 4 different options from which to choose (these are including the typical OK/Cancel) but also add a couple more at the top of the dialog:

At the top of the dialog

• Schedule – which will create an Agent job.
• Script – which will send the command(s) to a new Query Window in Management Studio

At the bottom of the dialog (the norm):

• Run it right now – with the OK button
• Cancel it – fairly obvious, eh?

The “Script” option has to be one of my favorites. You can learn from this dialog, you can cut/paste and help to build more complex scripts, you can just see what the heck the UI was going to do, etc… and well, that’s what led to SQLCMD. Something I wanted to do (from just a practical perspective) was create a simple/fast way to connect to my existing SQL Server 2000 machine, backup a database and then restore it to my SQL Server 2005 machine. One of the benefits of backup/restore is that SQL Server will upgrade the database on restore (no special settings required - this is default behavior if you restore a SQL Server 2000 backup to SQL Server 2005). Although finding the correct syntax for each step is easy through the UI, I wanted to take the overall process one-step further and create a SQLCMD-specific script to automate it (yes, I kind of like the idea of a command line tools for batch/automation, etc.. and yes, I could have also done this with DTS or the SQL Agent... good idea for another blog entry! Hey guys (and you know who you are!) you should do the comparable DTS/Agent jobs?! I'll be waiting. J J J).

Anyway, the primary goal is to leverage some of the new capabilities of SQLCMD (which I have to admit – if you’re a command line/automation person – this is going to quickly become your favorite new features).  Here’s a very quick summary of SQLCMD:

• Replaces OSQL (and then some!)
• Can set a variety of environment variables
• Offers a -A parameter for the dedicated admin connection (only one window available for this at a time and the usage here is when you can’t otherwise connect to your server – for whatever reason. To ensure that they can always use this they have kept resources available so that this should always work. I use “should” because well, I rarely use always (or never)… J
• Adds –v for variable input from the command line but also supports variables set as environment variables (via SET) or variables set within the SQLCMD script (SETVAR).
• Allows connections to be specified within the script via:
       :CONNECT servername
  NOTE: the semi-colon is required
• Has a few new error handling options such as
       :ON ERROR EXIT
  NICE: this uses RAISERROR and when a severity of 11 (I think) or higher is raised you can define what happens (EXIT, IGNORE or RETRY n) to control script flow and retry behavior (yeah!)
• And with the exception of a few new (yet VERY cool things like this) SQLCMD is really just TSQL plus some very nice automation stuff. (I can hear TSQL geeks cheering...)

So – I created a script that does this and here’s the play by play of it:

I don't want to support system databases for backup/restore. You could certainly do a backup and then restore to a new name... but that will have to be in version 2.

:ON ERROR EXIT
go

 

IF '$(DB)' IN ('master', 'model', 'msdb', 'ReportServer', 'distribution', 'tempdb')
BEGIN
 RAISERROR ('System Databases are NOT supported. Script Terminated.', 16, -1)
END
go

Next, I wanted to create a connection to my 2000 server (this could have easily been a parameter!)

:CONNECT servername

 

BACKUP DATABASE $(DB)
 TO DISK = '\\servername\sharename\path\$(db).bak' -- NOTICE YOU CAN USE VARIABLES WITHIN STRINGS!
 WITH INIT
PRINT ' '
go

NOTE: Be sure to set a GO after each batch that includes changes in connections, etc. The idea is that much of the SQLCMD code (per batch) is evaluated and executed first. Without the go I would have ended up in the next connection - executing the backup. Here's a quick example of what I mean:

-- Example 1

:CONNECT SERVER1
EXEC PROC1

: CONNECT SERVER2
EXEC PROC2

-- In Example 1, both procedures execute on SERVER2

 

:CONNECT SERVER1
EXEC PROC1
go

 

-- Example 2

: CONNECT SERVER2
EXEC PROC2
go

-- In Example 2, PROC1 executes on SERVER1 then PROC2 executes on SERVER2 - as desired.

After the database is backed up I connect to the SQL 2005 server and drop the existing database... you may or may not want to do this so blindly.

:CONNECT servername

SET NOCOUNT ON

 

IF DATABASEPROPERTYEX('$(DB)', 'COLLATION') IS NOT NULL
 DROP DATABASE [$(DB)]

 

WAITFOR DELAY '$(DELAY)' -- this was just for testing...

Next, I need to determine the SQL 2005's Data Path as I didn't want to assume C:\Program Files\...

To do this I'm using an undocumented xp (cause I wanted to see if it still works? it does!)

DECLARE @SQLPath nvarchar(512)
EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
            'SOFTWARE\Microsoft\MSSQLServer\Setup',
            'SQLPath', @SQLPath OUTPUT

Next, I do some cool stuff to create the correct locations for the new data and log file names. This was the hardest part really... What if the database has multiple files - and the pathes are NOT the same as the server from which the database was backed up. Well, now I need to do a restore with MOVE. Have you seen the syntax for RESTORE with MOVE... it's not overly pretty. Anyway, I first create a table into which I will store the output of LOAD FILELISTONLY (so I can interrogate the backup device and see the filelist of the database which was backed up):

CREATE TABLE #BackupFileList
( LogicalName sysname NULL
 , PhysicalName sysname NULL
 , [Type] char(1)
 , FileGroupName sysname NULL
 , Size bigint
 , MaxSize bigint
 , FileId smallint
 , CreateLSN numeric(25,0)
 , DropLSN numeric(25,0)
 , UniqueId uniqueidentifier
 , ReadOnlyLSN numeric(25,0)
 , ReadWriteLSN numeric(25,0)
 , BackupSizeInBytes bigint
 , SourceBlockSize bigint
 , FileGroupId  smallint
 , LogGroupGUID uniqueidentifier
 , DifferentialBaseLSN numeric(25,0)
 , DifferentialBaseGUID uniqueidentifier
 , IsReadOnly bit
 , IsPresent bit
)

Next, I get the info from the backup device (again, notice the parameters being used even in Dynamic String Execution!)

INSERT #BackupFileList
 EXEC('LOAD FILELISTONLY FROM DISK = ''D:\SQLDemo\$(db).bak''')

Now I reverse the physical name to find only the “file“ part of the name... reversing it to find the first string up to a backslash (from the end). Then I extract that string and reverse it back... and - at the beginning of the string, I add the local server's data path.

UPDATE #BackupFileList
 SET PhysicalName = @SQLPath + N'\Data\' + REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

Now I'm ready to walk the filelist and build the RESTORE with MOVE database string. This completes the script!

DECLARE @LogicalName sysname
 , @PhysicalName sysname
 , @ExecStr   nvarchar(max)

 

DECLARE FileListCursor CURSOR FAST_FORWARD FOR
 SELECT LogicalName, PhysicalName
 FROM #BackupFileList

 

OPEN FileListCursor

 

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

 

SELECT @ExecStr = N'RESTORE DATABASE $(DB)' +
       N' FROM DISK = ''D:\SQLDemo\$(db).bak''' +
       N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''
 
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

WHILE @@FETCH_STATUS <> -1
BEGIN
 SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + ''''
 FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END

 

-- SELECT @ExecStr

EXEC (@ExecStr)

 

DEALLOCATE FileListCursor
go

I set my parameters on the call to the script.

If you're interested in reading more about somewhat stressful demos for the BI Team read here.