Befriending Dragons

Transform Tech with Anti-bullying Cultures


Leave a comment

New Opportunities to be a SQL Master

Have you ever wanted to prove to yourself and others just how much you really know about SQL Server? The MCITP exams are a great start; they show you have a baseline level of knowledge about the product. However, sometimes it is possible to pass the MCITP level exams with little real world experience. For those of us with many years of real world experience we can take it one step further. The next level is the Microsoft Certified Master aka MCM. Before last week, to get the MCM certification for SQL Server your only choice was to buy the training bundled with the exams. The training session was three weeks of high speed, in depth schooling across the engine delivered by some of the top experts in the industry. It was a great networking and education experience: I learned a lot and made some great friends. However, when part of the requirement for the certification was that you could afford to take three weeks off work and pay a lot of money for the privilege of taking a set of exams many still didn’t pass, it unfairly excluded some very talented people. So as of last Tuesday some great changes were announced to the SQL MCM program. Now the training is no longer bundled with the exams. After the changes to the program that were just announced, you can choose to take as much or as little training as you want. There are now many hours of free training already available online, and you can still choose to pay for instructor led training from industry experts. Once you feel you are prepared, you can sign up for the written exam for $500. If you pass that filter, then you can sign up for the 6 hour lab exam for $2000. If you fail either exam there is a waiting period before you can retake it. The exams are not available at all Prometric locations due to concerns about security and/or bandwidth in some places, but at least it is no longer restricted to just Redmond. The tests are still extremely difficult and the precautions to protect against devaluation of the exams via cheaters are extensive. But now the MCM testing is available to a wider range of experienced, knowledgeable SQL Server experts, and I hope that if you feel you go above and beyond the average senior DBA you will consider preparing for the exams. Good luck!

 

·         SQL Server Masters Certification Goes Global

·         New Path to Microsoft Certified Master: Microsoft SQL Server 2008

·         Data sheet (executive summary)

·         SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos

·         Current SQL Server MCMs

 


Leave a comment

Out with the Old (SQL 7.0) and in with the New (SQL 2008 SP2)

We are fast approaching the end of support for SQL Server 7.0. It has been in “extended support” for a while, meaning you could call for help but no non-security hotfixes would be created. But after January 11, 2011 extended support for SQL Server 7.0 ends and you will no longer be able to contact Microsoft for help with SQL Server 7.0. See more details here: http://blogs.msdn.com/b/sqlreleaseservices/archive/2010/09/29/end-of-support-for-sql-server-7-0.aspx

But everything is not ending! Indeed we have a new service pack for SQL Server 2008! SQL Server 2008 SP2 is now available for download! SP2 includes all hotfixes from SP1 CU1 to CU8. You can also get the updated feature pack.

 

•          SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550

•          SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551

•          SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815

 

Key improvements in Microsoft SQL Server 2008 Service Pack 2 are:

 

  • Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services integration with SharePoint products.   SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in to support the integration of SQL Server 2008 R2 report servers with SharePoint 2007 products. This now enables SharePoint Server 2007 to be used with SQL Server 2008 R2 Report Server. For more information see the “What’s New in SharePoint Integration and SQL Server 2008 Service Pack 2 (SP2)” section in What’s New (Reporting Services).
  • SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.
    • SQL Server 2008 Instance Management. With SP2 applied, an instance of the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2 Utility Control Point as a managed instance of SQL Server. SQL Server 2008 SP2 enables organizations to extend the value of the Utility Control Point to instances of SQL Server 2008 SP2 without having to upgrade those servers to SQL Server 2008 R2. For more information, see Overview of SQL Server Utility in SQL Server 2008 R2 Books Online.

o   Data-tier Application (DAC) Support. Instances of the SQL Server 2008 Database Engine support all DAC operations delivered in SQL Server 2008 R2 after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.


2 Comments

Online learning with Kalen Delaney – SQL Server 2008 Indexes – Internals and Best Practices

Kalen always does a great job of explaining the guts of SQL Server. She has a deep, intuitive grasp of index internals. Even better, she can take that knowledge and share it so everyone can learn something, whether you’re a relative newbie or a veteran. This particular workshop is a deep-dive with advanced topics so if you have a good grasp of the basics and want to hone your index skills, sign up now for Friday’s online workshop for only $99 and learn all about index internals and best practices. You can even get a DVD as part of the registration (but it costs extra).

Workshop: SQL Server 2008 Indexes – Internals and Best Practices http://www.vconferenceonline.com/shows/workshops/indexing.asp or http://www.vconferenceonline.com/event/home.aspx?id=130

Date: Friday, July 23, 2010 at 9:00a Pacific

And learn more about Kalen Delaney and her SQL Server 2008 Internals book here: http://www.sqlserverinternals.com/about.html


Leave a comment

CTP of SP2 for SQL Server 2008 is now available

SQL Server 2008 will soon have a new service pack – SP2. SQL Server service packs go through a CTP (aka beta) cycle prior to release. The CTP of SP2 is now available for you to download and test. This is only the pre-release version, so only use it for testing.

 

·         SQL Server 2008 Service Pack 2 CTP http://www.microsoft.com/downloads/details.aspx?FamilyID=65606fdd-093f-4c70-91f6-dc1f24520e8f&displaylang=en

·         SQL Server 2008 Express Edition Service Pack 2 – CTP http://www.microsoft.com/downloads/details.aspx?FamilyID=c56e753d-13ef-4b95-a5eb-0b470bf56341&displaylang=en

·         Feedback via Connect http://connect.microsoft.com/SQLServer/Feedback

·         SQL Server 2008 Service Pack 2 Customer Technology Preview Available http://blogs.msdn.com/b/sqlreleaseservices/archive/2010/07/07/sql-server-2008-service-pack-2-customer-technology-preview-available.aspx

“Today marks the release of the Customer Technology Preview (CTP) for Microsoft SQL Server 2008 Service Pack 2.  Microsoft SQL Server 2008 Service Pack 2 includes:

·         All hotfixes released up to Microsoft SQL Server 2008 SP1 CU 8.

·         Updates to the Reporting Services Add-in for Microsoft SharePoint Products 2007 allowing customer to connect to SQL Server 2008 R2 Report Servers.

·         Support for SQL Server 2008 R2 version of Management Studio to perform operations for data-tier applications (DAC) in SQL Server 2008 instances that have SP2 installed.  For more information please visit http://msdn.microsoft.com/en-us/library/ee240739.aspx.

·         Support for SQL Server 2008 R2 Utility Explorer and SQL Server Utility viewpoints in (SSMS) provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP).  For more information please visit http://msdn.microsoft.com/en-us/library/ee210557.aspx.

 We encourage customers to try Microsoft SQL Server 2008 SP2 CTP, and provide us your feedback.  We are actively reading all feedback and appreciate any thoughts that you might have to help us with this product update.  To provide feedback please visit the Microsoft Connect Feedback Center.”

·         List of the bugs that are fixed in SQL Server 2008 Service Pack 2 http://support.microsoft.com/kb/979450

 

SQL Server Servicing plans

http://blogs.msdn.com/sqlreleaseservices/archive/2010/02/12/sql-server-servicing-plans.aspx

 

·         SQL Server 2008 SP2 is targeted for the third quarter of calendar year 2010.

·         SQL Server 2005 SP4 is targeted for the fourth quarter of calendar year 2010. This is planned to be the last SQL Server 2005 service pack.

 

SQL Server Service Pack support (mostly) ends 12 months after the next service pack releases. So 1 year after SQL Server 2008 SP2 releases you need to be have all SQL Server 2008 instances on SP2 (and optionally with SP2 based cumulative updates (CUs) applied). There is very limited support for older service packs under our new SP policy.

 

·         Service Pack support (general) http://support.microsoft.com/?LN=en-us&scid=gp%3B%5Bln%5D%3Blifecycle&x=17&y=10#ServicePackSupport

·         SQL Server lifecycle http://support.microsoft.com/gp/lifeselectindex#S


Leave a comment

How to slipstream SP1 based CU5 into SQL Server 2008

Starting with SQL Server 2008 SP1 you can “slipstream” SP1 and/or SP1 + SP1 based CUs to avoid a multi-step installation process.

 

http://blogs.msdn.com/petersad/archive/2009/04/16/create-a-merged-slipstream-drop-containing-sql-server-2008-server-pack-1-and-a-cumulative-update-cu-based-on-server-pack-1.aspx

 

To install a slipstreamed instance with SQL08+SP1+SP1-based-CU5 I followed these steps based on the above blog by Peter Saddow:

1)      You will probably want to rename the directory (and therefore update the CUSource entry in the Defaultsetup.ini files) to indicate exactly which CU you have slipstreamed.

2)      Instead of the blog reference in his step 1, you can optionally follow the more official KB 955392, choosing option/procedure 2 (Create a merged drop) to create the slipstream files of RTM+SP1.

a.       To make sure I got the commands exactly right I cut/pasted them into a .cmd file and ran that.

b.      Do NOT do step 7 “Start the Setup program” because you still need to add the CU files.

3)      Download your CU of choice which is likely different than what is listed in the blog. For SP1 based CU5 you will download the first file (SQL_Server_2008_SP1_Cumlative_Update_5) from http://support.microsoft.com/kb/975977.

4)      For CU5, run 399273_intl_x64_zip.exe, 399273_intl_ia64_zip.exe, and 399273_intl_ia64_zip.exe to extract the files SQLServer2008-KB975977-{architecture}.exe (which are the actual setup files for each architecture). You will need the password from the hotfix email you got from the 975977 link/email.

5)      In the blog’s step 4 which tells you to extract files from the setup files, use your actual KB number. For SP1-based-CU5 the KB is 975977 so the commands will be SQLServer2008-KB975977-{architecture}.exe /x:c:SQLServer2008_FullSP1_CUCU

6)      Run setup with the “run as admin” option on Windows 2008/Vista/Windows 7.

7)      If you later move the setup files to a different location you will need to update the Defaultsetup.ini file in each of the architecture directories (x86, x64, ia64).


Leave a comment

How to Find the Amount of Fragmentation on Your SQL Server Instance

Need to find the amount of fragmentation in all your indexes in all databases for a given instance of SQL Server 2005 or later? You can use the undocumented/unsupported sp_MSforeachdb to loop through all the databases. For more information on fragmentation see my previous blog http://blogs.msdn.com/cindygross/archive/2009/11/20/sql-server-and-fragmentation.aspx.

 

— Cindy Gross 2009

— find fragmentation on all indexes in all databases on this instances

— to find fragmentation on just one db comment out the exec master… line and the last quote

— you must use the db_id() rather than NULL as the first parameter of the DMV or it will try to do a join across all dbs

SELECT @@SERVERNAME, @@VERSION, GETDATE() as BatchStartTime

exec master.sys.sp_MSforeachdb ‘ USE [?];

 

DECLARE @starttime datetime, @endtime datetime

SELECT @starttime = GETDATE()

SELECT db_name() as CurrentDB, @starttime as DBStartTime

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id, DB_ID()) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent, page_count, index_depth, index_type_desc, alloc_unit_type_desc

— , record_count, avg_page_space_used_in_percent –(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

–WHERE index_id > 0 — exclude heaps

ORDER BY DatabaseName asc, avg_fragmentation_in_percent desc, ObjectName asc, IndexName asc

SELECT @endtime = GETDATE()

SELECT @starttime as StartTime, @endtime as EndTime, DATEDIFF(MINUTE,@starttime,@endtime) as TotalMinutes


Leave a comment

How People Abuse Their SQL Server Transaction Log – Things NOT to Do

Care and Feeding of the Transaction Log

 

Want to learn more about how the transaction log works? Kalen Delaney, celebrated author of SQL Server 2008 Internals and the Inside SQL Server series as well as a world class trainer and performance tuner,  is giving a pre-conference talk at SQL PASS next week. The pre and post conference sessions are full day sessions you can purchase as an add-on to the regular conference (which runs Tuesday-Thursday). Kalen’s pre-conference session “Care and Feeding of the Transaction Log” is scheduled for Monday, November 2, 2009. She will be raffling off copies of her book and will have copies of her DVD to give away. If you are guilty of any of the transaction log practices below, or have colleagues who are and you need ammunition in your fight for change, Kalen’s session will be a great help to you! Note that she will not necessarily be covering all the items below, they are my own list and not hers. 🙂

How People Abuse Their SQL Server Transaction Log – Things NOT to Do

·         Rely on autogrow – this causes fragmentation, poorly allocated VLFs, a performance hit for duration of grow, etc.

·         Don’t take backups at all or do them on a schedule that doesn’t meet SLAs or doesn’t keep the log “small enough”.

·         Use simple recovery mode in the belief it will improve performance when there are no bulk inserts or something else that actually benefits from minimal logging.

·         Want to “turn off logging”.

·         “Shrink” the log by deleting the LDF file – can result in corruption and an unusable database.

·         Shrink the log file when it will just grow again.

·         Add multiple LDF files due to the mistaken impression that this will improve performance (multiple threads myth or thinking it will split IO over multiple files when logs are mostly sequential). This is particularly a problem with TempDB where we recommend 1/4 to 1 file per core and don’t always make it clear we mean only the data files and not the log file.

·         Only backing up the log files when they reach a certain percentage full to reduce the number of backups occurring at any one time – this can result in not being able to meet your recovery SLAs.

·         Enable instant file initialization and expect it to help with log growth.

·         Create one extremely large log file without regard to # of VLFs created.

·         Sharepoint documents have recommended simple recovery mode to “keep the log from filling” but don’t explain the tradeoffs for recoverability such as losing Point in Time recovery options.

·         Don’t understand the difference between truncating and shrinking the log.

·         Don’t realize that in simple mode you still have to do a full backup first.

·         Don’t realize they need to do a log backup after switching from simple or bulk logged to full.

·         Think DBCC CHECKDB checks for inconsistencies in the log

·         Put data and log on same drive (mix random and mostly sequential), put lots of logs on one drive (defeats purpose of having a log/sequential writes on own IO path), put output of profiler or other activity on same drive as a log

·         Don’t put log on fast enough/properly configured drive (RAID 10, disk partition alignment, separate from file server/other SQL/db data, disk allocation unit = 64k, HBA queue depth 64+, anything that gets writes to 3ms or less, etc.)

·         Ignore corruption messages/event log messages about bad IO

·         Don’t secure the directories where the MDF/LDF and backup files reside

·         Ignore the fact that Tran replication affects log size (can’t truncate until log reader has read data from log)

·         Do many transaction log backups between full backups (with few or no differentials) and don’t consider what happens if one of the files is corrupted/missing or how long it will take to do the restores

·         Don’t test the DR strategy, or at least have it documented and planned/thought out

·         Don’t exclude the MDF/LDF files from virus scanning software

 

Instead of abusing your transaction log, pay attention to Pond’s Twelfth Law: Don’t practice in front of the CIO.  A professional prepares ahead of time. To help you prepare, attend Kalen’s pre-conference session!


1 Comment

SQL Server Security Granularity

I have had some questions recently about how to grant developers certain permissions without giving them sysadmin rights. Hopefully this summary will help you determine how to grant the least possible privileges. The summary is based on SQL Server 2005 but will also apply to SQL Server 2008.

·         I would hesitate to grant any more permissions in development than they get in production. This means avoid not only sysadmin but also db_owner where possible.

o   This avoids problems where they spend a long time developing something only to find out at the last minute that it won’t work with production permissions.

o   If there is any production data on the development system it may be more vulnerable to attack when more people have elevated permissions.

o   As an alternative you may want to create an application that lets them submit requests to do things that require elevated permissions. It can log on to SQL Server with the appropriate permissions and perform whatever action they need. It can optionally log this activity, create a change request ticket, email the DBAs, or whatever you like. This should reduce the chance that some elevated permission need makes it into the application because it is much more obvious when they are performing an activity that they or the application will not be able to do in production.

·         Generally you will not want to grant CREATE DATABASE permissions to non-DBAs. Creating databases involves OS level permissions and space management, performance considerations, best practice implementations, backups, maintenance, etc. Also, the creator of a database can make themselves a db_owner which is usually more than a developer needs. If you do decide to grant permissions to create databases, the permission is GRANT CREATE DATABASE TO … and/or GRANT ALTER ANY DATABASE TO ….

·         The KILL command to kill an existing SPID requires either PROCESSADMIN or SYSADMIN role membership. The PROCESSADMIN role includes both ALTER ANY CONNECTION and ALTER SERVER STATE and the combination of the two are required to use the KILL command.

·         To run SHOWPLAN or use the GUI actual/estimated execution plans to see execution plans, you can GRANT SHOWPLAN TO… in the database(s) that contain the objects referenced in the queries. They also need permission to execute the query itself. There is no need to grant anything more than the ability to execute the query if you just want to SET STATISTICS TIME or SET STATISTICS IO. The danger in granting this permission is that the plan could theoretically contain information about data or the schema that would help a hacker.

·         To run SQL Profiler you can GRANT ALTER TRACE TO…. The danger is that the user can see information about the schema and sometimes the data that could be used to hack into the system.

·         To use Job Activity Monitor, first add the login or group as a user in the MSDB database. Then add them to the operator role:

sp_addrolemember ‘SQLAgentReaderRole’, ‘test1’

·         Using the Activity Monitor requires VIEW SERVER STATE and SELECT on sysprocesses and syslocks. The SELECT on sysprocesses and syslocks is granted by default to PUBLIC and therefore everyone, but VIEW SERVER STATE has to be explicitly granted.

·         To run the Database Tuning Advisor (DTA) you need SHOWPLAN permissions and the ability to execute the queries in all the databases in the workload. However, if the trace file used as input includes the LoginName data column DTA will try to impersonate the users and therefore permission needs to be granted to each user OR you can avoid collecting the LoginName data column. Right after a new instance of SQL Server is installed, a sysadmin must run DTA once before anyone else can use it initialize some settings.

·         To create objects, you have a couple of choices. You can GRANT CREATE TABLE, GRANT CREATE PROCEDURE, etc. in each database. Alternatively you can add them to the db_ddladmin role in the appropriate databases. This will grant them VIEW ANY DATABASE and the database level permissions ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES

 

This query will show you the list of available privileges:
select * from sys.fn_builtin_permissions (DEFAULT)


12 Comments

How to configure DTC for SQL Server in a Windows 2008 cluster

How to configure DTC for SQL Server in a Windows 2008 cluster

[This post was revised 11 May 2009 with new mapping information, fixed formatting issues 23 March 2010]

With Windows 2008 you now have the option to use multiple DTCs. There is a lot of confusion over whether to do that for SQL Server and what the exact steps are. If you use multiple DTCs you must also make sure you map each SQL Server to a specific DTC.

How and Where

Here is some information to help you choose how many DTCs to have and where to put them:

Multiple DTCs:

·         Positive: Better performance (not sure how noticeable to the average application)

·         Negative: More administration

One DTC:

·         Positive: Easy to admin

·         Negative: May become overloaded/performance bottleneck (probably rare)

One DTC per SQL Server but all the DTCs in one group instead of in each SQL group:

·         Negative: you have to assign a disk and IP to each

·         Positive: you can have DTC affect the group – if you’re willing to have all DTCs failover when one fails

·         Negative: you can end up with DTC and SQL on separate nodes which (with the possible exception of a geo-cluster) probably has a negligible performance impact but still could theoretically present more problems than having DTC and SQL both local.

One DTC no matter how many SQL Servers – in its own group

·         Negative: if it does down/fails over it affects all SQLs (or other apps that use it)

·         Positive: easy to set up/maintain

·         Negative: DTC and SQL can be on separate nodes (see above)

·         Negative: you have to assign a unique disk and IP

One DTC no matter how many SQL Servers – in one of the SQL groups

·         Negative: if DTC goes down/fails over it affects all SQLs (or other apps that use it)

·         Positive: easy to set up/maintain

·         Negative: DTC and SQL can be on separate nodes (see above)

One DTC per SQL Server, each in the same group as the SQL Server it is mapped to

·         Positive: If you need to move or restart DTC it only affects one SQL Server

·         Positive: no extra disks or IPs needed (though giving DTC its own disk is still recommended for the best performance)

·         Positive: it’s easy for admins to see how its configured (assuming they didn’t forget the mapping)

·         Negative: requires more administration (though still very minimal, just a couple minutes per DTC)

·         Positive: best performance

·         Positive: It can simplify troubleshooting as your testing can be isolated to one group.

One DTC per SQL Server, each in its own DTC group with only a disk, IP, and name

·         Positive: DTC can affect the group

·         Negative: requires more disks and IPs

·         Negative: DTC and SQL can be on separate nodes (see above)

Multiple DTCs, but some DTCs service more than one SQL

·         Combination of above

Steps for DTC in a SQL Group

Here are the steps for creating DTC in a Windows 2008 cluster with one DTC per SQL Server and the DTC is in the SQL Server group. The ability to use multiple DTCs is new in Windows 2008, for older versions of Windows these steps will not work.

In Windows 2008 you can no longer make DTC dependent on the quorum disk so it either needs its own disk or can share with an application such as SQL Server. For the best performance you should give DTC its own disk.

There are two ways you can go through these steps, both options are covered below.

1. Connect

·         Start “Failover Cluster Management”

·         Connect to your cluster (click on the local cluster on the left or choose “Manage a Cluster” on the right and enter your remote cluster name)

2. If you are using a firewall make sure it has an exception for DTC and/or the DTC IP addresses.

3. Add a new DTC resource using one of the two options below.

 This is Option 1  from the blog mentioned at the end of this thread. The advantages are:

·         DTC may have its own IP and name.

·         Some steps can be completed before SQL is installed.

·         Requires a disk NOT used by SQL Server during setup, but it can be a disk that will later be used by SQL Server.

·         You can choose the name of the DTC resource.

 

a)      Create a new DTC resource

                                i.            Look for a green arrow in the middle pane next to “Configure a Service or Application” and click on it to open the “High Availability Wizard”

                              ii.            Choose “Next” to go to the “Select Service or Application” screen

                            iii.            Choose “Distributed Transaction Coordinator (DTC)” then click “Next”

                             iv.            You can either use the default name or create your own unique name for the “Client Access Point” then click “Next”. You may want to name it something like DTC_YourSQLServerResourceName. For example: if your SQL instance’s virtual name is SQL01, call your DTC resource something like DTC_SQL01 or MSDTC-SQL Server (SQL01).

                               v.            Choose a disk that is not already used and will not be the disk you choose during SQL Server setup.

                             vi.            Click “Next” twice.

b)      Install SQL Server on at least one node.

c)      Move DTC to the SQL Server group

                                i.            Right click on the DTC resource and choose “More Actions” then “Move this resource to another service or application”.

                              ii.            Choose your SQL Server group in “Select A Service or Application”.

                            iii.            Right click on the DTC resource and choose “Properties”. Go to the “Policies” tab and uncheck “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over. This is the same thing as “affect the group” in older versions of Windows.

                             iv.            Optionally you can take DTC offline and make it dependent on the SQL Server name and disk. Then remove its dependencies on the original name and disk. Then you can optionally delete the original IP, name, and disk or move them out of the SQL Server group.

d)      (Optional for SQL Server 2008) Map SQL Server to this DTC resource. If you skip this step SQL Server 2005 will use the default DTC. SQL Server 2005 will only use the DTC in the SQL Server group if that one is the default for the entire cluster or if you do the mapping step. SQL Server 2008 will use the DTC in its group unless that DTC is not online/started when SQL Server starts or if you create a mapping to a different DTC.

                                i.            Open a dos prompt using “Run as Administrator”.

                              ii.            Modify the below statement with a mapping name (make up a name to id the mapping), service (the SQL Server service name – not the display name), and ClusterResourceName (DTC resource name) then run it
Example: Msdtc -tmMappingSet -name DTC_SQL01_Mapping -service “MSSQL$SQL01” -ClusterResourceName “MSDTC-SQL Server (SQL01)”  <<– Note the correction I made here on 3/21/09 to the service name for SQL, and the additional verification steps below
Do you want to continue with this operation? [y/n]
y
[enter]

                            iii.            Run “msdtc -tmmappingview *” to verify the mapping was created successfully. The mapping is also stored in HKEY_LOCAL_MACHINEClusterMSDTCTMMappingService as a new key named the same as the mapping parameter used in the msdtc command.

                             iv.            To verify SQL Server is mapped to the expected instance of DTC you can either

§Shut down all the other DTC services and make sure the mapped DTC is online. Verify you can do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server.

§Do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server, verify it succeeds and roll it back. Then shut down the mapped DTC and verify a new BEGIN DISTRIBUTED TRANSACTION will work.

e)      Enable network access

                                i.            Run DcomCnfg

                              ii.            Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs

                            iii.            Right click on the DTC service for this SQL Server group and select “Properties”.

                             iv.            Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.

                               v.            When you hit “Apply” or “OK” it will restart the DTC resource.

This is Option 2 from the blog mentioned at the end of this thread – Pros/Cons include:

·         DTC will depend on the SQL Server resource name and IP

·         SQL must be installed first

·         You have no choice on the DTC resource name

·         Fewer overall steps

 

a)      Install SQL Server on at least one node.

b)      Add DTC

                                i.            Right click on your SQL Server group under “Services and Applications” and choose “Add a resource” then “More resources” then “2 – Add Distributed Transaction Coordinator”.

                              ii.            Right click on the new (offline) DTC resource and choose “Properties”. Note that the DTC resource name is MSDTC-SQL Server (YourInstanceName) and it cannot be changed.

                            iii.            Go to the “Dependencies” tab make DTC dependent on a disk in the SQL Server group. If DTC is not heavily used you can choose a disk already used for SQL Server, but this could have a performance impact.

                             iv.            Go to the “Dependencies” tab make DTC dependent on the SQL Server name.

                               v.            Go to the “Policies” tab and uncheck “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over.

                             vi.            Bring DTC online.

c)      (Optional for SQL Server 2008) Map SQL Server and this DTC resource. If you skip this step SQL Server 2005 will use the default DTC. SQL Server 2005 will only use the DTC in the SQL Server group if that one is the default or if you do the mapping step. SQL Server 2008 will use the DTC in its group unless that DTC is not online/started when SQL Server starts or if you create a mapping to a different DTC.

                                i.            Open a dos prompt using “Run as Administrator”.

                              ii.            Modify the below statement with a mapping name (make up a name to id the mapping), service (the SQL Server service name – not the display name), and ClusterResourceName (DTC resource name) then run it
Example: Msdtc -tmMappingSet -name DTC_SQL01_Mapping -service “MSSQL$SQL01” -ClusterResourceName “MSDTC-SQL Server (SQL01)”  <<– Note the correction I made here on 3/21/09 to the service name for SQL, and the additional verification steps below
Do you want to continue with this operation? [y/n]
y
[enter]

                            iii.            Run “msdtc -tmmappingview *” to verify the mapping was created successfully. The mapping is also stored in HKEY_LOCAL_MACHINEClusterMSDTCTMMappingService as a new key named the same as the mapping parameter used in the msdtc command.

                             iv.            To verify SQL Server is mapped to the expected instance of DTC you can either

§Shut down all the other DTC services and make sure the mapped DTC is online. Verify you can do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server.

§Do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server, verify it succeeds and roll it back. Then shut down the mapped DTC and verify a new BEGIN DISTRIBUTED TRANSACTION will work.

d)      Enable network access

                                i.            Run DcomCnfg

                              ii.            Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs

                            iii.            Right click on the DTC service for this SQL Server group and select “Properties”.

                             iv.            Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.

                               v.            When you hit “Apply” or “OK” it will restart the DTC resource.

Hints:

·         Repeat the above steps for any additional SQL Server instances you install.

·         If you use DTC heavily you should consider putting it on its own disk for optimal performance. Otherwise you can associate it with your least used SQL Server disk.

·         Do NOT make SQL Server dependent on the DTC resource.

·         Do NOT check “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over.

More information:

·         SQL Server 2008 Failover Clustering White Paper http://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx (link updated 15 April 2014)

·         How to Configure Multiple Instances of Distributed Transaction Coordinator (DTC) on a Windows Server Failover Cluster 2008 http://blogs.technet.com/askcore/archive/2009/02/18/how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspx

·         Windows Server 2008 Changes Simplify SQL Server Clustering  http://www.sqlmag.com/Articles/ArticleID/101502/101502.html?Ad=1