A Helpful SQL Server DBA Checklist

Update: As Omer points out in the comments to this entry, the recommendations made in the DBA Checklist mentioned below are in places at odds with the official BizTalk/SQL Server best practices. When reviewing the checklist, please refer to the Microsoft BizTalk SQL Server Best Practices KB article.

SimpleTalk has a helpful DBA checklist for all those BizTalkers who double-hat and manage a SQL Server as-well-as a BizTalk environment. It covers a number of useful topics, including:

• General best practices
• High-availability
• Performance tuning
• Application coding and design
• SSIS, Analysis Services, Reporting Services & Service Broker

Plenty of content for both new and experienced DBA’s – well worth a look.

Related articles by Zemanta

• Microsoft lays out road map for next BizTalk releases
• SQL Server 2008 = RTM

SQL Server – Hotfix Available for Elevation of Privilege

Just a quick heads-up for those of you managing a SQL Server environment, it looks as though there is an important update for SQL Server versions 7.0, 2000 & 2005. Full details of bulletin MS08-040 are available on the Microsoft Security Bulletin Website – details are as follows (my emphasis):

This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.

This security update is rated Important for supported releases of SQL Server 7.0, SQL Server 2000, SQL Server 2005, Microsoft Data Engine (MSDE) 1.0, Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2000 Desktop Engine (WMSDE), and Windows Internal Database (WYukon).

The security update addresses the vulnerabilities by modifying the way that SQL Server manages page reuse, allocating more memory for the convert function, validating on-disk files before loading them, and validating insert statements.

The hotfix will be installed automatically by Windows Update (as it has just done on my development machine); you may want to check it on a non-production environment first to ensure there are no unwanted side affects.

Tracking Down Deadlocks using SQL Profiler

Yet more great content over on the (RedGate sponsored) Simple Talk blog, this time an excellent article on tracking down deadlocks using SQL Profiler by Brad McGhee, well worth a read before your production system starts to exhibit this behaviour!

Searching *Inside* Stored Procedures

I’ve recently inherited a large BizTalk system and needed a quick way to determine which custom stored procedures accessed which tables/views and called other sprocs. Thanks to my co-worker Shaun, I now have the power of the INFORMATION_SCHEMA views at my disposal!

Information Schema Views

Information schema views provide an internal, system table-independent view of SQL Server metadata. They provide information on tables, views, stored procedures to name but a few objects. The views included in SQL Server 2005 comply with the ANSI SQL-92 standard definition, so any queries can (theoretically) be taken and executed on Oracle, or DB2, etc. More information can be found online at http://msdn2.microsoft.com/en-us/library/ms186778.aspx

Searching within Stored Procedures

To search for – or within – stored procedures, you need to work with the INFORMATION_SCHEMA.ROUTINES view, the following sample returns all stored procedures where the name contains ‘BizTalkServerApplication’:

–– Change to the BizTalk Message Box database.
USE BizTalkMsgBoxDb
GO
–– Search for sprocs with ‘BizTalkServerApplication’ in the name.
SELECT ROUTINE_NAME,
CONVERT(VARCHAR(8), created, 3) + ‘ ‘ + CONVERT(VARCHAR(8), created, 108) AS ‘Created Date’,
CONVERT(VARCHAR(8), last_altered, 3) + ‘ ‘ + CONVERT(VARCHAR(8), last_altered, 108) AS ‘Last Altered On’
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE ‘%BizTalkServerApplication%’
ORDER BY 1

To search within stored procedures, simply change your WHERE clause to use the ROUTINE_DEFINITION column (Note: the ROUTINE_DEFINITION column only includes the first 4000 characters of the T-SQL statements that created the stored procedure). The following sample returns all stored procedures that contain the text ‘SuspendedQ’:

–– Change to the BizTalk Message Box database.
USE BizTalkMsgBoxDb
GO
–– Search for sprocs that contain the text ‘SuspendedQ’ in the name.
SELECT ROUTINE_NAME,
CONVERT(VARCHAR(8), created, 3) + ‘ ‘ + CONVERT(VARCHAR(8), created, 108) AS ‘Created Date’,
CONVERT(VARCHAR(8), last_altered, 3) + ‘ ‘ + CONVERT(VARCHAR(8), last_altered, 108) AS ‘Last Altered On’
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%SuspendedQ%’
ORDER BY 1

Why Archive and Purge when you can just Purge?

In BizTalk 2004 SP2, the BizTalk Team gave us the Archive and Purge SQL Server Maintenance Job for managing the size of the Tracking Database. This was a great tool and really took away some of the admin headaches in maintaining this particular database.

The job allows administrators to archive olde tracking data and verify the integrity of the backup before purging from the live database (for detailed instructions, see the MSDN website). This is a good, pro-active practice for the health of any BizTalk environment, in that:

• BizTalkDTADb database growth is constantly checked, allowing the TDDS service to run effectively, thereby maintaining a healthy Message Box.
• Maintaining the size of the BizTalkDTADb data and log files ensures that the database doesn’t just eat all of your available disk space.
• The tracking data can be restored in a dedicated OLAP environment, allowing reports to be run without affecting the live BizTalk environment.

However, imagine a scenario where you don’t want the archived data – you simply want to purge. Where that’s the case, you can either manually run a truncate on the BizTalkDTADb (see my posts detailing how to do this under BizTalk 2004 or BizTalk 2006 – there are some subtle differences), or run the hidden admin gem dtasp_PurgeTrackingDatabase (a stored procedure used by the dtasp_ArchiveAndPurgeTrackingDatabase which just purges) on a scheduled basis, so you no longer need to worry about manually purging.

Configuring the dtasp_PurgeTrackingDatabase Stored Procedure

The Purge stored procedure is used in a very similar manner to dtasp_ArchiveAndPurgeTrackingDatabase, taking 4 parameters:

• Live Hours – Any completed instance older than the live hours + live days…
• Live Days – …will be deleted along with all associated data.
• Hard Days – all data older than this will be deleted. The time interval specified here should be greater than the live window of data.
• Last Backup – UTC Datetime of the last backup. When set to NULL, data is not purged from the database.

As an example of its usage, if you wanted to purge any tracking data older than two hours and hard-delete any data older than one day, you would use the follow T-SQL:


–– Change to the BizTalk Tracking database.
USE BizTalkDTADb
GO

–– Prune tracking data older than two hours.
DECLARE @dtLastBackup DATETIME
SET @dtLastBackup = GetUTCDate()
EXEC dtasp_PurgeTrackingDatabase 2, 0, 1, @dtLastBackup

The @dtLastBackup parameter is used to ensure that records that have not been backed-up are not deleted by the purge procedure, so we set it to the current UTC date/time to ensure that whatever live hours/days you specify, records are deleted. I’m not too sure why the development team included this as a parameter: the procedure is a wrapper that calls the dtasp_PurgeTrackingDatabase_Internal (which is also called by the dtasp_ArchiveAndPurgeTrackingDatabase procedure) so it could have been included in that wrapper given that it is always defaulted to the current UTC date/time during purges!

One other thing to note is that the wrapper script also modifies the prune before date (the date that is built by the live hours/days specified): this date date is tweaked to remove ten minutes to ensure redundancy in the remaining data.  In the example used above, rather than keeping two hours of data, there will in fact be two hours and ten minutes once the code has been run.

Using the dtasp_PurgeTrackingDatabase Stored Procedure

To start using this procedure, I would suggest that you first truncate your database, then configure the SQL Server Agent job to run every X hours/days depending on your environmental requirements. I suggest initially truncating your database to ensure the first run of the job doesn’t take several hours!

Further details about the dtasp_PurgeTrackingDatabase stored procedure can be found online at MSDN.

Diagnosing Performance Problems in SQL Server

SQL Server is the heart of a BizTalk environment, so a performant database is a must for any self-respecting BizTalk setup. But, if you consider that SQL Server is causing you performance issues, where do you start to look?

Thankfully, there is a two part series on this exact topic from Graham Kent, a SQL Server Support Team escalation engineer, where is he discusses PSSDIAG/SQLDIAG and the information that it produces to help diagnose a whole raft of possible SQL Server performance problems. Links are below, enjoy!

• .NET Performance Issues: What if I suspect that my performance problem is in SQL Server
• .NET Performance Issues: What if I suspect that my performance problem is in SQL Server Part II

Truncating the BizTalk 2006 Tracking Database

In Truncating the BizTalk 2004 Tracking  Database I discussed how to truncate the tracking database in BizTalk 2004. Over on the BizTalk Gurus forums, user Nick Busy wanted to do the same thing for BizTalk Server 2006 – he’s kindly allowed me to repost his instructions for the community on this blog:

0. Before start, ensure you have got the database admin priveleges on the database

1. Stop all BizTalk Server Host Instances

2. Full backup BizTalkDTADb database (just in case)

3. Make scripts to create views (MANDATORY)

dbo.dtav_ServiceFacts
dbo.dtav_MessageFacts
dbo.dtav_FindMessageFacts

4. Run SQL script:

use BizTalkDTADb
GO

— Drop the Views (before you perform this, ensure you take copies of these views!)
— unfortunately, it’s necessary for SQL 2000, but you can skip it for SQL 2005
Drop View dbo.dtav_ServiceFacts
Drop View dbo.dtav_MessageFacts
Drop View dbo.dtav_FindMessageFacts
Go

— Truncate the necessary Tables
Truncate Table dta_CallChain
Truncate Table dta_DebugTrace
Truncate Table dta_MessageInOutEvents

Truncate Table dta_ServiceInstanceExceptions
Truncate Table dta_ServiceInstances

Truncate Table Tracking_Fragments1
Truncate Table Tracking_Parts1
Truncate Table Tracking_Spool1

Truncate Table dta_MessageFieldValues

— end of the script

5. Update statistics on BizTalkDTADb database

— update statistics
exec sp_updatestats

6. Run the saved scripts (see step 3) to recreate the dropped views from your own environment.

7. Shrink BizTalkDTADb database (sometimes it doesn’t work from GUI, so using sql command will help)

— shrink database
dbcc shrinkdatabase (BizTalkDTADb, 10)

8. Start BizTalk Server Host Instances

9. Configure and enable SQL Agent job “DTA Purge and Archive” (to avoid over-growing the database in the future)

P.S. The script above does not truncate Rule Engine related tables.

Thanks Nick, much appreciated.

Can I get some BI with this?

While I’m lying in bed dying of flu for the second time this year (grrr!!), here is a little light-hearted look at which tastes better: SQL Server 2005 or Oracle 11g. Click on the screenshot or go to www.databasetastechallenge.com.

“Now…. Who’s still thirsty!”

An Easier Way to do Complex FOR XML EXPLICIT

I’ve got to confess that I’m not all that good with the FOR XML EXPLICIT syntax to generate Xml straight out of SQL Server – although there are a few good resources on the technology, I don’t spend enough time writing it and just get hopelessly muddled when I have to write anything remotely complex.

So, after posting a plea for help to the SQL Server Xml MSDN Forum I was pleasantly surprised to learn about the new PATH Mode for generating Xml that is available in SQL Server 2005. To quote the MSDN website:

“…the PATH mode provides a simpler way to mix elements and attributes. PATH mode is also a simpler way to introduce additional nesting for representing complex properties. You can use FOR XML EXPLICIT mode queries to construct such XML from a rowset, but the PATH mode provides a simpler alternative to the potentially cumbersome EXPLICIT mode queries. PATH mode, together with the ability to write nested FOR XML queries and the TYPE directive to return xml type instances, allows you to write queries with less complexity.”

So, using PATH mode, you can generate Xml along the lines of:

With T-SQL as simple as this:

Michael Rys has a couple of good blog posts on the subject here and here – well worth a read.

Further BizTalk & MSDTC Issues

Update 22nd June 2009: I’ve just encountered yet another issue trying to get MSDTC working. The culprit this time was a rogue entry in the hosts file (C:WINDOWSsystem32driversetchosts) which had an incorrect IP address specified for the server I was trying to ‘DTC Ping’.

I encountered yet another MSDTC issue when talking to a remote SQL Server database following a BizTalk 2006 development environment upgrade from Windows Server 2003 Ent. Ed. to Service Pack 1.

The usual ‘Mutual authentication required’ / ‘No Authentication Required’ problem reared its head, and although this needed to be resolved, I was still receiving the ‘New transaction cannot enlist in the specified transaction coordinator‘ error when BizTalk attempted to do anything involving a distributed transaction. Even with a fresh installation and reconfiguration of MSDTC, I still couldn’t successfully run the DTCPing tool – the RPC test ran fine, but the Binding test failed every time:

As discussed in this blog entry by Romualdas MSDTC additionally requires ‘NetBIOS over TCP/IP’ functionality enabled on the network adapter/s participating in the DTC transaction – this setting is disabled by default when using static IP addresses:

Because MSDTC uses NetBIOS to resolve remote machine names (rather than DNS Fully Qualified Names which is a little odd reading this O’Reilly article), the DTCPing Binding test was unable to resolve the machine initiating the call and hence the failure of the test. Setting the network adapter to enable NetBIOS over TCP/IP resolved the issue (no restart was required).