MattaBase | A place to store my database memories...

UPDATE: Bad File

By Matt | Published: December 17, 2010

In regards to my previous post about an erroneous log file entry: http://mattabase.com/archives/295

As I thought I couldn’t get Microsoft to sign-off on altering the system tables. So I created another workaround that gets rid of the file. It involves there own procedures of detach, attach, create file, and remove file… so it had to be supported. I ran it by them anyway just to make sure and they gave me the green light. So I did it and it was successful. Here are the steps:

1. Make sure to attempt to delete bad log file and get error message about not being initialized (does part of cleanup). ie:

ALTER DATABASE [testy] REMOVE FILE [testy_log2]
GO

2. Check with dbcc loginfo whether or not the bad log file is in use

a. If it is then your going to have to switch the database into simple recovery for a second…
b. If the log is not in use then continue…

3. Detach database
4. Reattach and make sure there are only 2 log file entries in sys.sysaltfiles now
5. Create temp datafile with…

ALTER DATABASE [testy] ADD FILE ( NAME = N’testy_temp’, FILENAME = N’C:\testy_temp2.ndf’ , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO

6. Delete both files in like so (in the same order)

ALTER DATABASE [testy] REMOVE FILE [testy_log2]
GO

ALTER DATABASE [testy] REMOVE FILE [testy_temp]
GO

7. Detach database
8. Attach database
9. Check sysaltfiles (should be gone)

If you were unlucky enough to have the bad log file in use then don’t forget to flip it back to full recovery and take a full backup to start a new log chain.

Rating: 10.0/10 (1 vote cast)

Rating: 0 (from 0 votes)

Posted in SQL Server | Tagged dbcc loginfo, drop file, One or more files listed in the statement could not be found or could not be initialized., orphaned log file, SQL Server, sysaltfiles | Leave a comment

Show Status of Large Index Rebuilds

By Matt | Published: December 17, 2010

I googled around for it a bit and wasn’t really able to find anything in the way of how to check the status of a rebuild or create index with drop_existing requests. The way I was able to get some insight was a query of sys.partitions. Something like this:

select * from sys.partitions where object_id = object_id(’schema.table’) order by index_id, partition_number

I could see what partition it was working on and then how many rows of that partition were done. In our case we ran a create index with drop_existing=on. I would think the behavior would be the same for all rebuilds. Because essentially they do the same thing… Also, if you do not partition the table you’re working on.. then there should only be 2 rows returned with one being the old index and the other being the new. The new rows column will increment as it is filled. For us it looked like this:

The partition_number being worked on was 10 and 1,660,865 rows out of 2,064,517 had completed. The next partition to be worked on was 11 and it will have to do 5,212,728 rows to complete… and so on.

Rating: 6.7/10 (3 votes cast)

Rating: 0 (from 0 votes)

Posted in SQL Server | Tagged reindex, reindex status, SQL Server | Leave a comment

Bad File

By Matt | Published: November 19, 2010

One weekend I get a call the transaction log is full for our finance database. A long running batch had been running for half the day and filled up the logs because the developer never heard of a commit. Best way to let this thing finish and not have the database crash into hours of recovery would be to create a second log file somewhere we had room. So I did this… Except I made a major boo-boo. I used the GUI and somehow created a datafile instead of a log file. At first glance not a big issue. Cause I can just delete it and give it another go. Did this and it went ok. Then a day later I went to delete the log file after no longer needing it and I get.

One or more files listed in the statement could not be found or could not be initialized.

So I do some research, find it’s a known bug and then call MS support. They tell us it will be fixed in the next major release (Denali). So, the fix is to wait for that or to copy the data into a new database. This database we created the extra log file for is going on a TB and is our major Finance system. So, that much downtime isn’t going to happen for an extra log file. An upgrade to Denali when it finally comes out will at least be a year or to after that.

This bugged me. I now had this log file out there we no longer needed on a LUN with other databases. Sure we could take the db down, then copy the file over to the designated log LUN with the other log. But even this made me mad and would be time consuming. I should be able to delete the darn thing. So I went on this quest to figure out what was going on.

First, create a test database (in my case called ‘testy’). Then, run the following…

select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] ADD FILE ( NAME = N'testy_log2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testy_log2.ldf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] REMOVE FILE [testy_log2]
GO
select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] ADD LOG FILE ( NAME = N'testy_log2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testy_log2.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] REMOVE FILE [testy_log2]
GO
select * from sys.sysaltfiles where name like 'testy%'

Notice the results…Looks good right?

(2 row(s) affected)
(3 row(s) affected)
The file ‘testy_log2′ has been removed.
(2 row(s) affected)
(3 row(s) affected)
The file ‘testy_log2′ has been removed.
(2 row(s) affected)

Now take a log backup.. which will require a full cause you haven’t taken one yet. Also, because of the way sql server handles knowing what files have or have not been part of a backup… you’ll need to run a log backup after you create the first file. Otherwise you’ll get an error stating you cannot create file with the same logical name until log backup has completed. So, run the script below to create,drop,backup,create,drop…

BACKUP DATABASE testy TO DISK = 'c:\testy.bak'
GO
BACKUP LOG testy TO DISK = 'c:\test.bak'
GO
select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] ADD FILE ( NAME = N'testy_log2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testy_log2.ldf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] REMOVE FILE [testy_log2]
GO
BACKUP LOG testy TO DISK = 'c:\test.bak'
GO
select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] ADD LOG FILE ( NAME = N'testy_log2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testy_log2.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
select * from sys.sysaltfiles where name like 'testy%'
ALTER DATABASE [testy] REMOVE FILE [testy_log2]
GO
select * from sys.sysaltfiles where name like 'testy%'

Results…

Processed 160 pages for database ‘testy’, file ‘testy’ on file 5.
Processed 2 pages for database ‘testy’, file ‘testy_log’ on file 5.
BACKUP DATABASE successfully processed 162 pages in 0.407 seconds (3.102 MB/sec).
Processed 3 pages for database ‘testy’, file ‘testy_log’ on file 11.
BACKUP LOG successfully processed 3 pages in 0.063 seconds (0.263 MB/sec).
(2 row(s) affected)
(3 row(s) affected)
The file ‘testy_log2′ has been removed.
Processed 5 pages for database ‘testy’, file ‘testy_log’ on file 12.
BACKUP LOG successfully processed 5 pages in 0.227 seconds (0.159 MB/sec).
(3 row(s) affected)
(4 row(s) affected)
Msg 5009, Level 16, State 9, Line 4
One or more files listed in the statement could not be found or could not be initialized.
(4 row(s) affected)

What’s going on? I thought that got deleted? Why are they both in sysaltfiles? I’m confused… Why aren’t things getting deleted from the system tables. If you inspect sysaltfiles closley you’ll see the original mistakingly created data file still exists. So now there is a duplicate entry in sysaltfiles for the logical name ‘testy_log2′. Yup definite bug.

How to fix it? The next steps need to be done in the DAC. It involves changing system tables. This isn’t supported unless MS tells you to do so. Which they haven’t told me to. So we haven’t done this in our financials database. I do not suggest doing this unless you contact MS first.

Steps:
1. Backup log and check to make sure nothing is active in the log you want to delete (dbcc loginfo anything with a status not 0 is active).
2. Stop instance
3. Start instance in single user mode and connect to DAC
4. From DAC connection run the following

delete from sys.sysfiles1 where name = 'testy_log2'
delete from sys.sysprufiles where lname = 'testy_log2'
delete from sys.master_files$ where name = 'testy_log2'

5. Stop instance
6. Start instance regularly

If all went well you should be able to delete the log from the file system and it should have disappeared from sysaltfiles view. How’d I figure this out. While connected to DAC I ran a couple queries… First I had to figure out what tables contained file data.

select * from sys.syscolpars order by name
select * from sys.objects where type = 'S'
sp_helptext 'sys.sysaltfiles'

With these three queries I was able to find anything file related. I figured anything that was file related would have a column called fileid. So I look in syscolpars for that and got the table object Id. Then looked for that table with the id in objects. Lastly, and most importantly. The main table that supplies data for the sysaltfiles. I needed to look at the text of the view. Which is querying the master_files$ table. If you do not delete the rows from this table… it will repopulate the other two tables with the erroneous entries again at startup.

Here is what the entries looked like for the bad testy database.

Once I deleted the rows corresponding to testy_log2 and started the instance back up. Everything was good again. I haven’t noticed any issues with this database since. I ran a couple long batches to fill up the log… backed it up a few time… etc. And it seems fine. But again. I would not do this unless MS says it’s ok. Which I plan on asking. Let you know how that goes.

NOTE: This bug has been reported in 2005, 2008 and 2008 R2. I was running 2008 SP2 for this test.

Rating: 9.5/10 (2 votes cast)

Rating: 0 (from 0 votes)

Try… Catch… Try Again

By Matt | Published: October 14, 2010

I remember hearing about the new feature of the try and catch for TSQL in 2005. I was very excited to use it, as I was aware from other languages how much it helps with the logic and readability of your code. To my dismay, it wasn’t all that I had hoped for. I hoped it would help with trapping errors and returning errors messages that were useful. In all cases you can trap an error. It’s the returning of a valuable error message that still is missing. Because certain pieces of TSQL are out of our coding control (ie. BACKUP DATABASE, ALTER INDEX, etc.). It’s not up to us how the error is passed. A prime example of this is with the backup command. Any time an error occurs with a backup command two critical errors are passed back. The catch is only able to catch one error message. And the SQL engineers thought it was wise to catch the last error. An example of this would like the following:

Msg 3201, Level 16, State 1, Line 3
Cannot open backup device ‘C:\SQLBU\newscripts\FULL_AdventureWorks_20101014121506.BAK’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

But if caught in a try-catch, the error_message() is the very unhelpful ‘BACKUP DATABASE is terminating abnormally’. This is very troublesome for someone who wants to create the normal backup everything on the instance script. Sure backups for the other databases would continue… but your stuck trying to figure out what might have went wrong. And when you finally get to looking on the server, the condition that made it fail may no longer exist and your left scratching your head.

I see many people complaining about this very example. Because it’s pretty common place to want to backup an entire instance with TSQL and then report any errors via email, output file, or whatever. To get around this I’ve added a couple things to help me troubleshoot backup failures in my backup scripts (which I also broke apart to be more modular now… see attached zip for all my code). I now start a server side trace and save it to a file on the server everytime a backup is running. I filter on the spid of the currently executing process and a couple other things to keep the size down. And also delete the previous .trc files if they exist. Now, if I recieve notification a backup failed. I can go out to wherever I am storing the .trc files and open them up in profiler. Inside will be the better more descriptive first error. Here’s the import snippets of code:

start trace if debug set
	select @TraceFile=@TraceDirectory + N'backup' + @Type + [dba].dbo.fn_DateLabel()
	BEGIN TRY
		IF @debug = 1
		BEGIN
			exec DBA.dbo.usp_DeleteTraceFiles @TraceDirectory = @TraceDirectory, @Type = @Type, @DaysToKeep = @DaysToKeep
			exec DBA.dbo.usp_StartBackupTrace @FileName=@TraceFile,@SPID=@@SPID, @TraceID=@TraceIDOut OUTPUT
		END
	END TRY
	BEGIN CATCH
		print 'Could not start trace... see output above for return code.'
	END CATCH

So call a proc to start the trace… and then later on in the script when backups are done:

--STOP AND DELETE TRACE DEFINITION
if @TraceIDOut > 0
BEGIN
	exec DBA.dbo.usp_StopBackupTrace @TraceIDOut
END

Call another proc to stop it… In the case above, where the location is not valid. You’ll see something like this in the trace file now when looking on the server.

If you want my code to use as a reference or just use it as is… backupscripts.zip

NOTE: We store all our maintenance scripts in a database called DBA on the server. So if you want to use it as is you’ll need to take all references to DBA out of the scripts. Unless you follow suit and create a database called DBA and put these scripts in it. In the zip file are 12 scripts that will create 11 new objects and 1 table type. They are:

ErrorTableType: a table type so we can pass errors to our error notification handler, usp_ProcessErrors
fn_CommaDelimStringToTable: a function to pass in a comma delimited string and recieve a table back
fn_DateLabel: a function to recieve a current time stamp back as a string
fn_DefaultBackupDirectory: a function to recieve the location to put backups… keys off of @@servername
fn_DefaultTraceDirectory: a function to recieve the location to put trace files… keys off of @@servername
usp_Backup: stored proc that is used to initiate instance backup calls
usp_BackupDiff: stored proc called by usp_Backup to run a differential backup for a database
usp_BackupFull: stored proc called by usp_Backup to run a full backup for a database
usp_BackupLog: stored proc called by usp_Backup to run a log backup for a database
usp_CmdShellSwitch: stored proc called to enable or disable xp_cmdshell switch (needed to delete files)
usp_DeleteBackupFiles: stored proc called to delete backup files in default folder of a particular type
usp_DeleteTraceFiles: stored proc called to delete trace files in default folder of a particular type
usp_StartBackupTrace: stored proc to start server side trace with minimal events used to keep size down
usp_StopBackupTrace: stored proc to stop a server side trace and delete it’s definition from the server

Last note is these scripts are designed for SQL Server 2008.

Rating: 9.0/10 (1 vote cast)

Rating: 0 (from 0 votes)

Posted in Motley, SQL Server | Tagged backup errors, backup get best error, SQL Server, SQL Server backup scripts, try catch, tsql try catch, wrapping backups in try catch | 1 Comment

Peoplesoft and Resource Governor

By Matt | Published: October 7, 2010

During implementation of our new PS finance environment I was given the task to explore the use of SQL 2008’s Resource Governor capabilities. We wanted, if possible, to categorize such transactions as; batch, psquery, administrative, and other. This was to ensure someone running a psquery, batch, or administrative transaction couldn’t take over the whole server.

Well I’m new to Peoplesoft and thought I’d give it a go. So first thing I had to do was understand how PS logs into SQL Server. That wasn’t so hard. In short, PS uses connection pooling and comes in as a user with access to it’s database. This user has dbo rights. They’re are a finite amount of connections (worker processes) made at startup for normal processing. Then depending on how the PS Admins set things up, a connection can be made on the fly or it can grab an existing connection. From here the first thing it does is query some PS tables to see if the PS login has rights to what it needs. But again, it does all of this with just a single sql login account. What it does do, is update the context_info field that resides in sys.dm_exec_requests. This field can be helpful when things are bogged down and the dba wants to know who is executing what within sql. The DBA could run something like:

select session_id, cast(context_info as varchar(max)) OPRID, qt.text, DB_NAME(database_id) as dbname ,wait_type,wait_time,wait_resource, blocking_session_id from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle) qt ; GO

