Tom's blog

I have updated the Service Pack and Cumulative Update list for SQL 2008 R2 and SQL 2008:

SQL 2008 R2

SQL 2008

Tom

Recently Microsoft has released Service Pack 3 for SQL 2008. I have updated the overview for both SQL 2008 R2 and SQL 2008

I changed the overview a bit, so you can better see the levels of cumulative updates and how they correspond to each other

SQL 2008 R2

SQL 2008

Tom

I recently had a strange error when installing a slipstreamed installation of SQL 2008 SP2:

The summary didn’t give a clear explanation:

Overall summary:
  Final result:                  SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
  Exit code (Decimal):           -2068643839
  Exit facility code:            1203
  Exit error code:               1
  Exit message:                  SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
  Start time:                    2011-08-17 11:08:48
  End time:                      2011-08-17 12:22:59
  Requested action:              Install

So I took a look in the detailed setup log, more or less the same:

2011-08-17 12:23:01 Slp: Error result: -2068643839
2011-08-17 12:23:01 Slp: Result facility code: 1203
2011-08-17 12:23:01 Slp: Result error code: 1

Apparantly the SQL services have tried to start, and an ERRORLOG was generated:

2011-08-18 11:34:14.28 Server      Error: 17182, Severity: 16, State: 1.
2011-08-18 11:34:14.28 Server      TDSSNIClient initialization failed with error 0×80092004, status code 0×80. Reason: Unable to initialize SSL support. Cannot find object or property.

2011-08-18 11:34:14.28 Server      Error: 17182, Severity: 16, State: 1.
2011-08-18 11:34:14.28 Server      TDSSNIClient initialization failed with error 0×80092004, status code 0×1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2011-08-18 11:34:14.28 Server      Error: 17826, Severity: 18, State: 3.
2011-08-18 11:34:14.28 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2011-08-18 11:34:14.28 Server      Error: 17120, Severity: 16, State: 1.
2011-08-18 11:34:14.28 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

After searching for this on the www, I found some references to a corrupt user profile for the account running SQL Services. I took a look at the profiles and it seemed that for this user, only a temporary profile was created. After looking at the account properties in AD it seemed that there was something weird with it, no user logon name was specified and the pre-windows 2000 logon name had a space at the end.

We deleted the account, re-created it, re-installed SQL and no problems anymore!

Recently our operational team had to do a restore of a database for BizTalk. These databases require a special restore routine, to let the BizTalk components in sync with the DB.

First they restored the latest full backup, which went without problems.

Then they needed to restore the transaction log backups, with the STOPATMARK option set:

RESTORE LOG [Database] FROM

DISK = N’LocationOfBackupFile’

WITH FILE = 1, NOUNLOAD, STATS = 10,

STOPATMARK = N’BTS_2011_06_06_00_00_29_087′ AFTER N’2011-06-06T02:00:32′

GO

which results in an error:

100 percent processed.

Processed 0 pages for database ‘Database’, file ‘Database’ on file 1.

Processed 2 pages for database ‘Database’, file ‘Database_log’ on file 1.

Location:     diskio.c:1769

Expression:   filepos + cBytes <= (DWORDLONG) m_size << PAGESIZEBITS

SPID:         65

Process ID:   1908

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Msg 3624, Level 17, State 1, Line 1

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

When restoring the log without the STOPATMARK option, no problems are encountered.

The log mark entries where all filled in correctly. You can see this by querying the dbo.logmarkhistory table in msdb.

We opened a case at Microsoft and they confirmed that this is a bug and is fixed in CU4 for SQL 2008 SP2 (http://support.microsoft.com/kb/2527180). I’ve asked for more information, but they cannot provide it for the moment as it is confidential. I still hope to find some more information though, I’ll update this post with the details.

Tom

Microsoft recently released two new cumulative updates: CU14 for SQL 2008 SP1 and CU4 for SQL 2008 SP2.

This makes following build table:

Current Cumulative Update Packs for SQL 2008:

Current Cumulative Update Packs for SQL 2008 R2:

Current Cumulative Update Packs for SQL 2008:

In our environment we have several SQL clusters, most of them in an active-active configuration.

Microsoft recommends that the sum of all maximum server memory for all instances is equal to the total installed memory – memory reserved for the system. This means that during normal circumstances, more than half of the memory is not used. While I can understand that Microsoft recommends this out of safety, I think it’s stupid to not let SQL profit from extra memory that is ready to be used.

I found some references to Memory Rebalance scripts on the net, but found them to be useless for me, as they either used a default node, were limited to 2 instances or required the nodes to have the same amount of memory installed.

Therefore, I have created a solution that uses ‘weights’ to calculate the maximum server memory for a given instance.

How it works:

• I select one instance to be the ‘master’ instance. In the master database of this instance, I create a new table which contains the InstanceNames, NodeName on which this instance is currently active and the weight.
  Script:
  

  USE [master] GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[MemorySettings]‘) AND type in (N’U')) BEGIN CREATE TABLE [dbo].[MemorySettings](     [InstanceName] [varchar](256) NOT NULL,     [ActiveNodeName] [varchar](256) NOT NULL,     [Weight] [int] NOT NULL ) ON [PRIMARY] END GO

• I fill in the InstanceNames, current Active Nodenames and weights in this table, for example:
   
• Now create on each instance a linked server to the other instances in the cluster.
• Next I create a stored procedure in each master database. This stored procedure will be set to execute on SQL service start. It will update the MemorySettings database on the master instance and call the sp_MemorySettings procedure on each instance. 
  

  create proc dbo.sp_DetectInstanceAndNodeSettings as /*     Purpose:    This procedure takes the instancename and current running node and places it in                 the “master” config table.                 This procedure will be set as autoexecute on startup of each instance.                 The latest step will be a cursor to call sp_SetMemorySettings on each instance     PreReq:        Table “MemorySettings” on a “master” instance     Version:    0.1 Initial Version     Author:        Tom Van Zele (tvz@live.be) */ /* Step 1: Update the “master” table with the ActiveNodeName */ declare @InstanceName as varchar(256) declare @ActiveNodeName as varchar(256) set @InstanceName = (select @@SERVERNAME) set @ActiveNodeName = (select CAST(SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as varchar(256))) UPDATE [YourMasterInstance].master.dbo.MemorySettings /* Replace [YourMasterInstance] with the name of your “master” instance */ SET ActiveNodeName = @ActiveNodeName WHERE InstanceName = @InstanceName /* Step 2: call all registered instances to execute sp_SetMemorySettings in order to set their MaxMemory */ declare @SQLToExecute varchar(1000) declare @InstanceNameToCheck varchar(256) declare AllInstancesCursor CURSOR for select distinct InstanceName from [YourMasterInstance].master.dbo.MemorySettings /* Replace [YourMasterInstance] with the name of your “master” instance */ open AllInstancesCursor fetch next from AllInstancesCursor into @InstanceNameToCheck while @@FETCH_STATUS=0 begin     set @SQLToExecute = ‘EXEC [' + @InstanceNameToCheck + '].master.dbo.sp_MemorySettings’     print (@SQLToExecute)     exec (@SQLToExecute)     fetch next from AllInstancesCursor into @InstanceNameToCheck end close AllInstancesCursor deallocate AllInstancesCursor GO   exec sp_procoption @ProcName=[‘sp_DetectInstanceAndNodeSettings’], @OptionName=‘STARTUP’, @OptionValue = [on] GO

• Then finally we create the sp_MemorySetting stored procedure in the master database of each instance in the cluster. This proc will check the current ActiveNode, detect what other instances are active on the same node and will calculate and set the Maximum Server Memory, based on the weights.
   

create proc dbo.sp_MemorySettings as /*     Purpose:    This procedure will adjust memory settings based on the “master” instance input     PreReq:        Table “MemorySettings” on a “master” instance     Version:    0.1 Initial Version     Author:        Tom Van Zele (tvz@live.be) */ /* Step 1: Calculate our max memory setting */ declare @InstanceName as varchar(256) declare @ActiveNodeName as varchar(256) declare @Weight as dec(10,2) declare @SumOfWeightOnActiveNode as dec(10,2) declare @Factor as dec(10,2) declare @MemoryInNode as int declare @MaxMemory as int declare @ReservedForSystem as int  set @ReservedForSystem = 2048 — Reserved memory for system, change as you like set @InstanceName = (select @@SERVERNAME) set @ActiveNodeName = (select CAST(SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as varchar(256))) select @Weight =  [Weight] from [YourMasterInstance].master.dbo.MemorySettings where InstanceName = @InstanceName /* Replace [YourMasterInstance] with the name of your “master” instance */ – print @Weight select @SumOfWeightOnActiveNode = SUM(Weight) from [YourMasterInstance].master.dbo.MemorySettings where ActiveNodeName=@ActiveNodeName /* Replace [YourMasterInstance] with the name of your “master” instance */ – print @SumOfWeightOnActiveNode select @Factor = @Weight/@SumOfWeightOnActiveNode – print (@Factor) select @MemoryInNode = physical_memory_in_bytes/1048576 from sys.dm_os_sys_info – print @MemoryInNode select @MaxMemory = (@MemoryInNode-@ReservedForSystem)*@Factor – print @MaxMemory /* Step 2: Set our calculated MaxMemory value */ exec sp_configure ‘show advanced option’,’1′; RECONFIGURE; exec sp_configure ‘max server memory (MB)’,@MaxMemory RECONFIGURE; exec sp_configure ‘show advanced option’,’0′; RECONFIGURE; GO

Known issue:

If you have a SQL 2008, a linked server is not automatically configured to accept RPC (You get an error that Server [servername] is not configured for RPC). To change this, please execute the following:

exec sp_serveroption @server=[YourInstanceName], @optname=’rpc’, @optvalue=’true’
exec sp_serveroption @server=[YourInstanceName], @optname=’rpc out’, @optvalue=’true’

So far the script works fine for me. If you have any remarks/better ideas, feel free to add a comment!

Tom

I recently received the feedback results of the presentation I gave for the Belgian SQL Users Group.

3 questions were asked:

• Fulfilled this evening your technical expectations?
• How do you evaluate the speaker about his presentation skills?
• How do you evaluate the speaker about his knowledge of the subject?

The results:

What I can conclude is that I should work on my presentation skills (which I already knew). I have the tendency to speak very fast when I’m nervous.

Something I don’t understand very well is that I got 2 scores of 3 for ‘Fulfilled this evening your technical expectations?’, where most of the scores were 7 or 8. It seems that I have really disappointed these 2 persons. If you are one of them, please feel free to contact me, so I can learn what I did wrong, or what I can do to improve this!

Tom

Last week I gave a presentation to the Belgian SQL Users Group about database forensics.

You can download the presentation here.

Thanks to Kevvie Fowler, I may also put the contents (demo databases, forensic scripts) available for download. The included scripts are also published on the Application Forensics website, check there for updates.

If you want to learn more about the subject, take a look at the SQL Server Forensic Analysis book!

Tom

In this guide, I will instruct step-by-step on how to create an HADR configuration between Denali SQL servers. For more information about this new high availability type see my post: CTP1 for SQL ‘Denali’ available for download

First a little overview of my test environment, I’m running Windows 2008 R2 Hyper-V on a laptop. The host is also a domain controller and has the Microsoft iSCSI Software Target running to provide shared disk storage via iSCSI to my guest machines. The iSCSI Software Target is part of Storage Server, but can be installed separately.

I have 4 Hyper-V guests running, 2x 2 nodes within a Windows Failover Cluster (requirement for Denali HADR) with denali clustered.

So let’s start with our setup:

1. Enable the HADR service on both clustered denali instances 

• Open SQL Server Configuration Manager
• Select SQL Server Services
• Right-click on your SQL Server, in my case SQL Server (INST1) and select Properties
• Select the SQL HADR tab and click the checkbox Enable SQL HADR Service
• Click Ok on the warning dialog box
  
• Restart your SQL Services (Attention: SQL HADR requires a Windows Failover Cluster, so do not stop the service through the Services Control Panel, but go to the Failover Cluster Manager, select your SQL Server Instance, and take it offline. When everything is offline, bring it online again)

2. Create an Availability Group

• Open SQL Server Management Studio
• Select Management
• Right-click Availability Groups and select New Availability Group
• Click Next
  
• Give your new Availability Group a name and click Next
• Select which user databases you want to add to your Availability Group. If your database is not listed, you can select Show user databases not meeting requirements. This will give you the reason why a certain db cannot be added to the AG. Then click Next 
• In the Specify Replicas screen, you can add the instances you want to be enable as HADR in the secondary role, after you have done this, click Next
  
  
• Next you have the overview screen, click Finish to start configuring the HADR setup
  
• Next you get a progress screen, click Next to finish the HADR setup.
  

That’s it for this guide. I will continue playing with HADR and post some reviews afterwards.

Your remarks are welcome.

Tom