Sunday, July 31, 2011

Changing a Read-Only File Group to Read-Write with a Partial Backup Sequence

If you have a database using the simple recovery model with both read-write and read-only file groups, you may want to use differential partial backups.  This backup strategy makes sense (and saves backup disk space / backup time) Since you do not need to repeatedly backup data that is read-only.  This backup scenario is called a partial backup sequence.

For example, you may have:
FILEGROUP        TYPE
Primary          Read-Write
FG2              Read-only
FG3              Read-only
FG4              Read-write


After doing an initial full backup, you may want to backup the file groups enabled for write by performing a weekly (depending on your business needs for data recovery) partial full backup like this:
BACKUP DATABASE <database name> READ_WRITE_FILEGROUPS TO <backup device>

and a daily (also depending on your business needs for data recovery) differential partial backup like this:
BACKUP DATABASE <database name> READ_WRITE_FILEGROUPS TO <backup device> WITH DIFFERENTIAL

What if you change a filegroup from read-only to read-write?  What do you need to do to your backup jobs to account for the change?

A partial backup sequence must begin with a base partial backup before differential partial backups can be performed. Therefore, to include a filegroup that has been changed from read-only to read-write in subsequent partial backups, you need to create a new partial base backup. You create a partial base backup by specifying the READ_WRITE_FILEGROUPS in a FULL backup.  After that point, you have a base to do a recovery and include differentials as needed.

Tail-Log Backups

Tail-Log Backups are needed when you wish to perform a database recovery on a database with full or bulk-logged recovery model and you are recovering to a point after the last full, differential, or transaction log backup.  If you are doing a recovery to point a in time prior to the last full, differential, or transaction log backup then a tail log backup is NOT required.

A Tail-Log Backup is also the first step in performing a manual failover of a primary log shipping server to a secondary server. 

A Tail-Log backup is simply a log backup taken using the BACKUP LOG statement.  It is called a Tail-Log backup because it is backing up the remainder or tail of the transaction log. What differentiates a Tail-Log backup from any other log backup?  When taking a Tail-Log backup, you need to ensure that transactions are not occurring after you do the backup.

If the database you are recovering is not damaged, online and available for connections, you should use the backup statement with NORECOVERY option.  Using the NORECOVERY option puts the database into the restoring state where no changes are allowed:
BACKUP LOG <database_name> TO <backup_device>
    WITH NORECOVERY 

If the database is damaged, you should use the CONTINUE_AFTER_ERROR option: 
BACKUP LOG <database_name> TO <backup_device>
    WITH CONTINUE_AFTER_ERROR
This option allows you to successfully produce a transaction log backup even if the database file is damaged.  Per the MSDN article, the log backup will succeed if:  "the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes".

Thursday, July 28, 2011

Preview of Windows Server "8" and Denali Release Date Hint

Interesting video of a presentation made to Microsoft business partners during the 2011 Microsoft Worldwide Partner Conference (July 10-14, 2011).

At 37:55 is a Windows Server "8" first time sneak preview. 

At 41:38 is a presentation on SQL Server "Denali" with heavy emphasis on BI. 

Most interesting is that the presenter says SQL Server "Denali" will be available the "first half of next year".  Based on this info, my guess is that SQL Server "Denali" will be RTM'd March of 2012.

If you're interested in SQL Server "Denali", it is well worth the time.

Thursday, July 21, 2011

MS_DataCollectorInternalUser User with No Login

Found what I thought was an orphaned User MS_DataCollectorInternalUser in the MSDB databases on our SQL2008 and SQL2008R2 servers.  My assumption is that it has to do with the Data Collector.  What I did not realize is that this user account is not an orphaned user at all.  It is a user account intentially created with no SQL Login.

As of SQL Server 2005, a user can be created without login - see MSDN reference.  The example given in this link for the use of this kind of user with no login is to grant access to the user and then grant impersonation of that user to an SQL Login.  From the same link above:

USE AdventureWorks2008R2 ;
CREATE USER CustomApp WITHOUT LOGIN ;
GRANT IMPERSONATE ON USER::CustomApp TO [adventure-works\tengiz0] ;
GO

To use the CustomApp access rights:

EXECUTE AS USER = 'CustomApp' ;
GO

I have not found a way to differentiate a user which is truly orphaned (once upon a time had a login) and a user wihout a login. I've left messages on Microsoft forums; however, no one seems to know how to differentiate between orphaned users and users created with no login.

Thursday, July 14, 2011

Utility Control Point in SQL 2008 R2

Monitoring Instances of SQL Server in the SQL Server Utility is something new in SQL Server 2008 R2.  The Utility Control Point (UCP), as it is called, looks to be incomplete as implemented in this release of SQL Server.  The purpose of UCP is to monitor the resource health of multiple SQL Servers.  The MSDN reference is here.  I have not spent much time with UCP, but it does look like it will fit nicely with the resource data collection in Centralized Management Server MDW and the new Microsoft Service Center offering:  SCA

Friday, July 8, 2011

Startup Stored Procedure

We were audited by our internal IT auditors who asked some good questions.  One question I could not immediately answer was:  "Why do some of your SQL instances have the 'scan for startup procs' configuration option set"?  Since we have no stored procedures configured (using sp_procoption) to run at startup (that I'm aware of), I did not know how to answer the question. 

I executed the following to determine what stored procedures (if any) are executing at startup:
select name from sys.sysobjects
where xtype = 'p'and
      OBJECTPROPERTY(id, 'ExecIsStartup') = 1;

The result was:  sp_MSrepl_startup

Replication sets sp_MSrepl_startup as a stored procedure to execute at SQL instance startup. 

Full explanation of the OBJECTPROPERTY function is here.

Mystery solved.

Thursday, July 7, 2011

Windows 2008 R2 Cluster Restart Issue

We had two of our Windows 2008 R2 clusters, each hosting two SQL 2008 R2 Enterprise Edition instances, failover one of the SQL resource groups in each of the two clusters at about the same time (within seconds).  The event log contained an Event ID 1135 on one cluster and Event ID 4201 on the other cluster as the pertinent system events just prior to failover.  How do you analyze these and is there a common issue?

To analyze Event ID 1135, see the Technet article here.  Essentially, I ran the "Validate this Cluster..." configuration function and found a NIC intended for backups only was used by the cluster as a network resource.  In our environment, we specifically configure the backup NICs to not be able to allow access from peer to peer node (just to backup devices), so this is most likely the cause.  I also found on one of the nodes that the backup NIC driver needed to be re-installed.  I'm guessing NIC drivers were updated and network resources on the cluster was a side-effect.

To analyze Event ID 4201, the Technet article was of no use since it states the message is a NIC start message and the message accompanying the event stated the NIC could not be started.  I'll have to report that one.  Further searching resulted in finding similar messages wherein the NIC was causing problems.  Looking at all NICs on the servers in the cluster, I saw no problems.  The message contained a GUID which referenced the NIC.  Searching the registry I found the problem NIC was the Microsoft Failover Cluster Virtual Adapter.  

I am still investigating this one.

In the meantime, I've disabled the use of all backup NICs from the clusters and validated that all clustered resources do function and properly failover to/from each node.

<Update>The network crew came clean and stated they restarted a network applicance (firewall) which caused the SQL Instances on both clusters to restart.  Mystery solved.

Max Server Memory in SQL 2008

Yagnesh Shah (blog) does a nice job of giving an easy to follow formula for determining the max server memory advanced options setting for SQL 2008 in his post here.  I have always added a little more memory than what was required of the OS and other apps running on the server.  Yagnesh's formula allows for additional memory space for worker threads - something I had not considered.

Tuesday, July 5, 2011

Windows 2008 Cluster Log Location

Looking for the Windows Cluster Log in Windows 2008 or Windows 2008 R2?  It's no longer a simple text file in the windows cluster directory as it was with Windows 2003.  There is a much more suphisticated system of tracing/logs in the new Event Tracing for Windows (ETW) which is now used in Windows 2008. 

If you want to extract a log file similar to the old cluster.log, perform the following:
1.   Go to a command prompt
2.   Type "Cluster /Cluster:yourclustername log /gen /copy "C:\temp".
3.   Navigate to the c:\temp directory and there you will find the .log files for each node of your cluster.

The cluster log can now be opened in Notepad.

Full details of the new ETW feature of Windows used by the failover clustering feature is provided on MSDN and Technet.

Monday, July 4, 2011

SQL Server DBA Interview Questions and Answers

Pinal Dave (blog) does a great job of discussing SQL Server DBA interview questions and answers here.  I like his overall approach to why both experienced SQL Server DBAs and newbies need to review these questions.  His answers are accurate and complete.  Worth the read.

Sunday, July 3, 2011

Moving TEMPDB files

TEMPDB files cannot be moved when SQL Server is online.  The way to move the files is to execute an ALTER DATABASE command which modifies the TEMPDB FILENAME (and location) in the catalog.  The next time the SQL Server is started, the files are created in the new location.   See the MSDN reference here.

The following script creates an ALTER DATABASE command which then I submit to move the TEMPDB files:  

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = ' +
name +
', FILENAME = ''M:\MSSQLDATA\' +
name +
'.ndf'');'
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO 

The resultant query becomes the script to move the files:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'M:\MSSQLDATA\tempdev.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'M:\MSSQLDATA\templog.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = TempD2, FILENAME = 'M:\MSSQLDATA\TempD2.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = TempD3, FILENAME = 'M:\MSSQLDATA\TempD3.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = TempD4, FILENAME = 'M:\MSSQLDATA\TempD4.ndf');

Be sue to modify the FILENAME part of the statement to match your environment drive/folders and modify the resultant code to change the .ndf to .mdf/.ldf file extension as appropriate for that data file (proper file extension name is not required for SQL Server, but helps DBAs quickly identify file types).

The old files are still there and should be deleted to conserve space as they are no longer used.

Saturday, July 2, 2011

SQL 2008 R2 Replication Default Article Properties

SQL 2008 R2 Replication, as a default behaviour, does not replicate non-clustered indexes.