It shows some info specific to the to the PS user running the query and then the actual query it’s running.

That’s good to know, but resource governor doesn’t work that way. It doesn’t look at the context of a running transaction. It looks at things at the time the connection is made. I wasn’t going to give up quite yet though. I knew it would be easy to classify most batch processing as that would come from the PS Process Scheduling server. Not the application server that does the normal day-to-day processing. The tough one becomes ps query. This as far as I understand it… at startup on the application server a configured amount of processes start for handling psquery. You can see the process(es) under task manager. And even in the startup logs. So my thought was, if I could somehow get the pid of the psquery process. I’d know at connection by using the hostprocess column in sys.sysprocesses if it were requested from psquery or not. Currently the only method I can think of doing this, and it wouldn’t be fun… is to somehow get the pid from the logs at startup and update a table somewhere on the sql instance. Then in my classifier function I could query that table to see if the pid matches hostprocess. That just sounded like too many loops and too much work to get ps queries into a designated bucket. So for now I scrapped it. Lastly, all administrative tasks that are scheduled will run a specific user. So in our case you can just classify on that user. There are many ways you can go for that one though, all of which have discussed at nausium.

The one that got away was the psquery stuff… so if anybody has an idea. I’m open to suggestions.

Rating: 8.0/10 (1 vote cast)

Rating: +1 (from 1 vote)

Posted in Peoplesoft, SQL Server | Tagged context_info, EnableDBMonitoring, Peoplesoft, Resource Governor, SQL Server | Leave a comment

Fantasy Football Draft Access Database 2010

By Matt | Published: September 3, 2010

Finally got to updating the access database that we used last year to this years players… If you’re new to this, here is the previous blog entry with the 2009 version, http://mattabase.com/archives/50. I didn’t really make any changes to this years version except I added this years players and top 300. Here you go…

FantasyFootball2010.zip

Rating: 7.0/10 (3 votes cast)

Rating: +3 (from 3 votes)

Posted in Access, Motley | Tagged fantasy, fantasy draft application, Fantasy Football, Fantasy Football Draft Access Database, fantasy football tool | 2 Comments

Powershell SQL Server Backup History Script

By Matt | Published: August 31, 2010

The script below can be used/scheduled to email a report to someone with the database backup history for a list of SQL instances. So in a text file you’ll need to place the instance name line by line and then change the first line of the script to point to that file. Then simply change the emailFrom, emailTo, and smtpServer to correct values for your environment and your good to go. As long as you sent it to yourself, you should get one html table back with servername, databasename, last_db_backup, and backup age (hours) for all of the instances you specified in the text file.

# Get list of SQL servers
$sqlservers = Get-Content "C:\sqlservers.txt";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
# Get the datetime to use for the log filename
$datetime = Get-Date -Format "yyyy-MM-ddThh-mm-ssZ";
$emailFrom = "whoever@wherever.com";
$emailTo =  "whoever@wherever.com";
$smtpServer = "smptserver.wherever.com"; 

Write-Host "Pulled in " $sqlservers.length " sql servers";

#sql statement for backup history
$sql = "SELECT ";
$sql = $sql + "CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS server, ";
$sql = $sql + "msdb.dbo.backupset.database_name as dbname, ";
$sql = $sql + "MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, ";
$sql = $sql + "DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [backup age (hrs)] ";
$sql = $sql + "FROM    msdb.dbo.backupset ";
$sql = $sql + "WHERE     msdb.dbo.backupset.type in  ('D', 'I') ";
$sql = $sql + "and msdb.dbo.backupset.database_name in (select name from master.dbo.sysdatabases) ";
$sql = $sql + "GROUP BY msdb.dbo.backupset.database_name ";
$sql = $sql + "HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE())) ";
$sql = $sql + "UNION ";
$sql = $sql + "SELECT ";
$sql = $sql + "CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS server, ";
$sql = $sql + "master.dbo.sysdatabases.name AS dbname, ";
$sql = $sql + "NULL AS [last_db_backup_date], ";
$sql = $sql + "-1 AS [backup age (hrs)]  ";
$sql = $sql + "FROM master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset ";
$sql = $sql + "ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name  ";
$sql = $sql + "WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' ";
$sql = $sql + "ORDER BY msdb.dbo.backupset.database_name";

Write-Host "Will execute... $sql";

$dtmaster = New-Object "System.Data.DataTable";
$commaservers = "";
$i = 0;

# For each SQL server listed in $sqlservers
foreach($sqlserver in $sqlservers)
{
    $i = $i + 1;
    Write-Host "Processing sql server: $sqlserver.";

    if ($i -lt $sqlservers.length)
    {
        $commaservers = $commaservers + $sqlserver + ", "
    }
    else
    {
        $commaservers = $commaservers + " and " + $sqlserver
    }

	# Create an instance of SMO.Server for the current sql server
	$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
    $db = New-Object "Microsoft.SqlServer.Management.SMO.Database";
    $db = $srv.Databases["master"];
    $ds = New-Object "System.Data.DataSet";
    $ds = $db.ExecuteWithResults($sql);

    Write-Host "Has rows: " $ds.Tables[0].Rows.Count;

    if ($ds.Tables[0].Rows.Count -ne 0)
    {
        $dtmaster.merge($ds.Tables[0]);
        Write-Host "DTmaster rows: " $dtmaster.Rows.Count;
    }
    Remove-Variable ds;
}

$dtmaster.Rows | format-table -AutoSize;

[string]$body = $dtmaster.Rows | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -Head $style -Body "DB BACKUP REPORTThis report contains a list of databases who have exceeded 24hrs since their last good diff or full backup... It reports on servers: $commaservers" ; 

