Piecemeal restore of a partitioned table

A few weeks ago I presented my Advanced Backup and Restore session on SQL Rally in Denmark.
One of the topics that I showed was the piecemeal restore functionality. This allows you to restore your database in “pieces”. The files or filegroups that have been restored are already accessible while the others are still restoring. Jes Borland (blog | twitter) has made a good blog post on this topic.
During the session, I got a question if it was possible to restore filegroups that were used by a partioned table, which should result that the table would be partially available.
My answer to that question was that you had to restore all the files before the table becomes available, but I figured out that my answer was not correct.

With this blog post I want to correct my answer.

Setup the test scenario

For testing purposes, I created a database, MyDatabase, with 5 filegroups: PRIMARY, Partition1, Partition2, Partition3 and Partition4.

Once the database was created I added my partition function and partition scheme.
As you can see, the partition function has 3 boundaries (1, 100, 1000) and the partition scheme is created that specifies the filegroups to hold each one of the four partitions.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (Partition1, Partition2, Partition3, Partition4);

Next step was creating a partitioned tabled based on the partition scheme

--Create Partitioned table
create TABLE PartitionedTable 
	(PartitionColumnID int not null,
	Name varchar(100) not null,
CONSTRAINT [PK_PartitionColumnID] PRIMARY KEY CLUSTERED 
(
	PartitionColumnID ASC	
) ON [myRangePS1]([PartitionColumnID])	
)ON myRangePS1(PartitionColumnID);

And fill the table with 1000 records

declare @i as int
set @i=1
while @i<=1000
	begin
		insert into PartitionedTable
		select @i,'Name' + CAST(@i as varchar)
		set @i=@i+1
	end

The query below give me the number of records per partition

SELECT $PARTITION.myRangePF1(PartitionColumnID) AS Partition, 
COUNT(*) AS [COUNT] FROM PartitionedTable
GROUP BY $PARTITION.myRangePF1(PartitionColumnID)
ORDER BY Partition ;
GO

Restore the partitioned table

Before I can start with a piecemeal restore, I need to have a backup of course of my database. Don’t forget to change the <BackupPath> in the script.

--Take a new FULL BACKUP
BACKUP DATABASE [MyDatabase]
TO DISK = N'<BackupPath>MyDatabase.bak' WITH COMPRESSION,INIT
GO
BACKUP LOG [MyDatabase] TO DISK=<BackupPath>MyDatabase_Log.trn' WITH FORMAT,INIT,COMPRESSION
GO

Ok, let’s drop the database and begin with the piecemeal restore

USE [master]
GO
ALTER DATABASE [MyDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatabase]
GO

First of all, I’m going to restore the PRIMARY filegroup so that my database is already available and “ONLINE” again.

RESTORE DATABASE [MyDatabase] 
FILEGROUP = 'PRIMARY'
FROM DISK = N'<BackupPath>MyDatabase.bak'
WITH PARTIAL, NORECOVERY 
GO
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY
GO

When I check the status of my filegroups, you can see that my PRIMARY file is already ONLINE

SELECT [name], [state_desc] 
FROM [MyDatabase].sys.database_files;
GO

Secondly, I restore the Partition1 filegroup. Don’t forget to always apply the transaction log backup.

USE master;
GO
RESTORE DATABASE [MyDatabase] 
FILEGROUP = 'Partition1'
FROM DISK = N'<BackupPath>MyDatabase.bak'
WITH NORECOVERY
GO
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY
GO

When I checked the status of my filegroups again, the Partition1 filegroup is now ONLINE

I know that in this filegroup, there is 1 record stored (see “the number of records per partition”), so let’s see if I can query the table

use [MyDatabase]
GO
select PartitionColumnID,Name from PartitionedTable
GO

Well, I get an error that the second partition cannot be accessed, but if you look at the Results, the query returned 1 record!

I restored the Partition2 filegroup

USE master;
GO
RESTORE DATABASE [MyDatabase] 
FILEGROUP = 'Partition2'
FROM DISK = N'<BackupPath>MyDatabase.bak'
WITH NORECOVERY
GO
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY
GO

And executed the same query. I got again an error that Partiton3 filegroup was not accessible but the query returned 100 rows. This is correct because I know that there were 100 records in my first 2 partitions of the table.

Of course, if you’re using the correct where-clause and only query the ONLINE filegroups, you will not get any errors at all.

--Try to select the tables
use [MyDatabase]
GO
select PartitionColumnID,Name from PartitionedTable where PartitionColumnID<=100
GO

Finally, I also restored Partition3 and 4 and my table was completely available again.

Conclusion

My answer during my session was not correct. It is possible to restore a partitioned table in pieces and make part of the table available while the other filegroups are still in recovering state.
This can be really helpful to improve your RTO. Instead of restoring the complete table in 1 time, you can restore only the partitions that you really need to get your application back up and running.
The less important partitions, that for example only contain history data, can be restored in a later stage.

Well, as always, you’re never too old to learn something!

Remove Backup files from Azure Storage Part 2

In April this year I’ve posted a script to delete your backup files from Windows Azure Storage.
This week, I’ve got a question from Mark, how you could use this script in a SQL Agent Job on an Azure VM. I mentioned in my previous blog post that you should download the Azure PowerShell cmdlets before you could use the script. But Mark always got the following error when he was trying to download the installation file.


In this blog post I will explain more into detail how you can configure the script correctly in a SQL Server Agent job and how you should install the Azure Powershell Cmdlets.

Startup your VM on Azure and connect to the instance with the SSMS.
Before we install anything extra on the VM, let’s configure a job with the Removal Script that you can find in my previous post. Don’t forget to change the storage account, the access key and the container in the script into your configuration.
Your job step should look like this. Choose the Type=Powershell and not T-SQL.


When you execute the job manually, you will probably get the following error message The term ‘New-AzureStorageContext’ is not recognized as the name of a cmdlet…


This is because we didn’t install the Azure PowerShell cmdlets yet. You can download the cmdlets here. Now, when you try to download this on the Azure VM, you will get the following error:


This is because your browser settings don’t allow you to download files. To fix this, open your Internet Explorer settings and select Internet Options


Select the security tab and click the button Custom level

Scroll down to Downloads and select the option Enable under “File download”


Click OK, close the settings and restart your browser. Now you should be able to download the file to install the Azure PowerShell Cmdlets


Save it and Run the file

First of all you will get this nice welcome screen…

Wait until it is finished and you see the next screen.
Just click Install

You will see that it will install the Microsoft Azure Powershell. Click “I accept” in the next screen

…and wait till the installation is finished

Click finish at the end

In the final screen you should actually see that the Azure Powershell Cmdlets are installed

Ok… that’s it!!!

Go back to the SQL Agent Job and execute it again. Normally the job should execute successfully!

To be 100% sure that it’s working, go to the Management portal of Windows Azure and verify the container if the files have been removed.

Have fun with it!

Enable a SQL Job Unattended

Today a customer asked me to disable a daily load on their test database. I disabled the job but my problem was that I couldn’t enable the job again because I’m only once a week at that customer…
I found this little trick that I want to share with you to activate the daily job unattended.

First of all I scripted the enabling of the “daily job”. Just open the job properties à Check Enabled and hit the Script button.

USE [msdb]
GO

EXEC msdb.dbo.sp_update_job
@job_id=N'bfd08f62-4074-4f83-bebf-c9d630037595',
        @enabled=1

Secondly, I scheduled a new job “Enable daily job” that will activate the daily job the day after it has been disabled.

The job step will be the T-SQL statement that you have scripted.
You need to schedule this job after the daily job time (but has been disabled) but before the next run. Make sure that the Schedule type is set to “One Time” as we only want to activate that job just once 😉

Select Notifications and activate the checkbox “Automatically delete job: When the job succeeds”. We don’t need this “Enable Daily job” anymore once the “Daily job” has been activated.

Easy to configure, the daily job has been enabled unattended and I automatically cleanup my solution. Works like a charm and it is one reminder less in my agenda!

 

Last known good DBCC CHECKDB not always correct

On the internet you can find a lot of methods to find out when DBCC CHECKDB has last run on your databases. The 2 most common queries are the based on DBCC DBINFO() and DBCC PAGE.
Below you can find 2 examples of this (replace “YourDatabase” with the correct database name):

USE YourDatabase
GO
CREATE TABLE #tmp
	(ParentObject varchar(1000) NULL,Object varchar(1000) NULL,Field varchar(1000) NULL,Value varchar(1000) NULL)
insert into #tmp
EXEC ('DBCC DBINFO() WITH TABLERESULTS')
select cast(value as datetime) from #tmp where field='dbi_dbcclastknowngood'
drop table #tmp

OR

DBCC TRACEON (3604); 
GO
DBCC PAGE (YourDatabase, 1, 9, 3);
GO

In my morning DBA checklist, I’m using the first query to check if there has been a database integrity check for the last 7 days. This query works well, however, last week I’ve noticed an issue. As soon as a database is in read-only or emergency mode, the DBCC CHECKDB command will succeed but the dbi_dbcclastknowngood field will (or can) not be updated. For that reason, my check indicates a failed status for my read-only databases…

Let’s take an example. First of all, I’m going to perform a DBCC CHECKDB for my AdventureWorks database

DBCC CHECKDB(AdventureWorks2012)

Once the check is finished, let’s verify the last dbcc check date with the DBCC DBINFO() query.

DBCC

Now, let’s set the database to read-only and do the test again.

DBCC read only

As you might notice, the last DBCC CHECKDB time is the same in both cases.

I found a good tip (old blog post) of Jonathan Kehayias to find the correct information. Just scan the SQL Error Log for the last 7 days. If I check the logs from my previous tests, you can see that there was a DBCC CHECKDB at 14:57:22 which was on my read-only database.

Error Log

I’ve modified the script of Jonathan. I’m using the xp_readerrorlog and I’m not checking the SQL 2000 version anymore… I’ve also changed my DBCC Check in my DBA checklist with the query below.

CREATE TABLE #ErrorLog
	(
	  Logdate datetime ,
	  ProcessInfo VARCHAR(12) ,
	  MsgText VARCHAR(3000)
	)
CREATE TABLE #NumberOfErrorLogFiles
	(
		ArchiveID tinyint,
		FileDate datetime,
		FileSize int
	)
insert into #NumberOfErrorLogFiles
EXEC master..xp_enumerrorlogs

DECLARE @SinceDate datetime  
SET @SinceDate = DATEADD(dd, -7, GETDATE())
DECLARE @ErrorFile tinyint
DECLARE @Severity tinyint
DECLARE @SeverityString nvarchar(3)
DECLARE @LastLogFile tinyint
select @LastLogFile=min(ArchiveID) from #NumberOfErrorLogFiles
where FileDate<@SinceDate
DECLARE @DBCCStatement nvarchar(1024)
set @DBCCStatement=N'DBCC CHECKDB (' + DB_NAME() + ')'

set @ErrorFile=0
while @ErrorFile<=ISNULL(@LastLogFile,0)
begin
    insert into #Errorlog
	EXEC master.dbo.xp_readerrorlog @ErrorFile, 1, @DBCCStatement, NULL, @SinceDate, NULL, N'ASC'
	set @ErrorFile=@ErrorFile+1
end

select * from #ErrorLog where Logdate>@SinceDate

drop table #ErrorLog
drop table #NumberOfErrorLogFiles

Have fun!

Pieter

Tracking login and user changes in SQL Server

Did you ever wanted to track SQL Server logins and database user changes on all your SQL Servers? At one of my customers, the IT guys are sysadmins on the SQL Servers. In fact, the IT department is managing the SQL Servers when I’m not around. One day, “somebody” removed a user and of course, nobody did it 😉

First thing that came up to me was implementing SQL Server Audit. Good solution but not all versions of SQL Server are supporting SQL Server Audit. SQL Server 2008 and R2 are supporting SQL audit only in Enterprise Edition and there is no audit at all on SQL Server 2005. So, I had to come up with a solution that worked on these versions as well. My solution is based on this blog post of Aaron Bertrand (twitter | blog) but I’ve extended it.

Server Audit

I’ve implemented SQL Server Audit on all SQL Server 2008 and R2 Enterprise servers and SQL 2012 servers. Unfortunately no SQL 2014 yet, but you should also implement SQL Audit on this version The audit is dumping all the audit information into a audit log file on the server.

USE [master]
GO

CREATE SERVER AUDIT [Audit Login Changes]
TO FILE 
(	FILEPATH = N'ENTERYOURFILEPATHHERE'
	,MAXSIZE = 1024 MB
	,MAX_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '33e055a9-1924-47c3-9798-e8875a5bcd42'
)
ALTER SERVER AUDIT [Audit Login Changes] WITH (STATE = ON)
GO

 

I defined following Audit Action Types because I wanted to know all changes made on logins, roles and users.

Audit Action Type Description
DATABASE_ROLE_MEMBER_CHANGE_GROUP Raised whenever a login is added to or removed from a database role
SERVER_ROLE_MEMBER_CHANGE_GROUP Raised whenever a login is added or removed from a fixed server role
DATABASE_PERMISSION_CHANGE_GROUP Raised for any database permission
SERVER_OBJECT_PERMISSION_CHANGE_GROUP Raised whenever a GRANT, REVOKE, or DENY is issued for a server object permission
SERVER_PERMISSION_CHANGE_GROUP Raised when a GRANT, REVOKE, or DENY is issued for permissions in the server scope, such as creating a login
DATABASE_PRINCIPAL_CHANGE_GROUP Raised when users, are created, altered, or dropped from a database
SERVER_PRINCIPAL_CHANGE_GROUP Raised when server principals are created, altered, or dropped

More information on the Audit Action Types can be found here.

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Audit Login]
FOR SERVER AUDIT [Audit Login Changes]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON)
GO

Event Notifications

As mentioned before, we cannot use SQL Audit for SQL 2008 and R2 Standard Edition or SQL 2005. As an alternative, you can setup event notifications which will capture messages via Service Broker. The scripts below are based on the scripts of Aaron but I’ve added more events to it as I wanted to trace more than just “change password”

Create the following table in the msdb database

USE [msdb];
GO

CREATE TABLE dbo.SecurityChangeLog
(
    ChangeLogID			 int IDENTITY(1,1),
	LoginName            SYSNAME,
    UserName             SYSNAME,
    DatabaseName         SYSNAME,
    SchemaName           SYSNAME,
    ObjectName           SYSNAME,
    ObjectType           VARCHAR(50),
    DDLCommand           VARCHAR(MAX),
	EventTime			 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT PK_ChangeLogID PRIMARY KEY (ChangeLogID)
);

 

Setup the queue

CREATE QUEUE SecurityChangeQueue;
GO

CREATE SERVICE SecurityChangeService ON QUEUE SecurityChangeQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

 

Setup the event notificiation. If you check the “FOR”-clause, you will notice that these are the same actions as defined in the SQL Audit Specification.

CREATE EVENT NOTIFICATION CreateLoginNotification
    ON SERVER WITH FAN_IN
    FOR CREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN,CREATE_USER,ALTER_USER,DROP_USER,ADD_SERVER_ROLE_MEMBER,DROP_SERVER_ROLE_MEMBER,ADD_ROLE_MEMBER,DROP_ROLE_MEMBER
    TO SERVICE 'SecurityChangeService', 'current database';
GO

 

Install the following stored procedure to log all the event notifications into the table we’ve just created. You might notice in the loop that I’m checking the version of SQL Server for some events. This is because the event notification content is different for SQL 2008 (R2) and SQL 2005.

USE [msdb];
GO
CREATE PROCEDURE [dbo].[LogSecurityChange]
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @version int
    DECLARE @message_body XML;
	set @version = (SELECT convert (int,REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')),2), '.', '')))

    WHILE (1 = 1)
    BEGIN
       WAITFOR 
       ( 
         RECEIVE TOP(1) @message_body = message_body
         FROM dbo.SecurityChangeQueue
       ), TIMEOUT 1000;

       IF (@@ROWCOUNT = 1)
       BEGIN
		if CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/EventType)')) in ('DROP_USER','CREATE_USER','ALTER_USER') or @version>9
		BEGIN
			INSERT dbo.SecurityChangeLog(LoginName,UserName,DatabaseName,SchemaName,ObjectName,ObjectType,DDLCommand) 
			SELECT CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/LoginName)')), 
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/UserName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/DatabaseName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/DefaultSchema)')),
			    CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/ObjectName)')),
		        CONVERT(VARCHAR(50), @message_body.query('data(/EVENT_INSTANCE/ObjectType)')),
		        CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
		END
		ELSE
		BEGIN
			INSERT dbo.SecurityChangeLog(LoginName,UserName,DatabaseName,SchemaName,ObjectName,ObjectType,DDLCommand) 
			SELECT CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/LoginName)')), 
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/UserName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/DatabaseName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/SchemaName)')),
			    CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/ObjectName)')),
		        CONVERT(VARCHAR(50), @message_body.query('data(/EVENT_INSTANCE/ObjectType)')),
		        CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/EventType)')) + ' ' + 
				CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/RoleName)')) + ' FOR ' +
				CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/LoginType)')) + ' ' +
				CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/ObjectName)'))
		END
       END
    END
