Category Archives: SQL Server 2008

I am always up for learning something new so I jumped in on the free PowerShell training at SQL University. I saw that Aaron Nelson gave me the tools to loop through a Central Management Server and I was inspired. My main motivation came from learning that Policy-Based Management isn’t the ideal solution for evaluating a daily checklist. I started to notice that the time needed to evaluate full backups started to grow as more databases were added to the farm.

I passed the #SQLU exam and got extra credit!

With the following framework you can automate the process of collecting data from the system tables and DMV’s. In this example we are going to focus on automating the process of collecting failed full backups within the last 24 hours.

NOTE: Remember, you could apply this framework to automate the process of collecting any data from the system tables or DMV’s.

Below is the framework steps: (Steps 1 – 3 are need only for the first implementation)

1. Configure your Central Management Server (We will assume you have this completed)
2. Configure your server to run PowerShell with SQL. (Go to PowerShell week at SQLUniversity for the guide)
3. Get and import the out-DataTable PowerShell function
4. Create and save T-SQL query you want to run against your SQL farm.
5. Create table to store results
6. Create table type required with Table Value Parameters
7. Create stored procedure to insert DataTable
8. Automate with SQL Agent Job

PowerShell – Find failed backups in the last 24 hours.

In general the following block of PowerShell will allow you to collect results for any query executed against your SQL Farm with the Central Management Server. With that said, there are a few lines that will need to explain so it can be modified for the code to work in your shop.

1. On line #1, the CMS for this example is PBMDEMO. You will need to switch this to your CMS server.
2. On line #4, the T-SQL file is named “Missing Backups.sql” and its located at C:\Demos\CMS_PBM\Scripts\T-SQL. You will need to change this to T-SQL file you want to execute against your SQL farm.
3. On line #7, the server name and database will need to be changed to your daily checklist repository. In this example I use PBMDEMO and PolicyManagement.
4. On line #10, the stored procedure name will need to be the name for your stored procedure to input your collected date using Table Value Parameters.
5. On line #17, The table type will need to match your table type used to strongly type the table parameter in your stored procedure (more on this below)

We will save the PowerShell script as FailedBackups.ps1 in the C:\demos\CMS_PBM\Scripts\PowerShell folder so the SQL Agent job will just load this file when we automate the data collection solution.

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Management Server Group'\PBMDEMO\ | where {$_.Mode -ne "d"} )
{

$dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "tempdb" -InputFile "C:\Demos\CMS_PBM\Scripts\T-SQL\Missing backups.sql" | out-DataTable
$dt
# Write data table to database using TVP
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=PBMDEMO;DataBase=PolicyManagement;Integrated Security=SSPI")
$conn.Open() | out-null
"Connected"
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertFailedBackups", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
#SQLParameter
$spParam = new-Object System.Data.SqlClient.SqlParameter
$spParam.ParameterName = "@TVP"
$spParam.Value = $dt
$spParam.SqlDbType = "Structured" #SqlDbType.Structured
$spParam.TypeName = "FailedBackups"

$cmd.Parameters.Add($spParam) | out-Null
$cmd.ExecuteNonQuery() | out-Null
$conn.Close() | out-Null
}

T-SQL Query to find failed backups. You will need to save it as “Missing Backup.sql” and place it in the C:\Demos\CMS_PBM\Scripts\T-SQL folder or modify the second line of code in the PowerShell example above to have the correct name and path.

BEGIN

DECLARE @BackupDB TABLE (database_name varchar(255), last_db_backup_date datetime, database_id integer)
INSERT INTO @BackupDB (database_name, last_db_backup_date, database_id)
SELECT
 msdb.dbo.backupset.database_name,
 MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
 DB_ID(database_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.dbo.backupset.type = 'D'
GROUP BY
 msdb.dbo.backupset.database_name
ORDER BY
 msdb.dbo.backupset.database_name

SELECT SERVERPROPERTY('ServerName') AS ServerName, name AS DatabaseName, last_db_backup_date
FROM master.dbo.sysdatabases  d LEFT JOIN @BackupDB b ON (DB_ID(d.name) = b.database_id)
WHERE (last_db_backup_date < DATEADD(hh, -24, getdate())
OR b.last_db_backup_date IS NULL)
AND dbid > 4
END

Create Table

The following table will be used to save the results for running the above t-sql query against the SQL Server farm.

CREATE TABLE [dbo].[FailedBackups](
[ServerName] [varchar](250) NULL,
[DatabaseName] [varchar](max) NULL,
[DateCaptured] [datetime] NULL,
[last_db_backup_date] [datetime] NULL
) ON [PRIMARY]

GO

Create Table Type

The table type is used as a requirement of using Table Value Parameters. This allows the stored procedure to know what table you are trying to pass to the stored procedure. The last five columns are required by the out-DataTable powershell function.

CREATE TYPE [dbo].[FailedBackups] AS TABLE(
[ServerName] [varchar](250) NOT NULL,
[DatabaseName] [varchar](max) NOT NULL,
[last_db_backup_date] [datetime] NULL,
[RowError] [varchar](1000) NULL,
[RowState] [varchar](1000) NULL,
[Table] [varchar](1000) NULL,
[ItemArray] [varchar](1000) NULL,
[HasErrors] [varchar](1000) NULL
)
GO

Create Insert Stored Procedure

The following stored procedure leverages Table Value Parameters to input a DataTable and do a insert select statement to populate the table.

CREATE PROCEDURE [dbo].[usp_InsertFailedBackups] @TVP FailedBackups readonly
AS
BEGIN

INSERT INTO dbo.FailedBackups (ServerName,DatabaseName, DateCaptured, last_db_backup_date)
SELECT  ServerName,
DatabaseName,
GETDATE(),
last_db_backup_date
FROM    @TVP
END

Automate Data Collection with SQL Agent and PowerShell

If you have used the Central Management Server you will notice quickly that you cannot schedule a query to run agains your farm. An easy work around is to use PowerShell to run your t-sql via a SQL Agent Job. Below is an simple scripted example that does that.

USE [msdb]
GO

/****** Object:  Job [Daily Checklist - Failed Backups]    Script Date: 05/11/2011 23:43:52 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/11/2011 23:43:52 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Daily Checklist - Failed Backups',
 @enabled=1,
 @notify_level_eventlog=0,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @description=N'No description available.',
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [PowerShell - Find Failed Backups]    Script Date: 05/11/2011 23:43:53 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PowerShell - Find Failed Backups',
 @step_id=1,
 @cmdexec_success_code=0,
 @on_success_action=1,
 @on_success_step_id=0,
 @on_fail_action=2,
 @on_fail_step_id=0,
 @retry_attempts=0,
 @retry_interval=0,
 @os_run_priority=0, @subsystem=N'PowerShell',
 @command=N'. C:\Demos\CMS_PBM\Scripts\PowerShell\out-DataTable.ps1
. C:\Demos\CMS_PBM\Scripts\PowerShell\FailedBackups.ps1',
 @database_name=N'master',
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

May is a great month for DBA’s and developers in Pittsburgh, PA and Wheeling, WV. Here are some great events you should attend this month.

Pittsburgh, PA and Wheeling, WV

Update 5/10/2011: Included SharePoint meeting as recommended by John Scott.

SQL Rally!

This week I will be getting my connect, share and learn on in Orlando, Florida at SQL Rally(yes, you can still sign up!). I cannot wait to connect with my friends in the SQL Server community.  I also look forward to making some new ones. I will be sharing my knowledge on “Evaluating your Daily Checklist against 100+ instances of SQL Server while you get a cup of coffee.” I will also be participating in the Mock Interview Overdrive session hosted by Kevin Kline.  I also look forward to learning from the vendors and some of the best presenters in the SQL Server community.

Speaking of vendors, I look forward to catching up with Confio, Idera, SQLSentry (bonus points for being a WVPASS sponsor), Quest, RedGate and DELL.  I also look forward to meeting new vendors like entrinsik, Enterprise Software Solutions, Melissa DATA.

I am sure I will have some ad-hoc changes but I still want to share my schedule for next week.

Last night on twitter an interesting question was asked by @just_samson using the #SQLHelp hash tag. He asked, “can you change the owner for jobs &dbs across instances using Policy Based Management and Central Management Server?”  The answer to this question is no because an agent facet doesn’t exist. Users cannot create facets.  Does this mean he is dead out of the water? No, because good old T-SQL and CMS can be leveraged to get the job done.

Today we are going to focus on changing SQL Agent Job owners.

How do we find owners?

SELECT
    sv.name AS [Name],
    sv.job_id AS [JobID],
    l.name AS UserName
    FROM
    msdb.dbo.sysjobs_view AS sv
    INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
    ORDER BY
    sv.[Name] ASC

Here is a screen shot from my demo so you can follow along.

How do we change the job owner for all jobs?

You can see that the first job “Device by Zero” has the owner name set to “PBMDEMO\Administrator.” In this example we will want to change the owner to be “sa”. The following script below will do this. The only problem is you would have to run this script against every instance. That’s where the Central Management Server comes into play.

** This script is used as demo. It will only work against SQL 2005\2008\2008R2. Run it on development before you consider using it in production. If you decide to run it in production you are on your own. Run it at your own risk. **

DECLARE @JobID uniqueidentifier
DECLARE @NewOwner varchar(200)
DECLARE @OldName varchar(200)

SET @NewOwner = 'sa'
SET @OldName = 'PBMDEMO\Administrator'

SELECT
sv.name AS [Name],
sv.job_id AS [JobID],
l.name AS [OwnerName]
INTO #SQLJobs
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
WHERE l.name like @OldName
ORDER BY
sv.[Name] ASC

SELECT * FROM #SQLJobs
WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN
    SELECT TOP 1 @JobID = JobID FROM #SQLJobs
    EXEC msdb.dbo.sp_update_job @job_id= @JobID,
        @owner_login_name=@NewOwner
        DELETE FROM #SQLJobs WHERE JobID = @JobID

END

DROP TABLE #SQLJobs

Now assuming you have Central Management Server configured just right click on the group of servers you want the change to be applied to and select “New Query.” Copy and paste the code and you should be able to execute the query.  Below are some screenshots from my demo incase you are new to Central Management Server. We will apply the script against the Production group.

Finally if we execute the initial query we will see that all jobs have “sa” as the owner.

Other Related Articles:

• Changing SQL Server Job Owners with Powershell via Sean McCown (b | t)
• Being Proactive with Central Management Server
• Policy-Based Management Myths – Three Part Series (#1, #2, #3)