$subject = "DB Backup Report";
$message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $body)
$message.IsBodyHTML = $true
$smtp = new-object Net.Mail.SmtpClient($smtpServer);
$smtp.Send($message);
Remove-Variable subject;
Remove-Variable message;
Remove-Variable smtpServer;
Remove-Variable emailTo;
Remove-Variable emailFrom;
Remove-Variable body;

Remove-Variable sqlservers;
Remove-Variable sql;
Remove-Variable srv;
Remove-Variable db;
Remove-Variable dtmaster;

Rating: 9.0/10 (1 vote cast)

Rating: 0 (from 0 votes)

Posted in SQL Server | Tagged backup history, backupset, email, msdb, powershell, report, script, SQL Server | Leave a comment

Transparent Data Encryption Backup Script

By Matt | Published: August 19, 2010

We’re beginning to use transparent data encryption (TDE) here… and we need to make sure we have good backups of the relevant certificates and keys. Because I like to script and automate everything, I’ve created a script to backup the databases certificate and it’s private key. These two files need to be restored to any other server in which you want to move the database to. The stored procedure is created with the “with encryption” option. This is to hide the password used to protect the backup files. Someone still could run a trace to capture the password being used by the stored procedure. But they would already have to have sysadmin rights to be able to run the trace. So if that were the case your data is gone anyway. If your that paranoid you could include sp_password in the script below to hide the contects from a trace as well. Here is the script:


CREATE PROCEDURE usp_backuptde WITH ENCRYPTION
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @backup_folder nvarchar(100),
		@cmd nvarchar(255),
		@cert_name 	nvarchar(100),
		@db_name 	nvarchar(100),
		@last_backup datetime,
		@passwd nvarchar(100),
		@cmdrunval as bit,
		@exists as int,
		@file as nvarchar(500)

	SELECT @backup_folder = 'C:\TDE\'
	SELECT @passwd = 'somepassword'
	SELECT @cmdrunval= 0

	CREATE TABLE #cmdshell
		(name varchar(20), min bit, max bit, config bit, run bit)

	--enable show advanced options
	exec sp_configure 'show advanced options', 1
	reconfigure with override

	--if cmdshell disabled then enable it to take care of deleting old cert and keys
	INSERT INTO #cmdshell EXEC sp_configure 'xp_cmdshell'
	SELECT @cmdrunval=run from #cmdshell

	IF @cmdrunval=0 BEGIN
		print 'Flipping xp_cmdshell...'
		exec sp_configure 'xp_cmdshell', 1
		reconfigure with override
	END

	DECLARE tde_cursor INSENSITIVE CURSOR FOR select DB_NAME(k.database_id) as dbname,
									c.name as certname, c.pvt_key_last_backup_date as lastbackup
									from sys.dm_database_encryption_keys k inner join sys.certificates c
									on k.encryptor_thumbprint = c.thumbprint

	OPEN tde_cursor
	print ''

	FETCH NEXT FROM tde_cursor INTO @db_name,@cert_name,@last_backup
	WHILE @@FETCH_STATUS = 0 BEGIN

		--if files exist already then delete them
		SELECT @file=@backup_folder + @db_name + '_' + @cert_name + '.cer'
		EXEC xp_fileexist @file,@exists OUTPUT

		IF @exists = 1 BEGIN
			print 'Certificate backup exists by that name, deleting... del ' + @file
			SELECT @cmd='del ' + @file
			exec xp_cmdshell @cmd
		END

		SELECT @file=@backup_folder + @db_name + '_' + @cert_name + '.pvk'
		EXEC xp_fileexist @file,@exists OUTPUT
		IF @exists = 1 BEGIN
			print 'Private key backup exists by that name, deleting... del ' + @file
			SELECT @cmd='del ' + @file
			exec xp_cmdshell @cmd
		END

		--create backup command
		SELECT @cmd = 'BACKUP CERTIFICATE ' + @cert_name + ' TO FILE = '''
			+ @backup_folder + @db_name + '_' + @cert_name + '.cer'
			+ ''' WITH PRIVATE KEY (file=''' + @backup_folder + @db_name + '_' + @cert_name + '.pvk'',encryption by password=''' + @passwd + ''')'

	    EXEC(@cmd)

	    SELECT @cmd = 'BACKUP CERTIFICATE ' + @cert_name + ' TO FILE = '''
			+ @backup_folder + @db_name + '_' + @cert_name + '.cer'
			+ ''' WITH PRIVATE KEY (file=''' + @backup_folder + @db_name + '_' + @cert_name + '.pvk'',encryption by password=''
'')'

		print @cmd
	    print 'Done...'
	    print ''

	    FETCH NEXT FROM tde_cursor into  @db_name,@cert_name,@last_backup
	END	--WHILE

	DROP TABLE #cmdshell
	DEALLOCATE tde_cursor

	--flip xp_cmdshell back if it was zero at the beginning of call
	IF @cmdrunval=0 BEGIN
		print 'Flipping xp_cmdshell back...'
		exec sp_configure 'xp_cmdshell', 0
		reconfigure with override
	END

	--ie. exec usp_backuptde
END
GO

Rating: 7.0/10 (1 vote cast)

Rating: 0 (from 0 votes)

Posted in SQL Server | Tagged backup certificate, tde, tde backup script, transparent data encyption | Leave a comment

SQL Litespeed vs. Native SQL Backups

By Matt | Published: July 23, 2010

I haven’t posted in awhile. It’s been crazy. I took a new job in a new city, have a baby on the way, selling my house, … well that’s enough right there. Anywho, a recent discussion we’re having at my new place of employment; Do we still need Litespeed? Is it worth the added cost and complexity to our environment? Basically, I came in and said why are we using Litespeed? I mean SQL 2008 has compression… why still pay the money right? So I did a little comparison and here I my findings.

	Litespeed	Native SQL
Compression	X
Options	X
Management	X
Complexity		X
Speed	X
Security	X
Cost		X

First note, compression is only an option if your running SQL 2008 Enterprise. You cannot use native backup compression with SQL 2008 Standard Edition. But, if/when you upgrade to 2008 R2 Standard you are allowed to use compression. We have a mix of both Standard and Enterprise instances here. And currently have no 2008 R2 instances. So as we sit, if we choose native sql, we won’t be able to implement a standard for backup compression until we upgrade everything to R2.

Compression… Running Litespeed with the option of level 3 I found equates to about what native SQL compresses files to. You can up the anty, and run Litespeed at level 10 if you want. But your CPU will definetly be hit hard on the SQL box during backups. In my testing I backed up a 50GB database using SQL and Litespeed. Here is that comparison.

	Speed	Size	CPU
Litespeed Level 3	2 Min.	4.5 GB	10%-15%
Native SQL	6 Min.	5 GB	10%-15%
Litespeed Level 5	2 Min.	3 GB	30%-40%

You can see Litespeed is both faster and compresses the data better.

Options… I found Litespeed has a few more options than SQL does pertaining to backups. For instance, striping, throttling, object level indexing for object level recovery (no object level recovery for native sql), compression levels, and encryption levels (no native sql encryption).

Management… Litespeed wins here too. It’s sole purpose in life is backups and restores. So it better win. I found the interface and reporting that you get right out of the box to be very good. Through the console you can register your instances and drill down into your backups and actually read the data on those backups. You can see when your last good backup took place, your total disk savings, any failures, set up log shipping, do restores, etc. Besides the reports you see in the console, you also have the option to use the Litespeed backend database to run reports on. Either reverse engineer it yourself, or use Reporting Services .rdl files already made for you by the Quest community.

Complexity… This is the area that was my initial concern with Litespeed. Any time you introduce a third party product into your backup strategy. Some complexities arise. Like, in a disaster type scenario how difficult would it be to begin your restore process? Litespeed has done everything they can possibly do to try and appease your concerns in this arena. Like “double-click restores”… this option allows you to create backups that are an executable. You then can do a restore without having to install Litespeed on the server. It also provides a tool to convert a Litespeed backup file into a native sql backup file. These options make you feel better about a DR type scenario. But like it or not, it does add a level of complexity to your environment that is not there if you use native backup means.

Speed… Litespeed lives up to its name. Reference the table above… it’s faster.

Security… about the only options you have to secure your data with native tools is adding a password to the backup file or encrypting the entire database. If option 1 doesn’t sound too secure you’re right… option 2 would provide security. But it also adds the overhead of encrypting your entire database. Litespeed offers many levels of encryption and definitively wins this battle.

Cost… Litespeed isn’t cheap. Neither is SQL Server… but running just SQL Server and using it’s native tools for backups is definitely cheaper than running SQL and Litespeed.

So overall, if your running either 2008 Enterprise or 2008 R2… and if you have some time to write some t-sql to help you manage things… and if encyption is not important to you. Then native SQL server backups are probably your best option. You can tell Microsoft is heading in the direction of making a tool like Litespeed obsolete. But for now, if you fall into the opposite category and you have the money. Litespeed is where it’s at.

Rating: 0.0/10 (0 votes cast)

Rating: +1 (from 1 vote)

Posted in SQL Server | Tagged Backup compression, Litespeed, Litespeed vs. SQL Server, Quest, SQL Server | Leave a comment

SAP Row-Level Compression

By Matt | Published: May 12, 2010

Going through upgrading the procedures to do row-level compression with our SQL Server 2008 SAP databases. We’ve got 9 landscapes to do (4 BI and 5 ERP). So far I’ve done 5 of these landscapes. One of these is a sandbox that is a complete copy of production ERP. This database went from 966GB to 772GB doing row-level data only compression of the ABAP schema. It took 17 hours to complete. Of which 16.5 of those hours the database was completely online. We have a java stack as well on these systems. I decided not to do these environments. At a high level, I’ve been running the stored procedure here (http://blogs.msdn.com/saponsqlserver/archive/2009/10/11/version-2-3-of-sap-use-db-compression-released.aspx) and only notify our developers at the point I run the offline portion in batch mode. Once we go to do this in production I’ll be doing the online portion during a weekend and the offline portion following notification from our Basis staff to the business on what tables will be offline. My steps have remained the same throughout except for our last instance. This instance Basis did not know the “master” password and I could not login as the schema owner (sid sql user). For everything else, I logged in as the sql user and ran the procedure like it was documented for a typical run. Here are the steps I took to do the instance that we did not know the password for the sid user:

Log into server with client tools
Run management studio connecting as someone with sysadmin rights
Create a new temporary sql account that has dbo rights to the SAP database
Make sure this accounts default schema is set to the sid (NOTE: do not create this account and give it sysadmin rights as this will cause the users default schema to always be dbo… the script used schema_id() to key off of and you’ll not get any of the SAP tables to compress)
Connect to instance with new user and in new query window run: exec sp_use_db_compression ROW, @maxdop=1, @online= ‘ON’, @verbose=1
You can then monitor your execution of compression by looking at verbose output or run the query, select * from sp_use_db_compression_table
Once this completes, check for any tables that will need to be compressed in offline mode by running, select * from sp_use_db_compression_batch_input
To execute this offline process; Run, exec sp_use_db_compression ROW, @maxdop=1, @verbose=1, @batch_input=1 (NOTE: the tables you saw in sp_use_db_compression_batch_input will be offline during it’s compression step)
Monitor this step again with, select * from sp_use_db_compression_table.
When that is finished, remove temporary user created in step 3.
DONE!

Special thanks goes out to the SAP Basis team at Microsoft for creating such a very well documented and written script.

Rating: 8.0/10 (1 vote cast)

Rating: 0 (from 0 votes)

Posted in SAP, SQL Server | Tagged row-level compression, SAP row level compression, sp_use_db_compression, users schema dbo | Leave a comment

Search
Pages
- About
Categories
- Access
- Motley
- Oracle
- Peoplesoft
- Reporting Services
- SAP
- SQL Server
Archives
GoStats Map

RSS Links
- All posts
- All comments
Meta
- Register
- Log in

A place to store my database memories…

DB BACKUP REPORT

Search

Pages

Categories

Archives

GoStats Map

RSS Links

Meta