SQLDaveOtt

Passed Exam 70-455

noreply@blogger.com (Dave Ott) — Wed, 24 Aug 2011 01:02:00 +0000

I passed exam 70-455 which is the upgrade from MCITP SQL Server 2005 Business Intelligence Developer to MCITP SQL Server 2008 Business Intelligence Developer.

Upgrading Reporting Services from 2005 to 2008

noreply@blogger.com (Dave Ott) — Sat, 21 Aug 2010 20:51:00 +0000

I was given the task of upgrading our 2005 SSRS to 2008 SSRS. This was not an in-place upgrade; we moved SSRS to a Virtual Machine with 2008 OS-64 bit. We have been using CNAMEs for a while, so switching servers is not a problem. A CNAME is basically an alias for a server at the network level. Our CNAME for our SSRS server is DWReporting, so everyone connects to http://DWReporting/Reports. Just a note on CNAMEs: I've had trouble connecting to Replication in SQL Server Management Studio 2005 so I tend not to use it there.

Environment
Our Network/Operations Team showed me how to backup and restore the VM via snapshots. This worked out great as I was fumbling along setting up the VM and SQL Serer 2008. I do not suggest that you use this method to backup your SQL Server Databases.

The one thing that was a gotcha trying to install SQL Server 2008 was that the .Net Framework could not be upgraded. I had to add that role to the server. There is no IIS in SSRS 2008 (which is great, by the way) so that does not need to be enabled/loaded. I installed just SQL Server (64 bit) and SSRS. We use SharePoint and will eventually get to integration with SSRS, but not now.

Upgrading SSRS to 2008 and Testing

I took a backup of the 2005 SSRS and the encryption key. I restored the databases and the encryption key on the 2008 version. Once SSRS was working, I created a backup job for all the databases and backed up the key to the backup directory. To eliminate confusion, I did not enter the SMTP information for emailing reports since our production was still running and I did not want reports to be delivered from both servers.

I ran into issues with both users who had IE8 and users who did not have IE8. It was the annoying authentication prompt for a user name and password. I had all non-IE8 users upgrade to IE8, and the users on IE8 had to reset the setting to default (Tools\Internet Options\Advanced Reset button - make sure that close IE afterward). I was surprise how long (days) it took us (the Net/Ops guys and myself) to find the solution to this.

We had Dundas Gauges and charts so I went through and viewed all reports and modified the reports that needed it. We keep our reporting project in VSS. I kept every thing checked in and made changes to local copy. I made sure all reports were checked in before we started. We have a group of four that can check out and work on reports, so I warned everyone early in the week to check reports in.

Moving Day- Go live
I did this during our maintenance window on a Sunday morning:

I restored the latest 2005 SSRS backups.

Disabled IIS on the 2005 server and made the databases read only, just in case.

Contacted the Net/Ops guy on call to change the CNAME (I did coordinate with him before hand).
I updated those reports that I modified
Added SMTP information for the emailing reporting

The Day After

I had to deal with the IE8 issue (or I guess you would call it the non IE8 issue). I underestimated the number of users that would have problems.
I checked out our Reporting Project from VSS in BIDs 2008 and upgraded it 2008

Moved in modified reports that I updated and checked them in

Report Manager seems faster moving from folder to folder. I believe this is because there is no IIS. Other than the IE upgrade issue, we really have had no other problems.

Update

Users were getting a repeated NT login prompts and the solution was to reset the IE internet options.

We also noticed that when creating or editing a subscription if we edited the schedule the TO Email line was wiped out and left blank.

We recently upgraded to SQL Server 2008 SP2, this resolved email in the subscriptions and seems to elevate the NT login prompt as well

Clustered SQL Servers - lessons learned

noreply@blogger.com (Dave Ott) — Fri, 13 Nov 2009 01:14:00 +0000

We have an active/passive, clustered in our production environment. We have two SQL instances on the passive node, we do this so we don’t have a perfectly good server sitting there doing nothing. Our main application used to report off our production database, but we replicate the databases to the secondary as well as to the warehouse, so the application on the primary can report off the secondary. I feel I have to point out that it was not designed by the company the source code was purchased and we continue to enhance and expand it. The other instance on the secondary instance is used as a “warm stand by” backup Instance in case another application database fails. Note this warm backup is not mirroring. So we have our main database server on primary, a reporting instance on the secondary and an additional instance that just needs to be up and ready.

Active/Active Cluster
This is not an Active/Active cluster; an Active/Active cluster is really two clusters, each with its own shared storage accessible only from the primary node. Each server is a primary on its cluster and secondary on the other cluster, assuming only two nodes in the cluster. If one server fails the other will run all instances.

Active/Passive Cluster
In Active/Passive cluster the primary can fail over to the secondary, but if the secondary server fails then you have to run around and find a replacement server or servers for what resided on it. So whatever you install on the secondary on an Active\Passive cluster make sure it is not critical. In our case we have a plan if the secondary goes down. Of the two instances on the secondary, one is just a warm stand by it has an up to date copy of the databases to take over the application when we decide, so its only critical if the main database server goes down. The instance for reporting is for the application and we also replicate to our data warehouse server for reporting servers and for feeds for the data warehouse nightly process. We have two options here one is point the application reporting to the main database on the primary, this is not desirable for an extended period, but it is the easiest to do. The second option is to point the application reporting to our warehouse/reporting services copy of the data. This is the desirable solution, but it does take some work. We actually have two projects; one to point the application reporting to our data warehouse copy of the data and the second is rewrite the reports in SSRS. But with all projects there are always others that are higher priority and have revenue attached to it

Memory and Clusters
When the primary fails over all three instances reside on the secondary. One thing you have to be careful of in cases like this is the memory allocation, you don’t want to over allocate the memory. For example our secondary and servers each have 6 GB of RAM, you want to leave 1 GB for the OS and then divide the remaining 5GB up between the instances. In our case we gave 4GB to the main instance in the cluster, 1 GB of the reporting instance and 50 MB to the warm standby. If instead I gave each instance 3GB and set that as the minimum memory limit for each instance, so the total allocation of 9 GB of RAM when there is only 6 GB of RAM the memory will be over allocated. Um yeah that would not be good.

Applications and Clusters
We have several SSIS packages on our primary database server that encrypt data and FTP/FTPS the data to and from other sites. We use GNU pgp for the encryption and a third party FTP software to send and retrieve data. With applications, like these, that are not cluster aware, they have to be loaded on all nodes in a cluster. This became painfully obvious this past week when our primary server failed. What we had on the secondary was an outdated GNU pgp files and an evaluation copy of the FTP software that was outdated. The GNU pgp was easy to resolve, since its file based I just copied the directory over. Also GNU pgp could have been set up on a non C: drive. My best guess is that when it was developed on local machines there was only a C: and was set up that way in SSIS packages. We’re manually sending retrieving files with the FTP on another server for now until we can install a valid version of the FTP software on the secondary node.

Conclusion

As you plan out your cluster, design it with these lessons learned; For an Active/Passive Cluster keep the secondary free of other SQL instances (and other software that SQL Server does not need) and accept the fact that it will just sit there “unused” waiting for the day for it to take over and rule the cluster’s world. For either Active/Active or Active/Passive that has a secondary with SQL instances residing on it allocate memory accordingly and do not over allocate the memory. And lastly install software that is not cluster aware on all nodes, and UPDATE IT!!

Disaster Recovery

noreply@blogger.com (Dave Ott) — Mon, 26 Oct 2009 00:56:00 +0000

Disaster Recovery

For a while we had a disaster recovery site and once a quarter we would bring our backups and restore the databases and we would see how far we could get bringing the business backup in one day. The site just had Servers, I got them after the OS was loaded, so I had to load SQL Server, and start restoring. We did not try to get everything up and running the first try, we set a goal of the databases and the main website and try to processes some business. Then in later visits we added to what we knew we could do, adding Phones (Voice over IP), Domains etc. The first time I just restored from a backup, the next and subsequent visits, I did a point in time restore using log restores. Oh and we have a ton of SQL logins that needed to be restored as well. So once a quarter for over two years we did this, after the second time of figuring out the restore script and restoring the logins etc I thought that there has to be a better way. This is what I came up with.

The first thing I did was create a directory in the backup path so I knew it was being backed up and would be available during our restore. I put the following scripts in this directory as well as other things that were needed:

· SQLDTSFTPTask.msi which is a FTP Task for DTS packages

· SQLServer2005_DTS.msi – since we are on 2005 and running DTS package we need the DTS add on

· Current SQL Service Pack that servers are on.

· Notes.txt – General instructions, things that were encountered

· CreateDatabaselist.sql – creates a list of databases and their logic file names. Most likely the database files will have to move

· Databaselist.txt – List of databases and their logical file names. We only have a primary file group, so if you have more than one you have to try something different.

· CreateRestoreScripts.sql – this reads the databaselist.txt and given the path of the backups returns the restore scripts by reading the header information of the backups.

· CreateLogins.sql – Script to recreate the SQL logins., Output from sp_help_revlogin

We always did a postmortem after wards and as things came up that would be help I added to the Notes.txt file. For example initially I did not have the SQL logins, but I did have a back up of master, so I put instructions on restoring master in the notes.TXT. You have to be in single user mode to restore master and I’m pretty sure, like msdb, the server that you are restoring to has to be at the same service pack that the backup is from. I was not too keen of restoring the master, but I was in a crunch.

"CreateDatabaselist.sql"

Put the output from this into the file Databaselist.txt

SET nocount ON

SELECT d.NAME + ',' + MAX(CASE WHEN m.name NOT LIKE '%log' THEN m.name

ELSE ''

END) + ','

+ MAX(CASE WHEN m.name LIKE '%log' THEN m.name

ELSE ''

END)

FROM sys.Master_files m

JOIN sys.databases d

ON m.database_id = d.database_id

WHERE d.name NOT IN ( 'tempdb', 'model', 'distribution' )

GROUP BY d.name

Here is some sample output:

database1,database1_Data,database1_Log

database2,database2_Data,database2_Log

database3,Special_Data,Special_Log

For the above script I put it in a job to create the Databaselist.txt, the same one I run sp_help_revlogin.

The Script that does the heavy lifting is CreateRestoreScripts.sql below. It has been a while since I ran this and one thing we did for our recovery test was put the backups onto USB drivers so it was easier to restore. And yes we did test the restores from tape, but we knew that worked and we had limited time. I have to say that this script evolved. I think the first time I did a point in time I scripted the restores of the .TRN files sequentially, knowing full well that the backup was done in the middle some were. So when I ran the script and tried to load the earlier log files it spit out errors until it hit the right LSN number and from there on it loaded all subsequent files. LSN is the Log sequence number and what is looked at for the sequences of files to restores. Comments are sparse in the script, sorry, so here are some notes:

· Assumes Backup path is formatted DatabaseName\backups

· Full backups are .BAK, Differentials are .DIF, and logs are .TRN

· The #Header table I took from a script online way back when and did not record the author, my bad, thanks to that unknown SQL person

· This uses DOS commands to get the list of files, here are some of the commands used

o Dir /B – gives only file names

o For a% in (PATH) do Echo … - this loops through all files in PATH and uses %a as variable, %~ta is the timestamp of that file

· This does the INSERT INTO “table” Exec proc – I try to avoid this because of performance issues I’ve run into, but this is such a limited use it’s OK.

· When restoring databases to a point in time they must restored WITH STANDBY, which this script does the last thing it does is restore the database with recovery so it’s ready for use.

SET NOCOUNT ON

-- set locations

-- backups location

DECLARE @BackUpPath VARCHAR(250)

SET @BackUpPath = 'E:\SQLSERVER\BACKUPs\'

DECLARE @NewSQLPath VARCHAR(250)

SET @NewSQLPath = 'D:\SQLSERVER\Data\'

/* tables used */

-- table of actual db data

DROP TABLE #DBINFO

CREATE TABLE #DBINFO

(

DBNAME SYSNAME,

DataFile VARCHAR(250),

PathDataFile VARCHAR(250),

LogFile VARCHAR(250),

PathLogFile VARCHAR(250)

)

BULK INSERT #DBINFO FROM 'E:\SQLSERVER\BACKUPs\DR Scripts\Databaselist.txt'

WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

-- create table to insert from cmdshell

DROP TABLE #files

CREATE TABLE #files ( FILNAME VARCHAR(1000) )

-- this is used to get the list of backup files from the backup system

DROP TABLE #FileList

CREATE TABLE #FileList

(

FileDate DATETIME,

FILNAME VARCHAR(250)

)

-- Restore header only Result set

DROP TABLE #Header

CREATE TABLE #Header

(

BackupName NVARCHAR(128),

BackupDescription NVARCHAR(255),

BackupType SMALLINT,

ExpirationDate DATETIME,

Compressed TINYINT,

Position SMALLINT,

DeviceType TINYINT,

UserName NVARCHAR(128),

ServerName NVARCHAR(128),

DatabaseName NVARCHAR(128),

DatabaseVersion INT,

DatabaseCreationDate DATETIME,

BackupSize NUMERIC(20, 0),

FirstLSN NUMERIC(25, 0),

LastLSN NUMERIC(25, 0),

CheckpointLSN NUMERIC(25, 0),

DatabaseBackupLSN NUMERIC(25, 0),

BackupStartDate DATETIME,

BackupFinishDate DATETIME,

SortOrder SMALLINT,

CodePage SMALLINT,

UnicodeLocaleId INT,

UnicodeComparisonStyle INT,

CompatibilityLevel TINYINT,

SoftwareVendorId INT,

SoftwareVersionMajor INT,

SoftwareVersionMinor INT,

SoftwareVersionBuild INT,

MachineName NVARCHAR(128),

Flags INT,

BindingID UNIQUEIDENTIFIER,

RecoveryForkID UNIQUEIDENTIFIER,

Collation NVARCHAR(128),

FamilyGUID UNIQUEIDENTIFIER,

HasBulkLoggedData BIT,

IsSnapshot BIT,

IsReadOnly BIT,

IsSingleUser BIT,

HasBackupChecksums BIT,

IsDamaged BIT,

BeginsLogChain BIT,

HasIncompleteMetaData BIT,

IsForceOffline BIT,

IsCopyOnly BIT,

FirstRecoveryForkID UNIQUEIDENTIFIER,

ForkPointLSN NUMERIC(25, 0) NULL,

RecoveryModel NVARCHAR(60),

DifferentialBaseLSN NUMERIC(25, 0) NULL,

DifferentialBaseGUID UNIQUEIDENTIFIER,

BackupTypeDescription NVARCHAR(60),

BackupSetGUID UNIQUEIDENTIFIER

)

DECLARE @sql VARCHAR(3000)

-- pulls double duty as a SQL command string and a DOS command string

-- loop through the databases and load them

DECLARE @dbname VARCHAR(250),

@DbbackupFilename VARCHAR(250),

@lsn VARCHAR(50),

@DataFileName VARCHAR(250),

@DataFilePath VARCHAR(250),

@LogFileName VARCHAR(250),

@logFilePath VARCHAR(250),

@filedate DATETIME

DELETE FROM #DBINFO

WHERE dbname <> @DBNAME

SET @dbname = ( SELECT MIN(dbname)

FROM #DBINFO

)

