Presenting at SQL Saturday #40 – South Florida

July 28th, 2010 § 0

Haven’t updated this blog in ages, and wanted to put in a quick post about the session that I will be delivering at SQL Saturday #40 – South Florida. The name of the session is “Multi-Server Management with SQL 2008+”, meaning 2008 and 2008 R2. I will be uploading slides and scripts here soon after the event.

SQL University: Backups and Restores – Recovery Models

October 7th, 2009 § 2

Backups? What are you talking about?

One of the most basic administrative tasks in SQL Server is maintaining proper backups. A backup is nothing more than a dump of all the data, metadata and objects on a database (like stored procedures, functions, etc.) Not only do you need to make sure that backups of your DBs are taken regularly — but you also need to make sure that those backups are kept on a resilient location (such as RAID 5 arrays) plus, you need to test your backups by restoring them regularly (how frequently really depends on how comfortable you are with your infrastructure) to ensure you can recover in the event of a failure.

How do backups work?

To understand how backups work, and how to create and maintain a proper Recovery Strategy, you need to understand what Recovery Models are. What recovery model you choose for your DB with will greatly impact the amount of data loss in the event of a failure. And as you already know — hardware is unreliable. Failures are bound to happen. As a DBA, you need to make sure that you can satisfy your business requirements for acceptable data loss. Your business rules might dictate that it is OK to lose a whole week’s worth of data in the event of a failure, so a weekly backup would be okay. This is rarely the case. Most businesses perform nightly backups and are okay with losing a whole day’s worth of data. There is talk of some financial institutions backing up their databases every 10 seconds or less!

Now, obviously, backing up an entire 100Gb database every 10 seconds is unrealistic (at least as of time of writing). But that’s where Recovery Models come into play. As you know, SQL databases are comprised of data files and a transaction log. The Recovery Model you choose for your database dictates how much information is kept on the transaction log. In SQL Server, every single transaction performed against the database is written to the transaction log first, and from there to the data file.

On the next post on this series, we will discuss the different types of backups and how those types of backups relate to the recovery model you have chosen for your database.

Ok, What do I choose from?

There are three different recovery models. Simple, Bulk-Logged, and Full. Let’s discuss each one.

The Simple Recovery Model

The Simple recovery model is what it implies. The information on the transaction log is kept to a minimum. If you choose this recovery model, you can only restore your database as it was when you issued your last backup. The types of backup supported by this model are full and differential (more on these in the next post). Space used by transactions in the transaction log is reclaimed automatically as they are written to the data file. Point-in-time recovery (where you can restore the database as it was at a specific time) is not available using this model.

The Full Recovery Model

In the Full recovery model, transactions in the transaction log are kept until you perform what is called a Transaction Log Backup, or tlog backup. In this model, point-in-time recovery is available, so you can recover your database to a specific moment, provided that a tlog backup was taken after that point. In this recovery model, your transaction log will grow indefinitely until you issue tlog backups. Transaction log backups are tipically much smaller than full backups (as they only backup information in the tlog)

The Bulk-Logged Recovery Model

The Bulk-Logged recovery model maintains a higher level of recoverability than the simple recovery model, as you still need to perform tlog backups with it. However, information for bulk-logged operations like BULK INSERT, CREATE INDEX, and SELECT INTO are not kept in the transaction log, but rather in the data files. This means that if a bulk-logged operation happened since your last backup, you will be forced to redo those operations manually. Also, point-in-time recovery is not available with this model. You might want to choose this model if your Bulk operations are done at different times than the normal processing.

Okay, where do I find more information on the Recovery Models?

SQLServerPedia.com has a great wiki on Recovery Models. It is available here.

MSDN has a copy of the entry in Books Online about Recovery Models – check it out here.

SQL Server 2000 Recovery Models Give You Backup-and-Restore Flexibility — rather old article from SQL Server Magazine available at MSDN. Still relevant.

 

What next??

Stay tuned for the next post on Backups, where we will discuss backup types and how to create a proper Backup and Recovery Strategy.

SQL University – Students, Head Over to Orientation Please!

September 24th, 2009 § 0

My good Twitterfriend Jorge Segarra, a.k.a. SQLChicken (Blog | Twitter) came up with a great idea that will hopefully be an awesome learning resource for those wanting to learn SQL Server from scratch, or refresh knowledge on certain topics within the SQL Server realm. It’s the SQL University. Orientation is located here. I am happy to be a part of this effort as a member of the faculty, and look forward to generating content for the syllabus. I hope all SQL newbies enjoy this wonderful idea!.

Server-level Trigger to Notify on SQL Login Changes (Including Password Changes)

September 23rd, 2009 § 2

Ideally, you want to go with Windows Authentication for all your users in SQL Server whenever possible. In the real world, most applications rely on SQL logins. So it happens rather frequently that when the password for a SQL login that is critical to an application is changed, that application breaks. Now, granted, if you are doing things right you have tight controls in place around who can modify passwords, but in reality, this stuff happens all the time.

In this post, I will present you with a script that will create a server-level DDL trigger that will send an email whenever one of a set of deemed critical SQL accounts is modified.

Note that DDL triggers were made available initially in SQL 2005, so this script won’t work on SQL 2000.

IF EXISTS (SELECT *
			FROM sys.server_triggers
			WHERE name = 'trg_notifyPasswordChange')
			DROP TRIGGER trg_notifyPasswordChange ON ALL SERVER;
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER trg_notifyPasswordChange
ON ALL SERVER
FOR ALTER_LOGIN
AS
	DECLARE @loginName AS sysname
	DECLARE @serverName AS sysname
	DECLARE @timeOfEvent AS datetime
	DECLARE @loginAffected AS sysname
	DECLARE @bodyOfEmail AS nvarchar(2000)
 
	SELECT @loginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)')
			, @serverName = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname')
			, @timeOfEvent = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
			, @loginAffected = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
 
	IF @loginAffected IN ('sa', 'sqlaccount1', 'sqlaccount2') -- List of logins goes here -- modify at your discretion
	BEGIN
		SELECT @bodyOfEmail = @loginName + ', ' + @serverName + ', ' + CONVERT(VARCHAR(20), @timeOfEvent, 100) + ', ' + @loginAffected
 
		-- You will have to modify profile_name and the recipients on the next statement
 
		EXEC msdb.dbo.sp_send_dbmail @profile_name = '<your_dbmail_profile_here>'
										, @recipients = '<your_recipient_list_here>'
										, @subject = 'Critical SQL Login Password Changed!'
										, @body = @bodyOfEmail
	END
GO

Sorry about the formatting — I am still fighting with the formatting WP plug-in. As always, comments are more than welcome. Hope this helps!

UPDATE As Tim Benninghoff commented below, this trigger will fire whenever ANY changes are made to ANY of the critical SQL logins. Please keep in mind that you will likely get false notifications.

SQL Service Accounts and SPNs (Service Named Principals)

September 9th, 2009 § 6

Ah, the nuisances of AD and Kerberos. Whenever you change the service account on a SQL instance, you need to make sure that an SPN pointing to the SQL instance is in place for the Service Account. Otherwise, you might get the dreaded “cannot generate sspi context” error. To manage SPNs registered on your service accounts, you will need to use the setspn.exe utility, which is available here.

To see which SPNs are registered with your SQL Service account:

setspn.exe -l <current_service_account>

Unregister the SPN for the old SQL Service account:

setspn.exe -D MSSQLSvc/full.name.to.your.host.here:1433 <old_service_account>

Finally, register the SPN for the new SQL Service account:

setspn.exe -A MSSQLSvc/full.name.to.your.host.here:1433 <new_service_account>

If you use named instances, the syntax for the command will vary slightly. See this post by Jeff Gilbert which has information on syntax to use and other details.

You could also get SSPI errors if there is no registered SPN for the SQL Service on the host itself. See Jeff Gilbert’s post for details on that.

Find out which tables have a Primary Key

September 4th, 2009 § 0

If you want to do transactional replication, all the tables in the publication need to have a primary key. Here’s a quick way to determine which tables have a primary key.

This is the query for SQL 2005 and 2008:

SELECT so.name AS TableName, OBJECTPROPERTY(so.object_id, 'TableHasPrimaryKey') AS HasPrimaryKey
FROM sys.objects so
WHERE type = 'U'

And here is the query for SQL 2000:

SELECT so.name AS TableName, OBJECTPROPERTY(so.id, 'TableHasPrimaryKey') AS HasPrimaryKey
FROM sysobjects so
WHERE type = 'U'

How to programatically change SQL Server startup parameters (options)

June 9th, 2009 § 0

Ok. First post. Let’s see how this goes.

So they’re called ‘Startup Options’ or ‘Startup Parameters’ interchangeably. I have no idea why. In this post, I intend to show you how to change these programatically (with a script). The example is restricted to one of the many Startup Options that SQL Server accepts.

Here’s the scenario. You need to change the size of the MemToLeave area on a SQL instance . You read somewhere that you need to add/modify the ‘-g’ startup option. Fine, you go ahead and start SQL Server Configuration Manager, you right-click on the SQL service for the instance which you want to modify, right-click on it, select properties, click on the advanced tab, and look for ‘Startup Parameters’. Then, you edit the field, go to the end of the line and add ‘-g<some_value>’ – with <some_value> being any positive integer. I’ve usually seen DBAs increase it in chunks of 128Mb, but that’s just a rule of thumb. You restart your instance and voila — you’re done.

Well, that’s great — but what if you needed to change the size of the MTL on many servers?

Here is where Powershell, SMO and WMI come to the rescue.

Disclaimer: I handle many instances, and some of those have less than 4Gb of RAM. It was decided that only on servers reporting total RAM = 3.5Gb (considering that some servers suck up some RAM for graphics, etc) we would bump up the size of the MTL to 512Mb. The script reflects this criteria. Modify and use at your own risk.

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Wmi")
 
$serverList = Get-Content 'path-to-your-serverlist.txt'
 
foreach ($serverName in $serverList) {
	# TotalPhysicalMemory is in Kilobytes, so we need to do the math -- there are other ways to do it, I know.
	$ram = (Get-WmiObject -Class Win32_ComputerSystem -ComputerName $serverName).TotalPhysicalMemory / 1024 / 1024
 
	if ($ram -ge (3.5 * 1024)) {
		$wmiserver = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $serverName
		$startupParameters = $wmiserver.Services['MSSQLSERVER'].StartupParameters # YMMV on the service name if you use named instances
		$mtlSetting = $startupParameters.split(';') | ? { $_.StartsWith('-g') }
 
		if ($mtlSetting -eq $null) {
			$wmiserver.Services['MSSQLSERVER'].StartupParameters += ';-g512'
		}
		else {
			$wmiserver.Services['MSSQLSERVER'].StartupParameters = $startupParameters.Replace($mtlSetting, '-g512')
		}
 
		$wmiserver.Services['MSSQLSERVER'].Alter()
		# Now reboot the box!
		$wmiOs = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $serverName
		$wmiOs.Reboot()
	}
}