Pivot or Sum

A developer was working on a report. The data was originally in a SQL 2000 Server database. The data being used was then transferred into a SQL 2005 database. The original report used SUM and Group BY. Now it was in 2005 Pivot could be used. He wrote a new script and compared results. His question give the same results, is it wrong to assume that SQL Pivot newness means betterness?

Here are the two queries:

Select [Reviewed] + [Logged] as [Queue], [Open] As Development, [Testing], [Pending Imp],
[Open] + [Testing] + [Pending Imp] as [Total In Progress],
[Reviewed] + [Logged] + [Open] + [Testing] + [Pending Imp] as [Grand Total]
from (Select [state] FROM dbo.tblCombinedSnapshto) Datatable
Pivot
(
Count([state])
For [state]
In ([Reviewed], [Logged], [Open], [Testing], [Pending Imp])
) Pivottable;

Select Sum(case when [state] = 'Reviewed' or [state] = 'Logged' then 1 else 0 end) as [Queue],
Sum(case when [state] = 'Open' then 1 else 0 end) as Development,
Sum(case when [state] = 'Testing' then 1 else 0 end) as Testing,
Sum(case when [state] = 'Pending Imp' then 1 else 0 end) as [Pending Imp],
Sum(case when [state] = 'Open' or[state] = 'Testing'
or [state] = 'Pending Imp' then 1 else 0 end) as [Total In Progress],
Count(*) as [Grand Total]
from dbo.tblCombinedSnapshto;

The results were:

PIVOT timing
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 38 ms.
IO Table 'tblCombinedSnapshto'. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

2000 timing
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 50 ms.
IO Table 'tblCombinedSnapshto'. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So for disk usage they were equal. For speed Pivot wins out.

Only a small scale test but worth doing now and then.

Table Diff: Compare the contents of two tables

Table diff will let you compare two tables and generate a change script to sync them up.

By default it is installed here:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe"

In this example I am comparing two tables in the same database called Live_Support. The tables are LF29699_Orig and LF29699_New.

I have created a batch file called comapretables.cmd.

The contents of the table looks like this:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver sqlmgmt1 -sourcedatabase Live_Support -sourcetable LF29699_Orig -destinationserver sqlmgmt1 -destinationdatabase colt_Live_Support -destinationtable LF29699_new -f c:\LF29699_diff.sql

Execute this and you get a file c:\LF29699_diff.sql which is a change script for that table:

-- Host: sqlmgmt1

-- Database: [Live_Support]

-- Table: [dbo].[LF29699_new]

SET IDENTITY_INSERT [dbo].[LF29699_new] ON

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=3 WHERE [pkid] = 3

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=2 WHERE [pkid] = 683

SET IDENTITY_INSERT [dbo].[LF29699_new] OFF

You can see from the script that there are 2 differences between the tables. It does not tell you what the values in the table are currently; you do have the primary key value though so you can look it up.

The two tables you compare will need to have primary keys defined on them.

BOL tableDiff.Exe: 'http://msdn.microsoft.com/en-us/library/ms162843.aspx

Moving System Databases: SQL Server 2000 and 2005

Moving System Databases in SQL Server 2005


Notes

Tempdb is built when ever the sql server restarts so you tell it where you want the database then restart sql and it will build the database there. You will then need to delete the old files.

In SQL 2005 the resource database log and data file have to go into the same directory as the master data file.

sp_helpfile will show you where the database files are – good to run after the change to check they are where you think they are.

Move Tempdb

Run the script below using the correct path for the server:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLogs\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO

For this next step you can wait and do the stop /start for msdb
Next stop Sql Server
Restart SQL Server
Go back and delete the old files

Move Model and MSDB Files

First run this – with the filename being the path required

ALTER DATABASE Model MODIFY FILE (NAME = modellog, FILENAME = 'F:\SQLLogs\modellog.ldf');
Go
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLog, FILENAME = 'F:\SQLLogs\msdblog.ldf');
Go
ALTER DATABASE Model
MODIFY FILE (NAME = modeldev, FILENAME = 'E:\SQLData\model.mdf');
Go
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBData, FILENAME = 'E:\SQLData\msdbdata.mdf');
go

Stop Sql Server
Move the files
Restart SQL Server

Move Master and Resource Log

1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server 2005 Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter.

5. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.

6. Move the master.mdf and mastlog.ldf files to the new location.

7. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

1. For the default (MSSQLSERVER) instance, run the following command.

NET START MSSQLSERVER /f /T3608

2. For a named instance, run the following command.

NET START MSSQL$instancename /f /T3608

8. Using sqlcmd commands or SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.

Save the script into a file called c:\move.sql (make sure the paths are right for your server NOTE the ldf and mdf go to the same location as the master mdf

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME=
'E:\SQLData\mssqlsystemresource.ldf');
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME=
'E:\SQLData\mssqlsystemresource.mdf');

Now run sqlcmd
"C:\Program Files\Microsoft SQL Server\90\tools\binn\SQLCMD.EXE" -E -S . -d master /i c:\move.sql

1. Move the files to the new location.

2. Set the Resource database to read-only by running the following statement.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

1. Stop the instance of SQL Server.

2. Restart the instance of SQL Server using the sqlcmd this time without the flags /f /t3608

3. Check file and log locations:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

Moving System Databases in SQL Server 2000
Moving the Master Database

The location of the master database and its associated log can be changed from within SQL Server Enterprise Manager. To do this:

Open SQL Enterprise Manger and drill down to the proper database server.

Right-click the SQL Server in Enterprise Manager and click Properties.
Click the Startup Parameters button and you will see something similar to the following entries:

-dC:\MSSQL\data\master.mdf
-eC:\MSSQL\log\ErrorLog
-lC:\MSSQL\data\mastlog.ldf

Change these values as follows:

Remove the current entries for the Master.mdf and Mastlog.ldf files.

Add new entries specifying the new location:

-dE:\SQLData\master.mdf
-lL:\SQLLogs\mastlog.ldf

Stop SQL Server.

Copy the Master.mdf and Mastlog.ldf files to the new locations

Restart SQL Server.

Moving MSDB and Model

When you are using this procedure to move the msdb and model databases, the order of reattachment must be model first and then msdb.

To move the MSDB follow these steps:

In SQL Server Enterprise Manager, right-click the server name and click Properties. On the General tab, click Startup Parameters.

Add a new parameter as -T3608. Select OK to close the Startup Parameters and the Properties page. You will not be able to access any user databases at this time. You should not perform any operations other than the steps below while using this trace flag.

Drill down to the msdb database and then right click on it. Select Properties and then select the Options tab.

Select Restrict access and then Single User. Close the Properties sheet by selecting OK.

Stop and then restart SQL Server.

Open SQL Query Analyzer and then detach the msdb database using the following commands:

use master
go
sp_detach_db 'msdb'
go

Move the Msdbdata.mdf and Msdblog.ldf files from the current location to the new location.

Reattach the MSDB database as using the following commands:

use master
go
sp_attach_db 'msdb','E:\SQLData\msdbdata.mdf','L:\SQLLogs\msdblog.ldf';
go
Remove the -T3608 trace flag from the Startup Parameters box in the SQL Enterprise Manager.

Stop and then restart SQL Server.
Moving Tempdb

*** This is the same as the SQL 2005 method ***

Run the script below using the correct path for the server:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQL_Logs\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQL_Data\tempdb.mdf');
GO

Next stop Sql Server
Restart SQL Server
Go back and delete the old files

Reporting Services Email Subscriptions SSRS 2005

This assumes a user does not have Content Manager as their SSRS role and SSRS is set up more or less as it comes out of the box.

The Problem

If a user hits subscribe on a report then the chooses email as the delivery option they will see that the To field has been pre-populated with their windows login. They will not have the permissions need to change this setting. This is fine if you are running Exchange as it will resolve the login against the Active Directory and work out the real email address.

If you are not running Exchange it is not so fine.

The fix

You need to make a change to the rsreportserver.config file.

This should be under C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer by default – if you installed SQL somewhere else then the first part of this path will be wrong.

Open up this file and look for a value <sendemailtouseralias> under <rsemaildpconfiguration>. If this is set to True, as it is by default, you will get the problem above. If it is set to False the To field will be blank and the user can enter an email address.

An error occurred while executing batch

I ran a query which resulted in this:
An error occurred while executing batch. Error message is: The directory name is invalid.

The reason for this was an invalid temp directory. The system was pointing at c:\temp but the directory did not exist. I re-pointed all the temp and tmp environment variables at the real temp directory and all was well again.

MCITP: Database Administrator.

I passed the 70-444 exam today which is the final exam in the MCITP: Database Administrator trilogy.
This exam was 52 multiple choice questions - old style mcp type exam.
The 70-443 was a set of 6 case studies with 12 questions on each.

I find that after revising for a while I begin to get what they are testing and can normally answer the question based on the answer options - although reading the question helps!

For the 70-443 exam I skimmed through the case studies and then went back to look at the specifics as they related to the individual questions.

I have still got to finish off the 2000 MCDBA. I have the 2 SQL exams just need to do the windows admin exams.

Step by Step guide to log shipping on SQL Server 2005

What it is and how it works.
Log shipping is the process of transferring transaction logs from one server to another – shipping them. It is used as a form of disaster recovery for a database. It does not have any automated fail over – transfer to the new database will involve human intervention.

Two servers are involved, the Primary server and the Secondary Server. The Primary server is the server with the database that is being protected. The secondary server holds the backup copy of the database.

The database to be protected needs to be in Full or Bulk Logged recovery mode. This is to stop the transaction logs being truncated. A full backup should then be taken and applied to the secondary server. The option No Recovery is used when restoring on the secondary server. This leaves the database in a restoring state. No log shipping is set up and transaction log backups are copied from the primary server to the secondary server and then applied to the restoring database. This has the affect of keeping the database in step with the primary version.

As an extra process login accounts need to be transferred to the secondary server. This way if the database ever has to be brought on line and used access will be the same as it is on the primary server.

How to set up Log Shipping
In this step by step guide I will set up log shipping on the DataImports database from serverA to serverB.

Create a directory on the primary server
On the primary server create a directory for the transaction log backups. The backups will be made into this directory and then copied across to a receiving directory on the secondary server.
I create this under the main maintenance or sql backup directories.
If there is not one there already create a folder called LS. Then create a sub-folder with the name of your database: \\serverA\m$\Backups\LS\dataImports

Create a directory on the secondary server
On the secondary server create a shared directory for receiving the log shipped files.
Again I create this main maintenance or sql backup directories.
If there is not one there already create a folder called LS. Then create a sub-folder with the name of your database: file://serverb/f$/Maint/LS/dataImports

Exclude the database from TLog backups
If you have any maintenance plans which carryout Transaction Log backups for this database you will need to exclude it.

Set recovery model of the database to Full
If the recovery model of the database is simple set it to Full. You can leave it if it is set to Bulk Insert.

Backup the database
Take a full backup of the database in to the folder you created on the primary server and copy it to the new folder on the secondary server.

Create a database on the secondary server and then restore the backup
Create a new database with the same name as the one you are going to log ship.
Now restore the database using the backup you have just taken – make sure you set the file paths to restore over the files you have just created and not try to use the paths the backup sets.
You must select the option to restore with no recovery.

Click OK.

The database name will now have a green arrow next to it and say restoring …
You can also delete the backup file you used for the restore.

Configure Log Shipping
In SSMS right click on the database name and select properties.
Click on Transaction Log Shipping. Now tick the box which says “Enable this as a primary database in a log shipping configuration”
The button ‘Backup Settings’ will now be enabled. Click this and fill out the options.


You should enter the full path to the folder on the primary server. Underneath this you should also enter the local path. In my example the dataimports data rarely changes so I have set different values from the default. I am deleting files older than 24 hours – 72 is the default. I am alerting on 2 hours where 1 is the default. Next click on the schedule button. As the database I am using does not change very often I have scheduled my backups to take place once an hour. The default is every 15 minutes.

Click OK.

You are now on the configuration screen again. There is a white box where it says secondary server instances and databases. Underneath the box click add. Click on the button at the top right which says connect. Enter the secondary server details. The database should be selected by default.

Now you are back on the secondary database settings make sure the option ‘no, the secondary database is initialised’ has been selected. Now click the Copy Files tab. In this screen you need to enter the path for the directory on the secondary server: file://serverb/f$/Maint/LS/dataImports
I change the deleted copied files option to 24 hours again. Now click schedule. I change this to run every hour again – the default is 15 minutes. This time I will also change the start time to 00:15. There is no point it trying to copy the files on the hour as the actual backup is being taken on the hour. Click Ok and that task will be scheduled.

Now click on the Restore Transaction Job task. I change the delay option to 5 minutes and the alert option to 2 hours. This is because I have a slow rate of change and so only copy every hour. Click on schedule again. This time I will set the task to run at 30 minutes past the hour and to run hourly. Click ok and then on the next screen click on again.

You are now on the properties page again.
Click ok again and the server will create the log shipping jobs. Once the two items have gone green you can click close.

Run the Log Shipping Jobs.
On the primary server there will be a job named like LSBackup_DatabaseName.
Run the LSBackup_DatabaseName job and check that a trn backup file has appeared in the folder you created on the primary server.

On the secondary server look for a job like LSCopy_PrimaryServerName_DatabaseName and run this job. You should see the trn file appear in th efoledr you created on ServerB. Now run LSRestore_PrimaryServerName_DatabaseName.

Finally Transfer Logins
You should use SSIS to create a transfer logins task to copy the logins from serverA to serverB. This way if you have to fail the database over to serverB you can enable to logins and won't have any permissions problems.

Change the Default Backup Directory

I have used this on most of my servers so that when I click restore or Backup database it doesn’t go off to a directory under programme files on the C drive and goes to the root of the backup directory I use.

SQL 2005 version

This part of the script will display the current default backup directory.

DECLARE @BackupDirectory VARCHAR(100)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',

@value_name='BackupDirectory',

@BackupDirectory=@BackupDirectory OUTPUT

SELECT @BackupDirectory as BackupDirectory

This script will set G:\Maintenance\userData as the new default directory.

EXEC master..xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',

@value_name='BackupDirectory',

@type='REG_SZ',

@value='G:\Maintenance\userData'

SQL 2000 version

DECLARE @BackupDirectory VARCHAR(100)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\MsSQLServer\MsSQLServer',

@value_name='BackupDirectory',

@BackupDirectory=@BackupDirectory OUTPUT

SELECT @BackupDirectory as BackupDirectory

EXEC master..xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\MsSQLServer\MsSQLServer',

@value_name='BackupDirectory',

@type='REG_SZ',

@value='G:\Maintenance\userData'

Upgrading SQL Server 2005

Upgrading SQL Server Standard to Developer Edition on Dev and Test servers – Stand alone servers. I have also used this to upgrade back again - Developer to Standard Edition.

Make sure you have backups of your System and user databases before you begin. Take a backup of your system databases before you apply the service pack. Take another backup of them when you have finished the hot fix install.

You should probably reboot the server before you begin to make sure you have a clean slate to work with.

Create a folder called sqlAdmin on the C drive.
Create a sub folder called SQL2005
Create two sub folders called Servers and Tools.
Copy the contents of CD1 into the Servers directory and CD2 into the Tools Directory.

You will also need to copy the latest service pack and hotfixes on to the server ready for installing after the upgrade: an upgrade takes the product level down to RTM.

In the sqlAdmin directory create a text file called upgrade.cmd and add this text to it:

@echo off

Set UPGRADE = SQL_Engine, SQL_Data_Files, SQL_Tools90, SQL_Replication

Set INSTANCENAME=MSSQLSERVER

Start /wait c:\sqlAdmin\SQL2005\servers\setup.exe /qb UPGRADE=%UPGRADE% SKUUPGRADE=1 INSTANCENAME=%INSTANCENAME%
:: END OF CMD TEXT FILE

This assumes no Reporting Services or Analysis Serivces. If you have them you may need to add RS_Server, RS_Web_Interface, Analysis_Server, AnalysisDataFiles to the list in Set UPGRADE=

After the upgrade you need to install the service pack and hot fixes.
Reboot the server once you have finished.

Problems

I had a problem with the upgrade failing when Reporting Serivces was on the server “sql bpa command line has encountered a problem and needs to close”. To fix this I created an extra directory under c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\ called BPAClient and in it I copied the file BPAClient.dll from the c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin\ directory.

BPA failed: http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/2a5f460b-8684-4510-8fcb-1e9d9786baff
 
I have not yet managed to upgrade SSAS - SSRS was fine but SSAS maintains the same version - If I ever work this I'll I will update this post. What I do instead is backup the databases then uninstall the old version and install the new edition.

Cluster install: SQL Service keeps auto-restarting

The set up was a SQL Server 2005 Cluster - the service was not failing over it was simply restarting. The error log would read something like - The service responded to a stop command from the cluster manager. There was no record of any problem in the SQL error log.

The first time it occurred whilst I was working here – it had happened before then – it looked like this:
At 00:29 the cluster manager issued a Stop command to the SQL Service. This caused the SQL Service to Stop. It was followed by a Start command and the service came back up again. The server was down for around 5 minutes.

There was no clue as to why it had happened except in the Windows Event Viewer which had a lot of lost communication and 19019 errors. These event errors corresponded with SQL jobs failing with server timeout errors – lost communication with the server - on tasks such as update stats in my maintenance plans (even though these are run directly on the server).

It carried on happening as I thought it was network cards and CPU affinity:
http://support.microsoft.com/kb/174812
http://www.sqlservercentral.com/Forums/Topic399630-149-1.aspx
http://support.microsoft.com/kb/892100

After a lot of tinkering and Googling it was resolved by setting Priority Boost to 0 – Microsoft recommends this for all cluster installs. After changing the setting you need to restart the SQL service. It is now servral months since this all happened and I no longer get any of these errors.
Even if you are not encountering any problems you ought to set PB to 0 (if it isn't already) as these restarts can happen at any time.