WHILE EXISTS ( SELECT TOP 1

FROM #DBINFO )

BEGIN

PRINT '/****** ' + @dbname

+ ' ************/'

SELECT @DataFileName = DataFile,

@DataFilePath = PathDataFile,

@LogFileName = LogFile,

@logFilePath = PathLogFile

FROM #DBINFO

WHERE dbname = @dbname

-- get the list of backup files

SET @sql = 'dir ' + @BackUpPath + @dbname + ' /B'

INSERT INTO #files

EXEC master..xp_cmdshell @sql

-- CLEAN UP NULL VALUE

DELETE FROM #files

WHERE filname IS NULL

-- find the lastest .BAK file

SET @DbbackupFilename = ( SELECT TOP 1

FILNAME

FROM #files

WHERE filname LIKE '%.bak'

ORDER BY FILNAME DESC

)

-- get last Lsn number from backup

SET @sql = 'restore headeronly from disk=''' + @BackUpPath + @dbname

+ '\' + @DbbackupFilename + ''''

DELETE FROM #Header

INSERT INTO #Header

EXEC ( @sql

)

SET @lsn = ( SELECT MAX(LastLSN)

FROM #header

)

SET @filedate = ( SELECT BackupFinishDate

FROM #header

)

SET @SQL = 'RESTORE DATABASE ' + @DBname + ' FROM DISK = '''

+ @BackUpPath + @dbname + '\' + @DbbackupFilename + '''

WITH MOVE ''' + @DataFileName + ''' TO ''' + @NewSQLPath + @DataFileName

+ '.mdf'',

MOVE ''' + @LogFileName + ''' TO ''' + @NewSQLPath + @LogFileName

+ '.ldf'',

REPLACE, STANDBY = ''' + @BackUpPath + @DBname + '\' + @DBname

+ '_Undo.dat'''

PRINT @SQL

-- remove ALL FULL BACKUPS FROM LIST

DELETE FROM #files

WHERE filname LIKE '%.bak'

TRUNCATE TABLE #files

SET @sql = 'for %a in (' + @BackUpPath + @dbname

+ '\*.dif) do @echo INSERT INTO #FileList Values(''%~ta'',''%a'')'

INSERT INTO #files

EXEC master..xp_cmdshell @sql

DELETE FROM #files

WHERE ISNULL(FILNAME, 'XX') NOT LIKE 'INSERT%'

WHILE EXISTS ( SELECT *

FROM #files )

BEGIN

SET @sql = ( SELECT MIN(FILNAME)

FROM #files

)

PRINT @sql -- just to verify it looks at all the .dif files

EXEC ( @sql

)

DELETE FROM #files

WHERE @sql = FILNAME

END

DELETE FROM #FileList

WHERE filedate < @filedate

-- Load differentials

WHILE EXISTS ( SELECT TOP 1

FROM #FileList

WHERE filname LIKE '%.dif' )

BEGIN

-- get next file name

SET @DbbackupFilename = ( SELECT TOP 1

FILNAME

FROM #FileList

WHERE filname LIKE '%.dif'

ORDER BY FILNAME ASC

)

-- get last Lsn number from backup

SET @sql = 'restore headeronly from disk='''

+ @DbbackupFilename + ''''

-- clear out header table and repopulate

DELETE FROM #Header

INSERT INTO #Header

EXEC ( @sql

)

SET @filedate = ( SELECT BackupFinishDate

FROM #header

)

-- if LSN is greater than last back up then load

IF ( SELECT LastLSN

FROM #header

) >= @LSN

BEGIN

SET @sql = 'RESTORE DATABASE ' + @DBName

+ ' FROM DISK=''' + @DbbackupFilename

+ ''' WITH STANDBY=''' + @BackUpPath + @DBname

+ '\' + @DBname + '_Undo.dat'''

PRINT @SQL

SET @lsn = ( SELECT LastLSN

FROM #header

)

END

DELETE FROM #FileList

WHERE filname = @DbbackupFilename

END

-- load logs

TRUNCATE TABLE #files

TRUNCATE TABLE #FileList

SET @sql = 'for %a in (' + @BackUpPath + @dbname

+ '\*.trn) do @echo INSERT INTO #FileList Values(''%~ta'',''%a'')'

INSERT INTO #files

EXEC master..xp_cmdshell @sql

DELETE FROM #files

WHERE ISNULL(FILNAME, 'XX') NOT LIKE 'INSERT%'

WHILE EXISTS ( SELECT *

FROM #files )

BEGIN

SET @sql = ( SELECT MIN(FILNAME)

FROM #files

)

PRINT @sql --just to verify that it looks at all .trn files

EXEC ( @sql

)

DELETE FROM #files

WHERE @sql = FILNAME

END

-- remove old files

DELETE FROM #FileList

WHERE filedate < @filedate

WHILE EXISTS ( SELECT TOP 1

FROM #FileList

WHERE filname LIKE '%.trn' )

BEGIN

-- get next file name

SET @DbbackupFilename = ( SELECT TOP 1

FILNAME

FROM #FileList

WHERE filname LIKE '%.trn'

ORDER BY FILNAME ASC

)

-- get last Lsn number from backup

SET @sql = 'restore headeronly from disk='''

+ @DbbackupFilename + ''''

-- clear out header table and repopulate

DELETE FROM #Header

INSERT INTO #Header

EXEC ( @sql

)

-- if LSN is greater than last back up then load

IF ( SELECT LastLSN

FROM #header

) >= @LSN

BEGIN

SET @sql = 'RESTORE LOG ' + @DBName + ' FROM DISK='''

+ @DbbackupFilename + ''' WITH STANDBY='''

+ @BackUpPath + @DBname + '\' + @DBname

+ '_Undo.dat'''

PRINT @SQL

SET @lsn = ( SELECT LastLSN

FROM #header

)

END

DELETE FROM #FileList

WHERE filname = @DbbackupFilename

END

SET @sql = 'Restore Database ' + @DBName + ' with recovery'

PRINT @SQL

DELETE FROM #DBINFO

WHERE dbname = @dbname

SET @dbname = ( SELECT MIN(dbname)

FROM #DBINFO

)

END

Postings

noreply@blogger.com (Dave Ott) — Mon, 19 Oct 2009 11:18:00 +0000

I just wanted to add a note about my posts. I’ve been posting once a month, but I have new goal of posting once a week. The main content that I’ve been posting is from a mini presentation I did for a SQL Saturday here in Atlanta and again to the user’s group Atlanta.MDF. They are mainly DBA helpful stored procedures that I created and modified over the years. I have a few more from this content, and then I’ll have to come up with new stuff to post. My dad was in the newspaper business back in the day, although he had daily deadlines, I’m giving myself a weekly one for my postings

Application Alerts!

noreply@blogger.com (Dave Ott) — Sun, 18 Oct 2009 21:07:00 +0000

We had a need to check application processes once and a while to be sure that they are running. So I created this table and stored procedure for an alerts jobs or exception reporting. At the time we did not have Data Driven Subscriptions in SSRS, which can do the same thing as this process, and I haven’t had the time to convert it.

It’s a simple structure; the table holds the information about what to check, the stored procedure to run using sp_ExecuteSQL, when to run it, how often to check it, who to send emails to using our friendly SQL mailer sp_send_dbmail. The store procedure reads from the table, figures out which alerts need to run. The store procedure that gets called needs to have two Output parameters @AlertStatus (INT) that will be 1 or 0 and @Info Varchar(500) which is additional information that will be put into the email. See BOL on how to use sp_send_dbmail and sp_ExecuteSQL.

Create a job and a schedule that runs the stored procedure, Alert_Monitor, at the least interval needed

Table:

Create table Alerts(

AlertID int identity(1,1), -- Unique alert ID

StoredProcedure nvarchar(200), -- Stored proc to run - must have @AlertStatus tinyint OUTPUT

EmailRecipients nvarchar(200), -- Who gets emails - comma separated list

EmailSubject nvarchar(100), -- Subject -- servername will be prefaced so we know live from test

EmailBody nVarchar(300), -- Email Body

AlertStart24Hour int, -- 24 hour/minutes clock when to start

AlertStop24Hour int, -- 24 hour/minutes clock when to stop

AlertMonday [bit],

AlertTuesday [bit],

AlertWednesday [bit],

AlertThursday [bit],

AlertFriday [bit],

AlertSaturday [bit],

AlertSunday [bit],

MinutesBetweenChecks int, -- How many minutes to wait before checking again

WhoOwnsThisAlert varchar(100), -- just to keep track of ownership

LastCheckTime DATETIME DEFAULT (GETDATE()), -- last check time

IncludeOnDeveloperOnCall [bit]

)

The last field IncludeDeveloperOnCall was added so whenever it’s run it will look at a table that tells which developer is on call and to get it and add the email/page to the recipients of the email.

Code:

CREATE PROC [dbo].[Alert_Monitor]

AS /*

created 10/23/2007

This looks at the alerts defined in the Alerts Table in DBA_OPS

This will run every 5 to 20 minutes as a job.

SET nocount ON

-- define temp table and load with alerts to check

DECLARE @Alerts TABLE

(

AlertID INT,

processed INT DEFAULT ( 0 )

)

INSERT INTO @Alerts ( AlertID )

SELECT AlertID

FROM Alerts WITH ( NOLOCK )

-- Are we between the start and stop time?

WHERE DATEPART(HOUR, GETDATE()) * 100 + ( DATEPART(mi, GETDATE()) ) BETWEEN AlertStart24Hour

AND AlertStop24Hour

-- is it time to run it again?

AND DATEADD(mi, MinutesBetweenChecks, LastCheckTime) <= GETDATE()

-- are we running it today?

AND CASE DATEPART(dw, GETDATE())

WHEN 1 THEN AlertSunday

WHEN 2 THEN AlertMonday

WHEN 3 THEN AlertTuesday

WHEN 4 THEN AlertWednesday

WHEN 5 THEN AlertThursday

WHEN 6 THEN AlertFriday

WHEN 7 THEN AlertSaturday

END = 1

DECLARE @storedProcedure NVARCHAR(130),

@Recipients NVARCHAR(200),

@Subject NVARCHAR(200),

@Message NVARCHAR(500),

@ReturnCode INT,

@ReturnInfo VARCHAR(500),

@ParameterDefinition NVARCHAR(50),

@AlertID INT,

@email VARCHAR(50)

DECLARE @RecTable TABLE ( email VARCHAR(50) )

SET @ParameterDefinition = N'@AlertStatus INT OUTPUT,@Info VARCHAR(500) OUTPUT'

SET @Recipients = ''

-- add any other email/pagers from other source table – Developer on call Table

INSERT @RecTable

SELECT SomeEmail

FROM Emailtable

WHERE GETDATE() BETWEEN StartDate AND EndDate

-- create list of recipients

WHILE EXISTS ( SELECT *

FROM @RecTable )

BEGIN

SET @email = ( SELECT MIN(email)

FROM @RecTable

)

SET @Recipients = CASE WHEN LEN(@Recipients) = 0 THEN ''

ELSE @Recipients + ';'

END + @email

DELETE FROM @RecTable

WHERE email = @email

END

-- loop through alerts and check

WHILE EXISTS ( SELECT *

FROM @Alerts )

BEGIN

SET @ReturnInfo = ''

SET @ReturnCode = 0

SET @AlertID = ( SELECT MIN(AlertID)

FROM @Alerts

)

SET @storedProcedure = ( SELECT StoredProcedure

FROM Alerts WITH ( NOLOCK )

WHERE AlertID = @AlertID

)

SET @storedProcedure = N'EXEC ' + @storedProcedure

+ N' @AlertStatus OUTPUT,@Info OUTPUT'

-- call stored proc

EXEC master.dbo.sp_executesql @storedProcedure,

@ParameterDefinition, @AlertStatus = @ReturnCode OUTPUT,

@Info = @ReturnInfo OUTPUT

-- if returned nonzero then send alert

IF @ReturnCode <> 0

BEGIN

SET @Recipients = ( SELECT EmailRecipients

+ CASE WHEN IncludeOnDeveloperOnCall = 1

THEN ';' + @Recipients

ELSE ''

END

FROM Alerts WITH ( NOLOCK )

WHERE AlertID = @AlertID

)

SET @Subject = ( SELECT EmailSubject

FROM Alerts WITH ( NOLOCK )

WHERE AlertID = @AlertID

) + N' ' + @@SERVERNAME

SET @Message = ( SELECT EmailBody

FROM Alerts WITH ( NOLOCK )

WHERE AlertID = @AlertID

)

SET @Message = @Message

+ CASE WHEN LEN(@ReturnInfo) > 0 THEN '

Additional info - ' + @ReturnInfo

ELSE ''

END

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mailprofile',

@recipients = @Recipients, @body = @Message,

@subject = @Subject, @body_format = 'HTML'

END

-- update last check time and go onto the next alert to check

UPDATE Alerts

SET LastCheckTime = GETDATE()

WHERE AlertID = @AlertID

DELETE FROM @Alerts

WHERE AlertID = @AlertID

END

Getting notified of structure changes via DDL Triggers

noreply@blogger.com (Dave Ott) — Tue, 01 Sep 2009 09:38:00 +0000

This procedure notifies our DBA Group of table and view changes by using a Database trigger. The trigger exposes the system function EVENTDATA() which returns XML data. See EVENTDATA in BOL for schema. This parses the XML to get information needed, and then uses the old standby sp_send_dbmail. Once the trigger is created they can be managed via SSMS by the path Database\Programmability\Database Triggers

USE MyDatabase

CREATE TRIGGER Notify_Table_changes_MyDataBase ON DATABASE

FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE, CREATE_VIEW, ALTER_VIEW,

DROP_VIEW

Can View triggers under Database\Programmability\Database Triggers

DECLARE @data XML

SET @data = EVENTDATA()

DECLARE @Recipients VARCHAR(MAX),

@Body VARCHAR(2500),

@Subject VARCHAR(50)

SET @Recipients = 'DBAGroup@MyCo.com'

SET @Subject = @@SERVERNAME +':'+RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/ServerName)[1]',

'nvarchar(100)'))) + '-'

+ RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]',

'nvarchar(100)'))) + '-' + RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')))

+ '-' + RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/ObjectName)[1]',

'nvarchar(100)')))

SET @Body = 'User:' + CONVERT(NVARCHAR(100), SYSTEM_USER) + ' Ran:'

+ @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Dataprofile',

@recipients = @Recipients, @body = @Body, @subject = @Subject

Start Up Procedures for SQL Sever (2005)

noreply@blogger.com (Dave Ott) — Sun, 16 Aug 2009 18:42:00 +0000

Start up procedures execute when the SQL Server Service is started. This can be advantageous at times. First you have to set the “Scan for Startup Procs” via sp_configure. Another, according to BOL is sp_procoption this sets the “Scan for startup Procs” and which one to execute.’ Below is the code for the proc that I want to run DBA_StartUpProc when the SQL Server service is started. After the Procedure code below there is code to run sp_procoption.

The Purpose of DBA_StartUpProc is to notify me when a SQL Server Service starts, if it’s one of our production servers I have it text me and other Operators. Why? There are several reasons: We have a cluster and I want to know when it fails over – a failover basically restart SQL Server on the other node. If a server goes bump in the night and restarts I want to know so I can check the services and jobs running on that server. To verify that something did happen that was supposed to like HW/SW/OS updates that cause a reboot or restart of SQL Server

The main flow of this proc is to determine if it’s a production server, then get then get the email addresses from the operators table in msdb, then it’s just formatting the addresses and setting parameters to call msdb.dbo.sp_send_dbmail

USE [master]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROC [dbo].[DBA_StartUpProc]

AS /*

12/11/2007 Dave Ott

Purpose of this proc is to notify when service is started

production servers will send an email and a text message,

all others will get an email

SET NOCOUNT ON

DECLARE @ProductionServerList VARCHAR(100),

@IsProductionServer BIT,

@recipients VARCHAR(4000),

@email VARCHAR(1000),

@Subject VARCHAR(100),

@Message VARCHAR(100)

DECLARE @emails TABLE ( Email VARCHAR(1000) )

SET @ProductionServerList = 'Server1,Server2,Server3'-- so far

SET @IsProductionServer = 0

-- set is production server flag

IF CHARINDEX(@@SERVERNAME, @ProductionServerList) <> 0

SET @IsProductionServer = 1

-- get list of emails and in the case of production text/Pager emails

INSERT INTO @emails

SELECT CASE WHEN email_address IS NULL THEN ''

ELSE email_address

END

+ -- if production get pager adress too

CASE WHEN @IsProductionServer = 1

AND pager_address IS NOT NULL

THEN CASE WHEN email_address IS NULL THEN pager_address

ELSE ';' + pager_address

END

ELSE ''

END

FROM msdb.dbo.sysoperators WITH ( NOLOCK )

WHERE email_address IS NOT NULL

-- get all Recipants

SET @recipients = ''

WHILE EXISTS ( SELECT TOP 1

FROM @emails )

BEGIN

SET @email = ( SELECT MIN(Email)

FROM @emails

)

SET @recipients = @recipients + -- we need a ; between emails

CASE WHEN LEN(@recipients) = 0 THEN @email

ELSE ';' + @email

END

DELETE FROM @emails

WHERE Email = @Email

END

-- send email

SET @Subject = 'SQL Server ' + @@servername + ' was started...'

SET @Message = 'Which means that it was stopped for some reason'

EXEC msdb.dbo.sp_send_dbmail @recipients = @recipients,

@profile_name = 'Profile', @body = @Message, @subject = @Subject,

@importance = 'High'

-- Accpording to books online this also sets 'scan for startup procs' in sp_configure

EXEC sp_procoption N'[dbo].[DBA_StartUpProc]', 'startup', '1'

Integrity Check file sender Script

noreply@blogger.com (Dave Ott) — Tue, 28 Apr 2009 10:36:00 +0000

These first five or so posts use DB mail and the stored procedure sp_send_dbmail. I like information sent to me, rather than me looking for it. In this case the weekly Database integrity checks are run and with over ten instances of SQL Server (all 2005). I don't want to spend my time every Monday morning (when I remember) to go look for each intergrity check output file.

Thinking about this, I should probably turn this into a SSIS package. That will be another post.

Each server needs DB mail running, it's easier if there is a common DB mail profile across all servers. This stored procedure should run on each machine and it would be one less thing to change in the code.

The code also uses xp_cmdshell. It's used to get directory listing where the intergrity output file exists. Here is the DOS command I use to get the list:
dir "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\*Integrity*.txt" /B /TC /O-D

where /B is base formating, /TC show time of creation and /O-D means order by desending date. So this will give us a list of files with the newest file at the top of the list. By dumping this into a temp table and getting the first one we have the file we want. Then we just have to mail it.
Code: /*

created by Dave Ott

This emails the Database Intergrity checks to me. PLace on all DB servers

Note: db mail must be set up and use the profile that is on that server

-- OS command to get file. Change path as needed

DECLARE @cmd NVARCHAR(500)

SET @cmd = 'dir "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\*Integrity*.txt" /B /TC /O-D'

/B - base format

/TC - Time, Creation

/O - List Files by (D)ate (-) reversed order

SET nocount ON

CREATE TABLE #list

(

ikey INT IDENTITY(1, 1)

NOT NULL,

filename VARCHAR(1000)

)

INSERT INTO #list ( filename )

EXEC xp_cmdshell @cmd

--Most servers have only one file, but keep logic to loop through multiple files

DELETE FROM #list

WHERE ikey > 2

DECLARE @file VARCHAR(1000),

@file_attachments VARCHAR(3000)

WHILE EXISTS ( SELECT * FROM #list )

BEGIN

SET @file = (SELECT MIN(filename) FROM #list)

SET @file_attachments = CASE WHEN @file_attachments IS NULL THEN ''

ELSE @file_attachments + ';'END

+ 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\'

+ @file

DELETE FROM #list

WHERE filename = @file

END

-- Email file

DECLARE @subject NVARCHAR(100)

SET @subject = 'Integrity checks - ' + @@servername

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile',

@recipients = 'DBAGroup@MyCo.com', @body = 'Integrity check',

@subject = @subject, @file_attachments = @file_attachments,

@importance = 'High'

SQLSAT13 - Job Failure Email

noreply@blogger.com (Dave Ott) — Sat, 18 Apr 2009 22:26:00 +0000

This Stored Procedure is the main one I can't live without. Every morning I get an email from our production servers and one from our test and development servers. If there are errors, then I know I have to take care of them before I can work on other stuff that day.

This procedure not only identifies the job failures and errors, it also returns the suspect pages on each server. Also, we log any failures that cause down time to our end users in Share Point. I've included a link to the Share Point page in the email message.

Requirements are that Database mail must be set up and remote servers must be a linked server.

Below the code is a sample email that I get.

USE [master]

CREATE PROC [dbo].[SP_SQLJobFailureEmail]

AS /*

DATE: 7/14/06

WHO: Dave Ott

WHY: For easy reporting of errors to the DBA Group

This loops through the failures on these serrvers and reports the errors in an email

Server Other than this one must be linked servers

Mods:

10/10/06 Dave Ott

added server3

change logic to build datetime from run_date and run_time

6/25/07 Dave ott

Added suspect_pages

SET NOCOUNT ON

-- list of errors

DECLARE @errors TABLE

(

Server VARCHAR(20),

Run_date DATETIME,

JobName VARCHAR(2000),

StepNumber TINYINT,

step_name VARCHAR(1000),

Message VARCHAR(2000),

Ukey INT IDENTITY(1, 1)

)

DECLARE @DaysToReport INT,

@Subject VARCHAR(100),

@Servers VARCHAR(300),

@Message VARCHAR(8000),

@EmailList VARCHAR(100),

@FromDate DATETIME,

@runDate INT,

@runTime INT

-- set defaults

SET @DaysToReport = 1 -- this will only report today and the yesturday

SET @EmailList = 'DBAGroup@MyCo.com' -- NT group for DBAs

SET @Subject = 'SQL Server Job Failure Email:'

SET @Servers = ''

SET @Message = ''

-- set up date and time for run

SET @FromDate = GETDATE() - @DaysToReport

SET @runDate = ( YEAR(@FromDate) * 10000 ) + ( MONTH(@FromDate) * 100 )

+ ( DAY(@FromDate) )

SET @runTime = DATEPART(hour, @FromDate) * 10000 + DATEPART(Minute, @FromDate)

* 100 + DATEPART(second, @FromDate)

-- server1 (here)

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM msdb.dbo.sysjobhistory h

JOIN msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(10), run_date),

4) + '-'

+ LEFT(RIGHT(CONVERT(VARCHAR(10), run_date), 4), 2) + '-'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2))

AND run_time < DATEPART(hh, @FromDate) * 10000

+ ( DATEPART(mi, @FromDate) * 100 ) + DATEPART(ss, @FromDate)

UNION

SELECT @@SERVERNAME,

GETDATE(),

'suspect_pages',

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM msdb.dbo.suspect_pages

-- server2

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM server2.msdb.dbo.sysjobhistory h

JOIN server2.msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= LEFT(RIGHT(CONVERT(VARCHAR(10), run_date),

4), 2) + '/'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2) + '/'

+ LEFT(CONVERT(VARCHAR(10), run_date), 4) + ' '

+ LEFT(RIGHT('0' + CONVERT(VARCHAR(10), run_time), 6), 2)

+ ':' + LEFT(RIGHT(CONVERT(VARCHAR(10), run_time), 4), 2)

+ ':' + RIGHT(CONVERT(VARCHAR(10), run_time), 2)

UNION

SELECT 'server2',

GETDATE(),

'suspect_pages',

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM server2.msdb.dbo.suspect_pages

--server2\Instance1

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM [server2\Instance1].msdb.dbo.sysjobhistory h

JOIN [server2\Instance1].msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= LEFT(RIGHT(CONVERT(VARCHAR(10), run_date),

4), 2) + '/'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2) + '/'

+ LEFT(CONVERT(VARCHAR(10), run_date), 4) + ' '

+ LEFT(RIGHT('0' + CONVERT(VARCHAR(10), run_time), 6), 2)

+ ':' + LEFT(RIGHT(CONVERT(VARCHAR(10), run_time), 4), 2)

+ ':' + RIGHT(CONVERT(VARCHAR(10), run_time), 2)

UNION

SELECT '[server2\Instance1]',

GETDATE(),

'suspect_pages',

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM [server2\Instance1].msdb.dbo.suspect_pages

--server3

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM [server3].msdb.dbo.sysjobhistory h

JOIN [server3].msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= LEFT(RIGHT(CONVERT(VARCHAR(10), run_date),

4), 2) + '/'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2) + '/'

+ LEFT(CONVERT(VARCHAR(10), run_date), 4) + ' '

+ LEFT(RIGHT('0' + CONVERT(VARCHAR(10), run_time), 6), 2)

+ ':' + LEFT(RIGHT(CONVERT(VARCHAR(10), run_time), 4), 2)

+ ':' + RIGHT(CONVERT(VARCHAR(10), run_time), 2)

UNION

SELECT '[server3]',

GETDATE(),

'suspect_pages',

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM [server3].msdb.dbo.suspect_pages

-- get list of server if any

WHILE EXISTS ( SELECT DISTINCT

server

FROM @errors

WHERE CHARINDEX(Server, @Servers, 1) = 0 )

BEGIN

SET @Servers = @Servers + ' '

+ ( SELECT TOP 1

Server

FROM @errors

WHERE CHARINDEX(Server, @Servers, 1) = 0

)

END

IF @Servers = ' ' -- if not error

BEGIN

-- send a plesent message

SET @Servers = 'No Failures'

END

ELSE

BEGIN

-- else replace space with comma

SET @Servers = REPLACE(LTRIM(RTRIM(@Servers)), ' ', ',')

END

SET @Subject = @Subject + ' ' + @Servers

SET @Message = ' Reporting from ' + CONVERT(VARCHAR(10), GETDATE()

- @DaysToReport, 110) + CHAR(13) + CHAR(13)

-- Format Email Message

IF @Servers NOT LIKE '%No Failures%'

BEGIN

-- Add link to record error in share point

SET @Message = @Message

+ 'Record Issues in SharePoint:http://SPServer/SiteDirectory/Numbers/Lists/BI%20Production%20Support%20Incidents/AllItems.aspx'

+ CHAR(13) + CHAR(13)

DECLARE @Server VARCHAR(20),

@Run_date DATETIME,

@JobName VARCHAR(2000),

@StepNumber TINYINT,

@step_name VARCHAR(1000),

@ErrMessage VARCHAR(2000),

@Ukey INT

WHILE EXISTS ( SELECT *

FROM @errors )

BEGIN

SET @Ukey = ( SELECT TOP 1

Ukey

FROM @errors

ORDER BY Server,

Run_date,

JobName

)

-- server

IF ISNULL(@Server, ' ') <> ( SELECT Server

FROM @errors

WHERE Ukey = @Ukey

)

BEGIN

SET @Server = ( SELECT Server

FROM @errors

WHERE Ukey = @Ukey

)

SET @Message = @Message + @Server + CHAR(13)

+ CHAR(10)

END

-- Run Date

IF ISNULL(@Run_date, ' ') <> ( SELECT Run_date

FROM @errors

WHERE Ukey = @Ukey

)

BEGIN

SET @Run_date = ( SELECT Run_date

FROM @errors

WHERE Ukey = @Ukey

)

SET @Message = @Message + ' '

+ CONVERT(VARCHAR(10), @Run_date, 110)

+ CHAR(13) + CHAR(10)

END

-- JOB

IF ISNULL(@JobName, ' ') <> ( SELECT JobName

FROM @errors

WHERE Ukey = @Ukey

)

BEGIN

SET @JobName = ( SELECT JobName

FROM @errors

WHERE Ukey = @Ukey

)

SET @Message = @Message + ' ' + @JobName

+ CHAR(13) + CHAR(10)

END

-- Job step

SET @StepNumber = ( SELECT MAX(StepNumber)

FROM @errors

WHERE JobName = @JobName

AND Server = @Server

)

SET @Message = @Message + ' Step:'

+ CONVERT(VARCHAR(3), @StepNumber) --+CHAR(13)+CHAR(10)

SET @ErrMessage = ( SELECT TOP 1

Message

FROM @errors

WHERE JobName = @JobName

AND Server = @Server

AND StepNumber = @StepNumber

)

SET @Message = @Message + '-' + @ErrMessage + CHAR(13)

+ CHAR(10) + CHAR(13) + CHAR(10)

-- Delete rows that we just put into the message

DELETE FROM @errors

WHERE JobName = @JobName

AND Server = @Server

DELETE FROM @errors

WHERE Ukey = @Ukey

END

SET @Message = @Message + CHAR(13) + CHAR(10)

+ 'Run From Sever1.Master.dbo.SP_SQLJobFailureEmail' + CHAR(13) + CHAR(10)

-- Mail it out!

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile',

@recipients = @EmailList, @body = @Message, @subject = @Subject,

@importance = 'High'

Here is an example email

Why did I create this blog?

noreply@blogger.com (Dave Ott) — Thu, 16 Apr 2009 16:47:00 +0000

Having used SQL Server for a long time and getting certified as a SQL Server 2005 Administrator and Developer of Business Intelligence, I have some experiences from which I hope others can benefit.

There are two main reasons why I created this blog. The first reason is to finally give something back to the online SQL community since I've done thousands of Google searches for every problem or issue that I've encountered. The second reason is that I will be presenting at SQLSaturday 13 here in Atlanta and I wanted to post the code I'll be showing in my presentation.

I hope at least one person can find this helpful.