END

 

Last step is modifying the queue so that it will use the stored procedure and starts tracking the login and user changes.

ALTER QUEUE SecurityChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogSecurityChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Bring it all together

All the information is now logged on each individual server. I want all the audit log information in one central database so that the customer can run reports on it. First of all, I’ve created a database on one of the SQL Servers and created a table that will store all the audit logs of all the servers.

CREATE TABLE [dbo].[SecurityChangeLog](
	[ChangeLogID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [sysname] NOT NULL,
	[LoginName] [sysname] NOT NULL,
	[DatabaseName] [sysname] NOT NULL,
	[ObjectName] [nvarchar](128) NULL,
	[ObjectType] [varchar](50) NULL,
	[DDLCommand] [nvarchar](max) NULL,
	[EventTime] [datetime2](7) NULL,
 CONSTRAINT [PK_ChangeLogID] PRIMARY KEY CLUSTERED 
(
	[ChangeLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

Next step is retrieving all the log information from each server. I was thinking of using an SSIS package for this but it looked more easily to use Powershell instead. The script below is based on the central management server that I’m using. First I’m retrieving all the SQL Servers where I have to collect the audit data. This is stored in the table msdb.dbo.sysmanagement_shared_registered_servers_internal of the central management server. Make sure that you change YOURGROUPID with the appropriate groupid of your CMS and YOURCMS with the instance name of your CMS.

Once we have the list, I execute a script GetAuditLog.sql against each instance to retrieve the audit information.

The final step will write the retrieved audit information into the database. Make sure you change the YOURDATABASENAME and YOURSQLSERVERINSTANCE in the script to your database name and SQL instance.

Schedule a SQL Agent Job to execute the powershell script on a daily basis.

 

#Set-ExecutionPolicy -ExecutionPolicy Unrestricted
Import-Module “sqlps” -DisableNameChecking
cls
$InputFile = "YOURFILEPATH + GetAuditLog.sql"

#Get the list of servers from the Central Management Server
$InstanceNameList = Invoke-Sqlcmd -Query "select server_name from msdb.dbo.sysmanagement_shared_registered_servers_internal 
                                          where server_group_id=YOURGROUPID" -ServerInstance "YOURCMS"
                                         
Clear-variable -Name results
Clear-variable -Name record

#Get Audit information
foreach($instanceName in $instanceNameList) 
{ 
    write-host "Executing query against server: " $instanceName.server_name
    $results += Invoke-Sqlcmd -InputFile $InputFile -ServerInstance $instanceName.server_name
}
#Save the Audit Log into the database
foreach($record in $results)
{
#write-host "ServerName: " $record.ServerName " " $record.DDLCommand
Invoke-Sqlcmd -Query "insert into [YOURDATABASENAME].[dbo].[SecurityChangeLog] ([ServerName],[LoginName],[DatabaseName],[ObjectName],[ObjectType],[DDLCommand],[EventTime])  
                      Values('$($record.ServerName)','$($record.LoginName)','$($record.DatabaseName)','$($record.ObjectName)','$($record.ObjectType)','$($record.DDLCommand)','$($record.EventTime)')" -ServerInstance "YOURSQLSERVERINSTANCE" 
}

 

Below you can find the SQL script that is used to retrieve the audit information in the powershell script. Dependent on the version we will read the audit file or the event notification table. I’m only selecting the logs of the last day because the powershell is scheduled to run on a daily basis.

USE MSDB
GO
declare @AuditLog  table (
LoginName sysname,
DatabaseName sysname,
ObjectName nvarchar(128),
ObjectType varchar(50),
DDLCommand nvarchar(4000),
EventTime datetime)
declare @LogFilePath varchar(256)
declare @version int
set @version = (SELECT convert (int,REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')),2), '.', '')))
IF (@version=10 and (select convert(varchar,SERVERPROPERTY('Edition'))) not like '%Standard%') OR (@version=11)
BEGIN
	select @LogFilePath=log_file_path from sys.server_file_audits
	where name='Audit Login Changes'
	IF @LogFilePath IS NOT NULL
	BEGIN
		set @LogFilePath=@LogFilePath +'*.sqlaudit'
		insert into @AuditLog
		SELECT session_server_principal_name,database_name,object_name,m.class_type_desc,statement,event_time FROM sys.fn_get_audit_file (@LogFilePath,default,default) f
		INNER JOIN sys.dm_audit_class_type_map m on m.class_type=f.class_type;
		select serverproperty('Servername') as ServerName,LoginName,DatabaseName,ObjectName,ObjectType,replace(DDLCommand,'''','''''') as DDLCommand,EventTime from @AuditLog
		where EventTime>dateadd(dd,-1,getdate())
	END
END
ELSE
BEGIN
	IF exists (select name from msdb.sys.tables where name='SecurityChangeLog')
		select serverproperty('Servername') as ServerName,LoginName,DatabaseName,ObjectName,ObjectType,replace(DDLCommand,'''','''''') as DDLCommand,EventTime from msdb.dbo.SecurityChangeLog
	    where EventTime>dateadd(dd,-1,getdate()) and DDLCommand<>''
END

Report

To make it easy for the customer to retrieve the data, I’ve created this report.

Security ChangesI know that this solution is not really secure because people can just stop the audit, disable the queue or delete the records from the tables but I think this is a good start to track the login and user modifications.

Have fun!

Test your scripts against different database states

Probably you all have created some scripts that were just working fine on your instance but they didn’t work on production. I’ve seen a lot scripts that were running just fine as long as all the databases have a status ONLINE. But as soon as a database has another state, the scripts start to fail… The possible database states are:

    • ONLINE
    • OFFLINE
    • RESTORING
    • RECOVERING
    • RECOVERY PENDING
    • SUSPECT
    • EMERGENCY

More information on the database states can be found on http://technet.microsoft.com/en-us/library/ms190442.aspx. You should handle the different database states in your scripts and test them against all different database states. Now… the challenge is to force your database in a particular state. In this blog post, I’m going to explain how you can create test databases with a different state.

ONLINE

That’s the easy part. When you create a new database, it will automatically become ONLINE

CREATE DATABASE ONLINEDB

OFFLINE

When you want to create an OFFLINE database you first have to create the database and run the ALTER DATABASE statement to bring it OFFLINE.

CREATE DATABASE OFFLINEDB
GO
ALTER DATABASE OFFLINEDB SET OFFLINE
GO

You can also bring a database offline with the GUI of the SQL Server Management Studio.

RESTORING

To create a database in RESTORING state, create a new database, take a FULL BACKUP of the database and RESTORE the database with the option NORECOVERY. Your database will not come ONLINE but it will stay in RESTORING mode.

CREATE DATABASE RESTORINGDB
GO
BACKUP DATABASE RESTORINGDB TO DISK=<YourBackupPath>RESTORINGDB.bak' WITH COPY_ONLY
GO
RESTORE DATABASE RESTORINGDB
FROM DISK='<YourBackupPath>RESTORINGDB.bak'
WITH REPLACE,NORECOVERY
GO

RECOVERING

This is a temporary state. Normally, a database is only “In Recovery” mode during startup. So, when SQL Server starts up the database or when you bring it back ONLINE from an OFFLINE state. I haven’t found a possible method to simulate a database that stays in RECOVERING mode…

RECOVERY PENDING

Follow these steps to create a database with a RECOVERY PENDING state

  • Create an empty database
  • Stop the SQL Server Service
  • Rename or remove the database transaction log file of the database
  • Restart the SQL Server Service

You will notice that the database will now have a Recovery Pending state

SUSPECT

I followed this blog post of Paul Randal (blog | twitter) to create a SUSPECT database. However, my database always turned in Recovery Pending state and not SUSPECT… In the blog post, Paul is performing and update in an uncommitted transaction. I changed it into an insert of an extra colleague (myself ;)) and stopped the instance. I opened the log file with the same XVI32 tool and searched for my name and changed that section. When I restarted the SQL Server instance, the engine was unable to perform a rollback of my transaction and the database finally got into a SUSPECT state. Paul describes in this article the differences between SUSPECT and RECOVERY PENDING.

EMERGENCY

When you want to create a database that needs to go into EMERGENCY mode, you first have to create the database and run the ALTER DATABASE statement.

CREATE DATABASE EMERGENCYDB
GO
ALTER DATABASE [EMERGENCYDB] SET EMERGENCY
GO

OTHER DATABASE OPTIONS

Apart from the above mentioned states, I would also recommend to create the following test databases.

  • Read Only –  ALTER DATABASE … SET READ_ONLY
  • Restricted User – ALTER DATABASE … SET RESTRICTED_USER
  • Single user – ALTER DATABASE … SET SINGLE_USER
  • A database with name DB_EX@T!ç-$&ù!&.,.;=+#

Well, start testing your scripts and see how many of them could be improved.

Moving transaction log files

One of my customers took my advice and finally wanted to move the transaction log files to a separate disk. Probably, the current disk space issues on that server helped here a little bit as well 😉
The best way to move database and log files to a new location is by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement.
To move a file of a database, follow these steps:

  1. Set the databases offline
  2. Move the file or files to the new location
  3. Alter the database for each file that has been moved
  4. Bring back the database online

This method works really well for a single database but if you have to repeat it twice, you can automate it.
And in my case, the customer wanted to move the transaction log files for all the databases (230) in 1 time.

The first part of the script will collect the current physical paths and file names of the transaction log files and will store it into a table variable.
Don’t forget to specify the parameter @NewPath which should contain the new path for your transaction log files.
Secondly, a loop will go over all the databases that are ONLINE and will execute the 4 actions as described above. With the “ONLINE” option, you avoid bringing OFFLINE databases back online.
If the move was successful, the script will delete the old transaction log file. I’m using xp_cmshell to perform the COPY and the DEL (I know…). If you should know any better solution, just drop me a comment!
Keep also in mind that this script only works within the same SQL Server Instance.

SET NOCOUNT ON
GO
declare @DatabaseFileLocation as table  (
[Databaseid] int,
[DBName] sysname,
[FileType] int,
[name] varchar(256),
[physical_name] varchar(1024)
)

DECLARE @sqlcmd nvarchar(1024), @Newpath nvarchar(1024),@ID int

--Specify the new transaction log file folder
set @Newpath='E:'
set @ID=4

--Collect the current physical name of the transaction log file
SET @sqlcmd = N'use [?]
select DB_ID(), DB_NAME(), type, name, physical_name from sys.database_files where type=1'

Insert into @DatabaseFileLocation
exec sp_MSForEachDB @sqlcmd

while 0=0 
begin
	select @ID=min(DATABASE_ID) from sys.databases where database_id&gt;@ID and state_desc='ONLINE'
	if @@rowcount=0 or @ID is null break
	begin
		--Set database OFFLINE
		select @sqlcmd= N'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE' from sys.databases
		where database_id=@ID
		print @sqlcmd
		exec sp_executesql @sqlcmd
		--COPY the transaction log file to the the new path
		SELECT @sqlcmd= 'COPY ' + physical_name + ' ' + REPLACE([physical_name],SUBSTRING(physical_name,0,LEN(physical_name)-CHARINDEX('&#092;',REVERSE(physical_name))+1),@Newpath) from @DatabaseFileLocation where Databaseid=@ID
		print @sqlcmd
		exec master..xp_cmdshell @sqlcmd
		--ALTER the database so that it will point to the new transaction log file location
		select @sqlcmd='ALTER DATABASE [' + DBName + '] MODIFY FILE ( NAME = ['+ name + '], FILENAME = ''' + REPLACE([physical_name],SUBSTRING(physical_name,0,LEN(physical_name)-CHARINDEX('&#092;',REVERSE(physical_name))+1),@Newpath) + ''')' from @DatabaseFileLocation where Databaseid=@ID
		print @sqlcmd
		exec sp_executesql @sqlcmd
		--Set database ONLINE
		select @sqlcmd= N'ALTER DATABASE [' + name + '] SET ONLINE' from sys.databases
		where database_id=@ID
		print @sqlcmd
		exec sp_executesql @sqlcmd
		--If all went well, remove the old transaction log files
		SELECT @sqlcmd= 'DEL ' + physical_name  from @DatabaseFileLocation where Databaseid=@ID
		print @sqlcmd
		exec master..xp_cmdshell @sqlcmd
	end
end

Snippets and Template Browser in SSMS

While I was working one my presentation for  12 hours of SQL Server 2012 I noticed a new feature of SQL 2012 called Snippets.  These code snippets are small templates that can be used as a starting point when building your queries. They can also be pretty handy for junior DBA’s that are not pretty sure about the syntax of certain statements.

Let me give an example.  I want to create a table Person with following structure:

Field Type
PersonID int
Last Name varchar(50)
First Name varchar(50)
Age smallint

Right click in your query windows and select “Insert Snippet” or just use the short keys CTRL+K, CTRL+X

image

The snippet window opens.  Just double click the type of snippet that you want to insert, in my case Table

image

image

As result you get the following code. As you can see, the snippet has some replacement points which are marked in yellow

image

Now, go through the template with “tab” and change the replacement points into the appropriate values that you want.  The replacement points remain active until you “close” the snippet. Just press “Enter” to close it.

My example,

CREATE TABLE dbo.Person
(
PersonID int NOT NULL,
Lastname Varchar(50),
Firstname varchar(50),
Age smallint
);

 

But… Didn’t we have this feature in older versions? I thought templates were actually providing the same. But in fact, there are some differences.  Let’s follow my example again, and create the same table by using the templates.

In the menu click on View – Template Browser

image

In the template browser, select Table and double click on Create Table

image

You’ll get the following result in your query window

— =========================================
— Create table template
— =========================================
USE <database,sysname,AdventureWorks>
GO

IF OBJECT_ID(”<schema_name, sysname, dbo>.<table_name, sysname, sample_table>”, ”U”) IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

You can use the Replace Template Parameters dialog box to specify values any time a parameter definition is used in code.  Or just change the code manually.

image

image

As you can see the sample code that is provide by default is slightly different.  In the template code, they added the IF clause to determine if the table already exists or not.  The template also provides more detailed information on the create table statement like identity, data types, constraints and primary keys,…

A snippet is really something you can use as a starting point when building your queries, I call it a quick insert of a SQL statement, while a template provides a more detailed pre-written SQL statement. Templates are highly useful queries to jumpstart some of the “not-so-familiar tasks”. They also come in handy when writing deployment scripts. Templates are also a place to store and organize your own parameterized queries. I personally, used templates a lot when I was working as a junior DBA.  Once you have used them a lot, you will know them by hart Knipogende emoticon

Configure Cluster MSDTC with Powershell

Today, I configured a new SQL Server Failover Cluster with command line. The goal is to have a fully automatic installation of a SQL Server Failover Cluster.  By using Powershell, I was already able to add the MSDTC resource.  My next challenge was to configure it automatically Knipogende emoticon
I figured out that the MSDTC settings were in the registry under HKEY_LOCAL_MACHINEClusterResources. The problem was that the folders below this registry key are having an ID, so I had to find a way to know which one is used for MSDTC.
Well, I managed to configure it with the following script.
Import-Module FailoverClusters;
$InstanceName=$args[0]
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”

Let’s have a detailed look
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
First, I get all the child items of the registry HKEY_LOCAL_MACHINEClusterResources.  In each of the child items, I’m searching for a name that matches “MSDTC-SQL Server (<MyinstanceName>)”
Of course, you should change the “match” parameter when you have used another name for your MSDTC. Note: The parameter $InstanceName should be the name of your SQL Server Instance
As a result, I get the correct key!
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
I have used that key to set the correct registry values, according to my customer needs.
More information can also be found on
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”
Finally, you need to stop and start the cluster resource in order to take the changes into effect.
Note: The parameter $InstanceName should be the name of your SQL Server Instance
Have fun with it!

SQL Server Management Studio Scripting Options

This week I had to partition a table for performance improvements.  I scripted the table to create a temporary table for my sliding window. When I wanted to change the script, I noticed that none of my indexes and my partition scheme was scripted… Strange… because I thought that indexes were always scripted together with the table. In the Options menu of the management studio I found the default scripting options. You can find them under Tools –> Options –> SQL Server Object Explorer

When I checked the default setting of the option “Script indexes”, it was indeed false. However, according to the books online, the default setting should be true.So I compared my SQL Management Studio scripting default values with the books online default values and I noticed a few differences, which can cause some confusions, like I had… Maybe it’s worth to mention that I used a standard installation and didn’t change any option :)

Below you can find a list of the SSMS settings and the books online default values

According to me, following settings should be changed after installation of the SSMS, otherwise you will get faulty scripts, which can have a serious impact on your database if you should reuse the scripts.

Script Data Compression Options
Script indexes
Script partition schemes
Script Triggers

HAVE FUN WITH IT!