SQL Server related thoughts

SSIS package to Sync databases between “like” environments, for instance production and test or acceptance.

This package was created in VS2008 and may have functionality only available in 2008 but will work against SQL2005 servers

There is some reliance that the environments will be the same – drive letters, file locations. Users are not synced as part of this package as generally these will be static (hopefully you are using groups) The job uses the admin UNC paths so the package needs to be run with administrative credentials of both servers or shares will need to be created with the appropriate permissions.

1. The package first queries msdb on the source server and retrieves the paths of the last full backups.
2. It passes the database name, path and full unc path into a foreach loop container.
3. The file system task copies the backup to the path defined in the FullDestPath variable
4. An execute SQL task sets the database (if it exists) to single user
5. An execute SQL task restores the database using the full admin unc path of step 3 – This could be changed to a share to allow the process to run with less privileges
6. The loop completes this for all non system or reportserver databases (this is defined in the sql script in step 2)
7. Success email is sent on completion
8. There is also an On error event handler at the root of the package that emails any package failure

Package Overview

1. Parameters

   1. 
   2. 

2. Retrieve Database name and last full backup Path

   1. Create an Execute T-SQL Statement task  against your Source server (msdb database)

1. With a full result set to an Object data type – here I have used rowset1
2. 
3. Use the SQL statement below,

WITH    BACKUPS
   AS (
 SELECT
    a.Server
    ,a.database_name
    ,B.physical_device_name
  FROM
  (
  SELECT
     CONVERT(NVARCHAR(MAX), SERVERPROPERTY('Servername')) AS Server
     ,msdb.dbo.backupset.database_name
     ,MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM
     msdb.dbo.backupmediafamily
     INNER JOIN msdb.dbo.backupset
     ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE
     msdb..backupset.type = 'D'
    AND msdb.dbo.backupset.database_name NOT IN (
    'Master', 'msdb', 'model', 'reportserver',
    'reportservertempdb' )
   GROUP BY
     msdb.dbo.backupset.database_name
  ) AS A
    LEFT JOIN (
  SELECT
     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server
     ,msdb.dbo.backupset.database_name
     ,msdb.dbo.backupset.backup_finish_date
     ,msdb.dbo.backupmediafamily.physical_device_name
   FROM
     msdb.dbo.backupmediafamily
     INNER JOIN msdb.dbo.backupset
     ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE
     msdb..backupset.type = 'D'
  ) AS B
    ON A.[server] = B.[server]
   AND A.[database_name] = B.[database_name]
   AND A.[last_db_backup_date] = B.[backup_finish_date]
 )
SELECT
   [database_name]
   ,[physical_device_name] AS databasePath
   ,'\\' + [Server] + '\' + REPLACE([physical_device_name], ':', '$') AS FullSourcePath
 FROM
   [BACKUPS]

   

1. Set your result set
2. 

1. Use a ForEach Loop Container

   1. Set the enumerator to Foreach ADO Enumerator with your object source as your object variable .

1. 1. Map your variables

1. Add a File System Task

   1. Configure with your source and destination variable and a Copy File operation

1. Create T-SQL Statement Task against your destination server (master database)

DECLARE @databaseName VARCHAR(50)
SET @databaseName = ?
IF  EXISTS
(SELECT name FROM sys.databases WHERE name = @databaseName)
EXEC('ALTER DATABASE ' + @databaseName +  ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

1. Map your parameter like this

1. Create T-SQL Statement Task against your destination server (master database)

DECLARE @databaseName VARCHAR(50)
DECLARE @FullDestPath VARCHAR(MAX)
    SET @databasename = ?
    SET @FullDestPath = ?
EXEC(
        'RESTORE DATABASE ' + @databaseName + ' FROM DISK =
N''' +  @FullDestPath + '''WITH RECOVERY, FILE = 1, NOUNLOAD, REPLACE, STATS = 10')
GO

1. Map your parameters like this

1. Add a Send Mail  task using the following expressions

1. On the even Handlers tab

1. Create an onError handler at the root of the package

• Add a Send mail task with the following expressions

1. I also create a config file to allow the package to be used in different locations without editing the package itself
   1. databaseName – value
   2. DestPath – value ( this is either a share or UNC – it does NOT contain the server name – it uses the DestServer variable so it should be in the format “C$\sql\backups” or “mybackupshare”
   3. DestServer – value
   4. SMTPFailureTo – value
   5. SMTPFrom – value
   6. SMTPServer – value
   7. SMTPSuccessTo – value
   8. SourceServer – value

I buy a lot of technical books, I have at least 10 SQL 2008 books, I normally go for the professional versions and I actually read them. The trouble is there huge! They are a pain to carry round and just plain awkward to read. I usually end up using a stand as most technical books have walkthrough’s or demos – try holding up one of those books or even prop them up while you are trying to type. This means I generally only read in the evenings at home. When I saw the 24 hour trainer book I figured here is a good sized book I can carry around and actually read.

I have always struggled with SSIS, it looks cool and seems friendly but whenever I tried to do anything I couldn’t figure out my arse from my elbow.  I have a need to sync databases, cubes, packages etc between environments. I figured it would be a cool to try and do it in SSIS. Solving my problem and increasing my knowledge at the same time.

I originally started watching the dvd demos and following along. Then I switched to the book – don’t get me wrong it was great to see the packages being constructed – (nice to see it instead of following on from a book). But the book presented the “try it” with hints so you could attempt to build the package without just mimicking the demo. Also the DVD was not the greatest quality – I found it hard to see some of the scripts, when I didn’t have the book in front of me.

I named all my packages as the task that was being covered ionstead of chapter name as suggested because I feel like I can refer back to them easily- I did all my script tasks in C# – the book was VB only.

Overall I found this to be an excellent book, I really learnt a lot. I definitely appreciate the smaller size.

Note: Authors – how about breaking your books into series to keep the size down.

I’m going to attempt to build my Environment Sync – if it works I’ll post some details

Got questions? come to the free Breakfast session at the PASS Summit

Had some strange error accessing the history tab and some of the other tabs of some scheduled reports in SQL 2008 SP1.

With a misleading error “   ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.”

I at one time restored these databases after a server reinstall from a failed SP1 upgrade. Everything worked for the most part but these odd errors on some reports kept happening.

This error pointed me in the right direction when expanding the schedules tab in SSMS connected with Reporting services

EXECUTE permission denied on object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’.

To resolve this ensure the rsexecrole exists in Master, MSDB, ReportServer & ReportServerTempDB

then run the following script

USE master
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
GO

USE msdb
GO
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GO


Looks like an old bug came back in VSPhere4 concerning SQL 2000 – When you update the VM tools MSVCP71.dll goes missing and the SQL service will not start.

Version 4.0.0, build 164009

This can be resolved by placing the file back into the %SYSTEMROOT%\System32 folder

When using a domain account that is not a local administrator in SQL 2008 & SP1 as the SQL Agent service account I see the error:

The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID
{46063B1E-BE4A-4014-8755-5B377CD462FC}
to the user *DOMAIN ACCOUNT* SID (S-1-5-21-651981998-221456604-1849977318-14822). This security permission can be modified using the Component Services administrative tool.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

To correct this

– open com permissions
Component services, computers, my computer,
DCOM config, find the MSDTSServer100
Properties
Security
Launch and Activate permissions – edit
Add the local SQL AgentUser group and enable local launch and Local activation

I think there is a lot of potential using powershell to build a cmbd.

rhyscampbell on twitter has a great post on his site

Also Chad Miller cmille19 on twitter has done some great stuff with the  sqlpsx project

I  am still pursuing using merge but there is definately an advantage to dropping to csv and then using etl in in one go.

I figured out how to do get variables from a query. So i added a second get-sqldata function within the loop

Because i have a mixed environment i need a version check to set the sysprocess table or view -The new columns will need to be added to the merge statement in the the previous script and the table updated

if ($srv.Version.ToString() -lt "9.00.0000.00")
{$sysprocesses = "sysprocesses"}
else
{$sysprocesses = "sys.sysprocesses"}
#Write-Host $sysprocesses
function Get-SqlListsstarttime
{
Get-SqlData $Server "master"  "SELECT login_time AS Started ,DATEDIFF(DAY, login_time, CURRENT_TIMESTAMP) AS daysUptime FROM $sysprocesses where spid = 1;
" | foreach {$_.started,$_.daysUptime} |
foreach { $uptimeList.Add("$_") > $null }
}# gets time started and uptime
Get-SqlListsstarttime
$started = $uptimelist[0]
$daysuptime = $uptimelist[1]


/****** Object:  Table [dbo].[ServerInfo]    Script Date: 05/29/2009 15:46:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerInfo](
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[LastUpdated] [datetime] NULL,
[Server] [varchar](50) NULL,
[Instance] [varchar](50) NULL,
[Version] [varchar](50) NULL,
[Edition] [varchar](50) NULL,
[ServicePack] [varchar](50) NULL,
[TimeStarted] [datetime] NULL,
[BackupDirectory] [varchar](250) NULL,
[defaultfile] [varchar](250) NULL,
[defaultlog] [varchar](250) NULL,
[installdatadirectory] [varchar](250) NULL,
[osversion] [varchar](50) NULL,
[physicalmemory] [int] NULL,
[errorlogpath] [nvarchar](250) NULL,
CONSTRAINT [PK_ServerInfo] PRIMARY KEY CLUSTERED
(
[ServerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

I have been looking at gathering basic information in my environment using powershell. There is some good stuff out there. I have been using the CMS server registered server function to query multiple servers but the problem is you can only query against them and not write to your CMS without using linked servers.

So I looked at some of the projects on Codeplex  – Buck Woody’s SQLCMS got me interested in extending the CMS datawarehouse.

Sqlserverpedia has some good info on building a CMDB and extending the CMS datawarehouse too

Then I took a look at Chad Millers SQLPSX project he has written some excellent functions and has a complete Powershell “base” on codeplex. I learnt a lot from looking throught the scripts and functions.

Using the SQLPSX library and functions, I wrote a powershell script that queries each server in your Central Management Server by group or as a whole returning basic information like version, Service pack, default backupfolder, OS version, Memory and path location information and merges it into a table so it can be run repeatedly.

*use at your own risk of course

**this requires the SQLPSX library – see above

[void][reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

##Point to library files

$scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)

#. $scriptRoot\LibrarySmo.ps1

Set-Alias Test-SqlConn $scriptRoot\Test-SqlConn.ps1

##Define variables

#CMS Server

$sqlServer = “YOURSERVERNAMEHERE”

$dbName = “msdb”

$ServerList = New-Object System.Collections.ArrayList

##CMS server Group

$serverGroup = “CMS SERVER GROUP NAME OR % FOR ALL”

##CMS database

$dbTARGET = “DATABASE WITH THE TABLE _SCRIPT AT THE BOTTOM”

##CMS Table

$tblServerInfo = “TABLENAME”

#######################

function Get-SqlList

{

Get-SqlData $SQLServer $dbname  “SELECT DISTINCT

s.server_name as server

FROM    dbo.sysmanagement_shared_server_groups_internal g

INNER JOIN dbo.sysmanagement_shared_registered_servers_internal s ON g.server_group_id = s.server_group_id

where   g.NAME LIKE ‘$serverGroup’

” | foreach {$_.Server} | Test-SqlConn |

foreach { $ServerList.Add(“$_”) > $null }

#Write-Host $ServerList

}# Get-SqlList

Get-SqlList

while ($ServerList.Count -gt 0)

{

$server = $ServerList[0]

#Launch Another Thread

#Write-Host $server

## open database connection

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=$sqlServer;

Initial Catalog=$dbtarget; Integrated Security=SSPI”)

$conn.Open()

$cmd = $conn.CreateCommand()

##read servernames and update/insert the table

#get-content C:\Working\SQLCMS\Powershellscripts\servers.txt | foreach {

#$server = $_

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server

$lastUpdated = Get-Date

$servername = $srv.Name

$instance = $srv.InstanceName

$version = $srv.VersionString

$edition = $srv.Edition

$ServicePack = $srv.ProductLevel

$BackupDirectory = $srv.BackupDirectory

$defaultfile = $srv.DefaultFile

$defaultlog = $srv.DefaultLog

$installdatadirectory = $srv.InstallDataDirectory

$osversion = $srv.OSVersion

$physicalmemory = $srv.PhysicalMemory

$errorlogpath = $srv.ErrorLogPath

$cmd.CommandText = ”

MERGE $tblServerInfo AS target

USING (VALUES (‘$lastupdated’,'$server’

,’$instance’

,’$version’

,’$Edition’

,’$ServicePack’

,’$BackupDirectory

,’$defaultfile’

,’$defaultlog’

,’$installdatadirectory’

,’$osversion’

,’$physicalmemory’

,’$errorlogpath’))

AS src

([LastUpdated]

,[Server]

,[Instance]

,[Version]

,[Edition]

,[ServicePack]

,[BackupDirectory]

,[defaultfile]

,[defaultlog]

,[installdatadirectory]

,[osversion]

,[physicalmemory]

,[errorlogpath])

ON (Target.SERVER = (‘$servername’))

WHEN MATCHED THEN

UPDATE SET lastupdated = (‘$lastupdated’)

,instance = (‘$instance’)

,Version = (‘$version’)

,Edition = (‘$Edition’)

,Servicepack = (‘$ServicePack’)

,BackupDirectory = (‘$BackupDirectory’)

,defaultfile = (‘$DefaultFile’)

,defaultlog = (‘$DefaultLog’)

,installdatadirectory = (‘$InstallDataDirectory’)

,osversion = (‘$OSVersion’)

,physicalmemory = (‘$PhysicalMemory’)

,errorlogpath = (‘$ErrorLogPath’)

WHEN NOT MATCHED THEN

INSERT

([LastUpdated]

,[Server]

,[Instance]

,[Version]

,[Edition]

,[ServicePack]

,[BackupDirectory]

,[defaultfile]

,[defaultlog]

,[installdatadirectory]

,[osversion]

,[physicalmemory]

,[errorlogpath])

VALUES  (‘$lastupdated’

,’$server’

,’$instance’

,’$version’

,’$Edition’

,’$ServicePack’

,’$BackupDirectory’

,’$defaultfile’

,’$defaultlog’

,’$installdatadirectory’

,’$osversion’

,’$physicalmemory’

,’$errorlogpath’);”

$cmd.ExecuteNonQuery()

#}

$conn.Close()

#Set the Server as processed

$ServerList.Remove(“$server”)

}

I imagine there is an easier way of doing this but it was fun figuring it out. One thing i wanted to get was the start time of the server. I usually use crdate of tempdb in master but i couldn’t work this into the script. That will be my next step.

Below is the table that the script inserts into


/****** Object:  Table [dbo].[ServerInfo]    Script Date: 05/29/2009 15:46:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerInfo](
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[LastUpdated] [datetime] NULL,
[Server] [varchar](50) NULL,
[Instance] [varchar](50) NULL,
[Version] [varchar](50) NULL,
[Edition] [varchar](50) NULL,
[ServicePack] [varchar](50) NULL,
[TimeStarted] [datetime] NULL,
[BackupDirectory] [varchar](250) NULL,
[defaultfile] [varchar](250) NULL,
[defaultlog] [varchar](250) NULL,
[installdatadirectory] [varchar](250) NULL,
[osversion] [varchar](50) NULL,
[physicalmemory] [int] NULL,
[errorlogpath] [nvarchar](250) NULL,
CONSTRAINT [PK_ServerInfo] PRIMARY KEY CLUSTERED
(
[ServerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO