SQL Server Consolidation

Many companies are now looking to consolidate their SQL Server instances. The old strategy of one instance per server can be wasteful of resources. Often large chunks of physical resources sit idle and a lot of money is spent on electricity to power all those machines. If you can consolidate even some of your instances onto fewer machines, you can save both physical and human resources. Note that any one of the below strategies will work on either a standalone or clustered system.

 

Some of the pros/cons of each common type of consolidation strategy:

 

Hyper-V virtualization

·         Great when you have to use a version of the OS, drivers, or applications that aren’t used anywhere else or that don’t ‘play well with others’.

·         Can be more flexible and easier to move to another host machine than a physical instance.

·         Has great performance for SQL Server if configured per best practices, you can get the same throughput at a slight cost in increased CPU usage.

·         Network intensive applications may have a higher network and CPU cost on a VM.

·         For now, any Hyper-V virtual machine (VM) is limited to only 4 CPUs assigned per VM (for Windows 2008, 2 CPUs for Windows 2003 guest OS).

·         For now, any Hyper-V virtual machine is limited to 64GB of RAM per VM.

·         Requires x64 chips with Intel VT or VMD virtual with DEP enabled

·         Allows total isolation of the entire environment.

 

Multiple instances

·         Very good for isolating security (assuming each SQL Server service starts with a different account).

·         Allows each instance to be managed and configured to meet the needs of a single group of users. This often makes downtime for SQL Server patches easier to arrange.

·         There is some additional overhead, mainly memory, required compared to a single instance because each instance has some allocations that occur at startup regardless of actual usage. However, this is usually low, especially on today’s high RAM systems.

·         Allows multiple versions of SQL Server to be installed at once with each application on its preferred version. The different version could mean 2000 vs. 2005 vs. 2008, or it could mean different service pack and hotfix levels.

 

Single instance with multiple databases/applications

·         This method can be very cost effective and is often easy to manage as long as the applications/databases don’t have performance problems or cause conflicts with one another.

·         If a SQL Server patch has to be applied for one database, all the databases share the downtime. The administration is easier because the patch only has to be applied once, but it can be more difficult to arrange downtime that is acceptable to all users.

·         This method can cause security problems. If any application has permissions, such as sysadmin role membership, which extends outside of its own database it can affect other databases/applications on the server.

o   One example of a problem is an application with sysadmin permissions that changes configuration settings, TempDB settings, or other things that affect other databases/applications either directly or indirectly.

o   Another example is that if an application doesn’t protect against SQL injection, it can allow a hacker into the database. If that database is on an instance with other databases and the id used by the hacked application has elevated permissions such as sysadmin then the hacker now has access to all the other databases.

o   This could include accidental or intentional changes by internal employees or contractors, so the danger is not limited to databases accessible through the internet.

·         Applications share TempDB which can sometimes be a bottleneck depending on the way the applications access the databases. Often this can be managed with proper TempDB sizing and number of data files.

·         Depending on how you license your software, this could possibly save you some money.

 

I hope you find this information helpful, and I have included some references below with more details on your options.

Green IT in Practice: SQL Server Consolidation in Microsoft IT

http://msdn.microsoft.com/en-us/architecture/dd393309.aspx

Running SQL Server 2008 in a Hyper-V Environment – Best Practices and Performance Recommendations

http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx

Planning for Consolidation with Microsoft SQL Server 2000

http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/SQL2KCon.mspx

SQL Server Consolidation on the 64-Bit Platform

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx

 

SQL Server 2005 Clustering Tips/References

I have copied this over from an older blog. I have cleaned it up a bit to clarify a few areas and added some links.

SQL Server 2005 Clustering Tips/References

This information will supplement the clustering information I wrote in chapter 10 of SQL Server 2005 Practical Troubleshooting: The Database Engine  

— Handy cluster related info

select SERVERPROPERTY(‘IsClustered’) as _1_Means_Clustered

, SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as CurrentNode

, SERVERPROPERTY(‘Edition’) as Edition

, SERVERPROPERTY(‘MachineName’) as VirtualName

, SERVERPROPERTY(‘InstanceName’) as InstanceName

, SERVERPROPERTY(‘ServerName’) as Virtual_and_InstanceNames

, SERVERPROPERTY(‘ProductVersion’) as Version

, SERVERPROPERTY(‘ProductLevel’) as VersionNameWithoutHotfixes

select * from sys.dm_io_cluster_shared_drives

select * from sys.dm_os_cluster_nodes

Best practices

Setup (RTM, Service Pack, Cumulative Update, or hotfix)

·         The account you use to launch setup must be a local admin on all nodes. However, it is not required that the account you choose to assign as the service account for each service be a local admin. Only setup requires local admin permissions.

·         Install and cluster DTC before installing any SQL instance. For Windows 2003, DTC should ideally go in its own group with its own disk and IP address. Second best is to place it in the cluster group and make it depend on the quorum disk and IP address. For Windows 2008 see this blog.

·        For SQL 2005, the client tools, SSIS, NS, and RS are only installed on the node where setup was run because they are not cluster aware. If you are installing as many or more instances than nodes, you can run the install program for each instance from a different node so the non cluster aware components are installed on each node. Otherwise install the non cluster aware components later on the other node(s). This means that the service packs and hotfixes must be installed on each node as well so that the non cluster aware components are updated. Setup is run once per instance and that will update all the cluster aware and components for that instance. The first time setup (RTM, SP, hotfix) is run from each node it will also update any non cluster aware components on the box such as the tools, SSIS, NS, and RS.

·         All nodes should be configured identically.

·         Windows level policies should be the same on all nodes.

·         Remote Registry, Cryptography services, and Task scheduler must be started on all nodes during the setup process.

·         No Terminal Services users can be logged in on any remote nodes during setup.

·         Do not use quotes in the password for SQL service account.

·         Do not allow <, >, ‘, “, & in the cluster group names.

·         Virtual Server name should be 14 characters or less.

·         NIC name cannot have trailing spaces.

·         Stop any non-essential applications or services as they may hold open files that setup needs to modify or may otherwise interfere with setup.

·         All nodes must have access to setup files without prompting for credentials.

·         All disks in all groups must be online during setup.

·         All other, existing instances in the cluster must have valid, non-UNC paths for the SQL Server registry keys

·         If there are many trusts for the domain the nodes/service account reside in, see KB 910070 before running setup

·         For Windows 2003, the complete node must be on HCL, for Windows 2008 each node in the cluster must pass a validation.

·         Copy the setup files to the “primary” node or at least make sure all nodes can access the setup files without being prompted for credentials.

·         Any mounted drives must have an associated drive letter and be clustered, even if they will not be used by SQL Server. Do not use mounted drives anywhere on a cluster if SQL Server 2000 will exist in the cluster. A mounted drive must be in SQL resource group and SQL Server must depend on them if you want that instance of SQL Server to use them for data or log files.

·         Verify you have not installed terminal services, there are no compressed drives, and no node is a domain controller.

·         Disable netbios on private NICs.

·         Pre-create the domain groups needed by setup.

·         Make sure the SQL Server resource is set to “affect the group”. Often it’s best to leave SQL Agent, FTS, and DTC to not “affect the group” but that depends on your business needs.

·         Test failover of all groups before any SQL setup, test again before any service packs or hotfixes. If any group gets an error during failover, address that problem before running SQL setup.

After setup

·         Go back to the cluster administrator and take the SQL Server resource offline. Make SQL Server dependent on the disk(s) and mount points in their group (any disk or mount point where you need to create data files, log files, or full text catalogs) then bring SQL Server, SQL Agent, and FTS back online.

·         For the SSIS service, %ProgramFiles%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml must have the SQL virtual server name instead of “.”

·         Configure memory to handle instances moving due to failover.

·         If you installed NS, you now have to configure it separately.

·         Leave SQL services set to manual in the services applet.

Maintenance

·         Use add/remove programs to add/remove nodes.

·         Add/remove non-cluster aware components must be done from cmd line.

·         Can rename the virtual server name but not the instance name.

·         Make IP changes in cluster admin instead of setup.

·         Cluster service account must have a login in SQL, must be sysadmin only for FTS.

·         Clustered SQL Server startup account can only be a local admin if SQL 2000/7.0 is not installed side-by-side with 2005. Even if a lower version is not installed side-by-side it is best not to make the SQL Server startup account a local administrator or otherwise give it excessive permissions.

·         After adding new node, fail to that node to  apply SPs/hotfixes.

·         For Kerberos on a cluster, you need two SPNs per instance, one with and one without the port. They must belong to the current SQL Server startup account and not to any other accounts.

·         If you use SSL encryption, install certificates on all nodes before turning on the SSL encryption.

·         If antivirus is installed, see http://support.microsoft.com/kb/250355

References:

·         Updated Books Online: http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

·         915846  Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;915846

·         819546  SQL Server 2000 and SQL Server 2005 support for mounted volumes http://support.microsoft.com/default.aspx?scid=kb;EN-US;819546

·         913815  Error message when you install a SQL Server 2005 failover cluster on a node: “The drive specified cannot be used for program location” http://support.microsoft.com/default.aspx?scid=kb;EN-US;913815

·         922670  How to use the Add or Remove Programs item in Control Panel to add or remove components for stand-alone installations and clustered installations of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;922670

·         910230  How to install SQL Server 2005 Analysis Services on a failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;910230

·         910233  Migrate a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;910233

·         912397  The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid http://support.microsoft.com/default.aspx?scid=kb;EN-US;912397

·         910851  You receive error messages when you try to set up a clustered instance of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;910851

·         926621  Error message when you try to install SQL Server 2005 in a cluster environment: “SQL Server Setup could not validate the service accounts” http://support.microsoft.com/default.aspx?scid=kb;EN-US;926621

·         327518  The Microsoft SQL Server support policy for Microsoft Clustering http://support.microsoft.com/default.aspx?scid=kb;EN-US;327518

·         254321  Clustered SQL Server do’s, don’ts, and basic warnings http://support.microsoft.com/default.aspx?scid=kb;EN-US;254321

·         942176  Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service http://support.microsoft.com/default.aspx?scid=kb;EN-US;942176

·         922209  The SQL Server 2005 Setup program does not remove all IP address cluster resources when you uninstall SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;922209

·         295732  How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installed http://support.microsoft.com/default.aspx?scid=kb;EN-US;295732

·         263712  How to impede Windows NT administrators from administering a clustered instance of SQL Server http://support.microsoft.com/default.aspx?scid=kb;EN-US;263712

·         932881  How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult http://support.microsoft.com/default.aspx?scid=kb;EN-US;932881

·         934749  BUG: Error message when you try to install SQL Server 2005 Service Pack 1 or SQL Server 2005 Service Pack 2 from the existing active node: “The product instance <InstanceName> been patched with more recent updates” http://support.microsoft.com/default.aspx?scid=kb;EN-US;934749

·         283811  How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;283811

·         910070  FIX: The SQL Server 2005 Setup program may take much longer than expected to finish running http://support.microsoft.com/default.aspx?scid=kb;EN-US;910070

·         909967  How to uninstall an instance of SQL Server 2005 manually http://support.microsoft.com/default.aspx?scid=kb;EN-US;909967

Windows 2003 SP2 is better than SP1 because:

·         918483  How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483

·         922658  SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer http://support.microsoft.com/default.aspx?scid=kb;EN-US;922658

·         904160 Network performance is slower than expected in Windows Server 2003 SP1 http://support.microsoft.com/?id=904160

Cluster specific:

·         923830  Recommended hotfixes for Windows Server 2003 Service Pack 1- based server clusters http://support.microsoft.com/default.aspx?scid=kb;EN-US;923830

What to know before you choose a SQL Server Disaster Recovery and/or High Availability solution

Why disaster recovery planning matters:

·         Do you know what your plans are to recover from various data losses? Have you ever tested those plans? Can they be implemented within your Service Level Agreements (SLA)? Are you confident your plans cover the most likely and/or most painful types of data losses?

·         Who gets to explain to the CEO and potentially the press why you were down for X hours more than the agreed upon SLA or why you were never able to recover your customers’ data at all? If it’s not you and your department that does the explaining directly, you can still bet you’ll be grilled by the person who does get to do the talking.

·         What will happen to the business if there’s a large and possibly catastrophic data loss to one or maybe even multiple sites? What will happen to your job?

Methodology:

·         Decide what you would like to protect against. Examples: hardware failures (disk, CPU, memory, etc.), loss of server, loss of server room (flooding for example), loss of electric grid in a city or section of country, malicious attack (internal or external), accidental data loss (delete wrong rows, drop database), etc. You may want to do a probability/impact matrix.

·         Decide how much downtime you can afford per type of potential downtime. For example, if it’s an electrical outage expected to last one day, will you bring up all systems on generators or bring up remote locations. It’s possible that with a bigger loss you may be able to have a longer acceptable downtime. If the entire city is without power people may expect that they won’t be able to do business locally. But they may expect that their data is available at other sites during the local outage (their last bank transaction, the prescription they just dropped off, the fantasy game predications they just entered).

·         Decide how much data you can afford to lose. Can you reenter the last X minutes/hours/days worth of data or must you be able to save every byte? What is the level of acceptable loss within your budget?

·         How long do you need to keep data backups? If there’s an accidental data loss that is not discovered for X amount of time, what will you do? What if you find out backups have been failing for the last 3 weeks and for some reason no one knew about it, but all the older backups are gone and you’ve lost the current database?

·         Once a system is taken offline, is there still a chance you’ll be asked to recover data from it? For example, regulatory requirements might demand that you be able to pull up data from 7 years ago, even if you only migrated only the most recent year from the obsolete system to your current system. Will you have the hardware, software, and operational expertise to restore an older backup taken from a system on what is now outdated hardware and software?

·         Once you know what you need to protect against, you can begin to consider technical and resource considerations that will help you meet those goals. This includes frequent testing of whatever technologies and processes you put into place. There will be many tradeoffs in cost vs. functionality. It would be astronomically expensive to protect against all types of potential failures, the business has to decide where to draw the line. The planning should be revisited periodically to make sure it still meets your needs and that it is still working properly (based on your testing).

·         Once you decide on technologies, then you can develop policies, procedures, and responsibility (departments/groups and/or people) guidelines. This is at least as important as the technologies you choose. This will include how to implement the technology on existing and forthcoming systems and monitoring the system as well as periodic testing. The testing must include the entire process across all responsible groups or it isn’t complete/accurate.

·         Determine who is responsible for making sure this is done initially and who is responsible for making sure it is revisited periodically.

SQL Server 2008

·         High Availability http://www.microsoft.com/sql/techinfo/whitepapers/SQL_2008_HA.mspx

·         Always On http://download.microsoft.com/download/c/a/f/caff7135-8d80-4dad-a104-0da8558d8a0e/Availability%20DataSheet.pdf

SQL Server 2005

SQL Server 2000

Technical options/considerations:

·         RAID arrays to reduce disk failure problems. RAID 10 is generally the best across the board.

·         Clustering to reduce failures from non-disk hardware problems (this can be local or geographically dispersed).

·         Mirroring or log shipping to protect against various types of failures (various levels/options are available).

·         Replication to provide concurrently usable copies of some data on another server (you must build your own recovery methods, there is not anything built in). You have to consider recovery from loss of publisher(s), distributor, and subscribers and scenarios that require a reinitialization.

·         Backups, which should include frequent testing of the entire restore process, both from a technology and personnel/procedure perspective. You also have to make a wise decision on full, differential, and tran log backup frequency and retention as well as compression. Point in time recovery might be an option for incorrect data updates (malicious or accidental) but may be difficult to across multiple databases. The media (local disk, remote disk/same room, very remote disk/different location, tape/dvd) should also be considered. The frequency is important and may vary depending on the time of day or even time of year. Are the backups themselves protected from loss, theft, and corruption?

·         Various recovery models and settings in the databases.

·         Database snapshots have a limited potential role is very specific scenarios (maybe to protect against accidental data loss where you would know about the problem very quickly).

·         Be able to consolidate various systems on one set of hardware (if sufficient hardware cannot be found, you may have to run a 2nd system on an existing server). This may involve a 2nd instance or just another set of databases on an existing instance. There may be issues to consider with sync’ing logins/users either across domains or if you combine two instance’s worth of databases into a single instance. Will you combine databases from different versions? This goes into the bigger issue of knowing when you can recover in the same location and/or hardware vs. new hardware/location. This could involve Virtual Server or the Hyper-V virtualization features in Windows 2008.

·         You should plan to run DBCCs on your backups to make sure they are valid.

·         Available disk space plays a big part in some of these decisions.

·         Consistency of names, disk layouts, configuration, etc. can make recovery simpler. Regardless of planned consistency, you need to have metadata, login/user, and configuration information available remotely separately from the backups (or at least know/practice how to get the necessary data from the backups).

·         For a warehouse or Analysis Services database, you will need to have the ability to rebuild from the source data. The warehouse or AS database may have to re-populated when the design changes which requires that the source data still be available.

·         Do you have the media available to apply the exact same OS, firmware, SQL Server, etc. versions including hotfixes, editions, and x86 vs. x64 vs. IA64? Do you have a way to track exactly what version/hotfix level is needed?

·         What about other applications on the box, especially those that rely on SQL Server? Do you have a recovery plan for Sharepoint, Project Server, BizTalk, Performance Point, or whatever applications you run against SQL Server databases?

·         What if you can’t restore one or more of the system databases, do you have enough information saved off (ids, password, job schedules, linked servers, etc.) to be able to rebuild the system so you can use the user databases you did have saved?

Non-technical considerations:

·         How often can you afford to test the restore process (this is a very important step and the proper resources should be dedicated to it, weigh the costs of testing against the costs if you cannot restore the data for some reason). This will probably involve training each time as people move through various positions/responsibilities and the technology changes over time as well.

·         Define general priorities in case multiple systems fail at once (such as a big storm or a malicious attack). You won’t necessarily have the hardware, bandwidth, or personnel available to restore everything at once. Where do you start?

·         Are your SLAs realistic? Well-documented?

·         Make sure the planning process is revisited at least once a year (preferably more often), including verifying that testing is still occurring and succeeding.

·         Have well-defined procedures for reacting in a timely, preferably automated fashion to technical failures (such as the backups failing due to lack of disk space or one disk in a RAID 5 array failing).

·         Will your plan facilitate/complement population of your QA or development environment? Will it facilitate/compliment the plan to rollout new systems?

·         How will you detect a problem? DBCCs, users reporting problems, etc.

·         Document completely and clearly and make sure more than one person knows where the documents and passwords are and more than one person practices implementing them. This is a very important process and you cannot afford to leave it in one person’s hands in case that person is not available at the time of a disaster.

·         Document why you didn’t implement the options you choose not to use. For example, maybe you’ll choose not to implement option X because it would put you over budget or you chose technology A over B because it was more important to have quick recovery even if it meant losing a small amount of data.

·         Know and document where you are now and where you want and need to be.

·         Know your executive sponsors and make sure they understand the tradeoffs in the system.

SQL Server 2005/2008 Now Supports Guest Clustering in a Virtual Machine!

Technorati Tags: ,

Guest clustering in a Virtual Machine for SQL Server has been a hotly requested feature for quite some time, and as of today it is officially supported! See the below links from Bob Ward for the exact details. Basically the guest OS has to be Windows 2008 or higher, SQL Server has to be version 2005 or higher, everything must pass the cluster validation tests, and the virtualization software has to be supported for SQL Server (for most of us that means Hyper-V and some versions of VMWare). So test it out and tell us your implementation stories!

SQL Server Support Policy for Failover Clustering and Virtualization gets an update…

Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment

Introduction to Policy Based Management

Thanks to Lara Rubbelke for teaching me about PBM! See the attached PBM.zip for a full slide deck and demos of the “on change” functionality. 

 

Install

•       Get updated policies from the feature pack item “Microsoft SQL Server 2008 Policies”

•       In Management Studio expand your instance then Management.Policy Management.Policies.Import Policy

•       Import from C:Program FilesMicrosoft SQL Server100ToolsPoliciesDatabaseEngine1033

 

Policy-Based Management

•       Combines prior features such as

−     Agent for schedules and alerts

−     DDL triggers as event handlers

−     Best Practices Analyzer

−     Surface Area Configuration Tool

 

PBM Components

•       Facets

−     Groupings of properties that encapsulate a target such as Surface Area Configuration or Table

•       Conditions

−     States such as true/false or on/off for given settings

−     Almost anything that can be verified programmatically

•       Policies

−     Verification of a condition and the required state for a defined target(s)

 

PBM – Facets

•       Contains properties that can have conditions set

•       Example: Table facet has properties such as Name, HasClusteredIndex, IsPartitioned, CreateDate, etc.

•       Cannot be modified or added

•       Are not executed directly

 

PBM – Conditions

•       Are set for facet properties

•       Can be tested programmatically

•       Can be limited to targets such as certain databases or objects

•       Example: For the Table facet you can set @HasClusteredIndex = True

            AND @Owner = dbo

 

PBM – Policies

•       Applies a check condition against targets such as “every table”

•       Can be on demand or scheduled

−     Scheduling is through SQL Agent jobs

−     On demand has the option of “apply” for some conditions to correct exceptions

•       Can be exported then imported to other servers

•       Or execute against a group of servers in SSMS

 

PBM – “On Change ” Enforcement

•       Not available for all conditions

•       On Change – Log Only

−     Allows a change away from the policy to occur but logs the change to SQL Server error log and Windows application log

•       On Change – Prevent

−     Prevents changes from occurring if they are against policy

−     Enforced through DDL triggers

 

Permissions

•       To set/change policies, add users to role PolicyAdministratorRole in msdb – note that this an attack vector as a possible elevation of privilege

•       On Schedule evaluation mode uses SQL Server agent jobs owned by SA

 

References

•       List of evaluation modes for each facet

•       Evaluating Policies On Demand Through PowerShell

•       Administering Servers by Using Policy-Based Management

•       Take Control of the Enterprise: Effective Solutions for Governing your Environment With Policy Based Management

 

PBM.zip

Previous blogs on SQLCommunity.com

Connect.Microsoft.com

By Cindy Gross on Monday, March 09, 2009 12:15:56 AM

Want to provide feedback on any Microsoft product? Want access to the latest CTPs (sometimes known as betas)?

 

Create an account on http://connect.microsoft.com/ and you can file bugs or wishes and vote on what other people have submitted. You can also find CTPs and evaluation editions of various products. For example, to get the CTP of SQL Server 2008 SP1 go to http://connect.microsoft.com/SQLServer.  From the same page you can access the SQL Server feedback section as well as the SQL Server discussion forums. Please vote on your favorite bugs/requests/wishes as this increases the chance they will change for a future version of SQL Server.

SPNs: What They Are and How SQL Server 2000/2005 Uses Them for Kerberos Authentication

By Cindy Gross on Sunday, March 08, 2009 10:59:50 PM

SPNs: What They Are and How SQL Server 2000/2005 Uses Them

Agenda

·         Overview

·         Vocabulary

·         When SQL Server Uses an SPN

·         What a valid SQL Server SPN Looks Like

·         Define a Valid SQL or OLAP SPN

·         How to Check an SPN

·         SPN Ownership

·         Prerequisites

·         How to Set an SPN

·         Troubleshooting

·         SQL 2008

·         References

How to open a file in SSMS without opening a new window

By Cindy Gross on Wednesday, January 28, 2009 9:26:51 PM

When you choose to open a file in SQL Server Management Studio (SSMS) it opens a new query window. Sometimes you would prefer to open the file in an existing window to add it to other code or to avoid entering the connection information again.

Disk Alignment for SQL Server

By Cindy Gross on Wednesday, January 28, 2009 9:25:40 PM

This is the best description I’ve seen of how to do disk alignment. It’s not really specific to SQL Server, it applies to any Windows server.

SQL Server 2005 SP3 + SP3 CU1 are available!

By Cindy Gross on Monday, December 15, 2008 6:52:49 PM

SQL Server 2005 service pack 3 (SP3) is now available! There’s also a new version of the feature pack and of BOL.

·         SP3 itself contains all fixes from SP2 based CU1-CU9 but it does not include most CU10 or CU11 fixes due to the timing of when the CTP (beta) of SP3 started. This pure SP3 version is 9.00.4035. If you want to have al

New book from Kalen Delaney!

By Cindy Gross on Wednesday, October 22, 2008 12:48:10 PM

Kalen’s new book is ready for pre-ordering!
Microsoft® SQL Server® 2008 Internals

How to change the collation of a SQL Server 2005 database

By Cindy Gross on Wednesday, September 10, 2008 9:03:40 PM

Sometimes you need to change the collation of a database after you have created it and populated it with objects and data. So how do you change the collation?

SQL Injection attacks

By Cindy Gross on Tuesday, June 24, 2008 5:09:22 PM

This year SQL injection attacks are being stepped up and even automated against SQL Server. While SQL injection attacks can occur against any DBMS, my blog will only address SQL Server.

How to estimate disk space needed for SQL Server

By Cindy Gross on Tuesday, June 24, 2008 8:44:58 AM

So how do you estimate the amount of disk space needed for a server that runs SQL Server? My assumption in answering this is that you already know approximately how big the data/indexes/logs will be but need to know how much total disk space to request on the server. The information below is for SQL Server itself, not for Analysis Services or Reporting Services. My answer is really a list of questions, because once you answer the questions for your environment you’ll have a good idea of how much space you need. This is an art, not a science, so there is no one right answer.

T-SQL Debugger

By Cindy Gross on Monday, June 23, 2008 4:18:30 PM

Many people have asked where the TSQL debugger is for SQL Server 2005.

SQL Server 2008 RC0

By Cindy Gross on Thursday, June 12, 2008 11:09:45 AM

SQL Server 2008 release candidate 0 (RC0) is now available for public download. Use the links below to download SQL Server, Books Online, the Feature Pack, and more.

Where to find SQL Server hotfixes, service packs, and cumulative updates

By Cindy Gross on Tuesday, May 13, 2008 10:43:06 AM

Finding out which build is the latest or what fixes are included in a particular build can be difficult. These links should make that job easier.

How to test connectivity from a UDL

By Cindy Gross on Wednesday, April 30, 2008 2:31:16 PM

A Universal Data Link (UDL) can be very useful in troubleshooting SQL Server and Analysis Services connectivity issues.

SQL Server Memory – Lock Pages in Memory

By Cindy Gross on Monday, April 28, 2008 6:53:33 AM

Microsoft has recently revised the documentation on the below message due to our recent history with troubleshooting the warning:
A significant part of sql server process memory has been paged out.

SQL Server 2005 Service Broker References

By Cindy Gross on Monday, April 14, 2008 12:04:41 PM

Have you heard about the new Service Broker functionality in SQL Server but don’t know where to start with installing, understanding, or troubleshooting it? These links will help you get started.

 

Should new installations of SQL Server be on 64bit or 32bit?

By Cindy Gross on Wednesday, April 09, 2008 8:03:11 AM

The switch from 32-bit to 64-bit systems can seem overwhelming. I have summarized the major changes and differences to help make your transition a bit smoother.

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

 

%d bloggers like this: