GD Bloggers

This is the blog site for Microsoft Global Delivery Communities focused in sharing the technical knowledge about devices, apps and cloud.
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

SQL Consolidation Planning and recommended practices

SQL Consolidation Planning and recommended practices

  • Comments 2
  • Likes

 

What is SQL server consolidation

it can be defined simply as reduce the number of physical SQL servers, by migrating/moving different SQL databases running on different servers into one high performance server machine.

image

Early in the process of a consolidation project, you will create a profile to help identify which applications are good candidates for consolidation. Then you can identify the applications that fit this profile.

Some general traits that make an application a good candidate for consolidation are:

  • low machine resource utilization
  • Moderate performance requirements
  • little active development
  • low maintenance costs.

 

Consolidation strategies

SQL server consolidation can be achieved mainly through one of the below strategies:

  1. Virtualization: using a single physical machine to host multiple virtual machines (VMs) running Microsoft® SQL Server® data management software
  2. Multiple Instances: using a single machine to host multiple SQL Server instances,
  3. Multiple databases: using a single instance of SQL Server to host multiple databases

Each of these strategies has different advantages and disadvantages related to security and compliance requirements, high availability and disaster recovery requirements, resource management benefits, level of consolidation density, and manageability tradeoffs

Consolidation projects are typically started to achieve specific goals such as creating room for new servers or reducing operating expenditure. These goals can be broadly grouped into the following categories:

  • Lack of space in the data center: reducing the physical space required to host applications
  • Reducing costs and improving efficiency: hardware will run closer to capacity, reducing inefficiencies and allowing for fewer machines
  • Standardization and centralization:Place various databases in a centrally managed system, better audit control .
  • IT agility: Moving application into newer hardware, these application can take advantage of the newer technology and improved performance of these machines.
  • Green IT: computers and fewer idle machines result in lower power consumption and a reduced need for cooling

 

Consolidation Strategy Check list

when choosing one of the consolidations strategies each of which has its cons and pros, in the below checklist im trying to list the main concerns to take care of , these concerns can be captured from different views as the following :

  • Security
  • High availability and disaster recovery
  • Resource management
  • Density
  • Manageability

answering the below checklist will help you to decide the best strategy for your environment: 

Category

Check item

Yes

No

Security

Share SQL service account for all applications (dbs)

Database

Instance, VM

Security

Do you need to isolate system admins (e.g. sa)

Instance , VM

Database

Security

Do you need to isolate local windows admin account

VM

Instance, database

Security

Do you seek isolation on SQL binaries' and patching for each application DB ?

Instance, VM

Database

Resource management

Full software & hardware resources isolation , Hard limits on CPU and memory usage set per application

VM, Instance-partially

Database

High availability

Applications (DBs) can be moved to different HW without windows restart or downtime

VM

Instance, database

Resource management

Isolation of tempdb, one tempDb for all applications (dbs)

Database

Instance , VM

Resource management

Isolation of server level objects (credentials, linked servers, msdb, SQL Server Agent jobs, and so on)

VM, Instance

Database

Density

Best performance when same hardware is provided

database

VM, Instance

Manageability

Reduces number of physical servers to maintain

Instance, database

VM

Manageability

Reduces number of Windows installations to maintain

Instance, database

VM

Manageability

Reduces number of SQL Server instances to maintain

database

VM, Instance

Manageability

Are you seeking reduced management overhead and licensing cost

database

VM, Instance

 

Recommendation when planning for SQL consolidation

  • Use the MAP tool to assess the current environment and plan for consolidation. in my next post im going to talk in details how to use the MAP tool and analyze its results, you can download the tool form here.

 

  • Performance considerations
    • If multiple applications are consolidated as databases and these have dependencies on tempdb, I/O bottlenecks on tempdb can cause performance issues, assess your disks IOPS and allocate dedicated physical disks to tempDB.
    • Virtualization: we generally recommend using a fixed size virtual hard disk (VHD) or a pass-through disk because dynamic VHDs can cause additional I/O overhead.
    • Virtualization: Use SLAT processors incase of virtualization , the VMs will perform better especially when increasing number of VMs .
    • Virtualization: If an application has very strict security requirements, it is an ideal candidate for a virtualized approach to consolidation because the virtual machine has almost the same security isolation options as if the application had a dedicated physical host.
    • Virtualization: One virtual processor is recommended to be mapped to one physical processor.
    • Instance-level and database-level consolidation options provide direct access to the consolidated server’s physical hardware, which may help scalability by providing support for hot-add CPU and memory

 

  • SQL Server provides the max server memory and CPU affinity mask settings to set limits on how much memory and how many logical processors the SQL Server instance can use..
  • Database-level consolidation provides the lowest overhead, because all other resources are shared with the other databases on the single instance.
  • Plan for Network bandwidth thoroughly: Another factor to consider when planning for consolidation is the impact on the application’s network and I/O latency, because both the network and storage resources become shared as part of consolidation.

 

  • CPU sizing
    • Newer processors may reduce the need for the application to use as many processors as it previously had.
    • look at all applications that significantly under-utilize CPU, pick the one that utilizes the most processors, and take that number of processors as a base.
    • You should always leave room for peak performance or application usage growth. Targeting approximately 50 percent utilization is a good starting point.

 

  • Density: which is the number of databases that can be placed together in one instance or server, In terms of Performance , database can demonstrate the best throughput and fastest response time when hardware is baselined

Consolidation method

Number of applications

Throughput

Response time

Host system CPU utilization

Baseline (old hardware)

1

100%

100%

6%

Virtualization

24

+0.8%

80%

24%

Instance

24

+0.6%

58%

20%

Database

24

+0.9%

53%

16%

Virtualization

40

+0.6%

95%

45%

Instance

40

+1.1%

73%

37%

Database

40

+1.3%

55%

34%

Table : sample Density results based on throughput (higher is better) and response time (lower is better) across options

 

  • Scalability
    • Its important to check what is the scalability room of current hardware (scale up RAM , CPU , available PCI Network slots , Storage)
  • Security
    • Instance-level consolidation provides an additional layer of protection, because the binaries and the SQL Server logins are separate, but the instances still share the same Windows accounts and operating system configuration. At the instance level, we recommend that you use different service accounts for each instance to reduce security risks
  • High Availability
    • All three approaches can leverage the various high-availability features built into SQL Server such as failover clustering, database mirroring, and replication
    • SQL Server failover clustering is the high availability solution, database-level consolidation may not be the best choice, because failover will occur at the instance level. If you have applications that are consolidated at the database level, these applications will need to rely on health monitoring based on the entire instance failing over. However Virtualization or dedicated hardware may be the best choices in this scenario.

 

Limitations and Boundaries

  • Virtualization Host requires hardware-assisted virtualization support (Intel VT or AMD-V) .
  • Virtualization Host requires hardware data execution prevention (DEP, also called Intel XD bit and AMD NX bit).
  • HW resources in virtualized environment will be allocated to the VM regardless of whether or not they are fully utilized. In addition, the guest operating system of the VM itself will consume some overhead of the allocated resources, and the host operating system will also require an additional allocation of resources although these are generally relatively small.
  • SQL Server is currently limited to a maximum of 50 instances per operating system environment (physical or virtual).
  • Hyper-V has a limit of 64 VMs per node and a SQL Server instance has a limit of 32,767 databases per instance

 

next  blog , how to use MAP tool to assess your environment readiness for SQL consolidation  :

http://blogs.technet.com/b/meamcs/archive/2012/09/24/how-to-use-map-tool-microsoft-assessment-and-planning-toolkit.aspx

 

References: http://msdn.microsoft.com/en-us/library/ee819082.aspx

Comments
  • Good post Issa :)

  • Very nice article.Good information

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment