Cindy Gross | Befriending Dragons

Tag: sql server

  • 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

     

  • 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.