MS SQL Server: Multiple Instances

Consolidation PracticesAlexander Prusakov

Senior MS SQL Server DBAMay, 2010

Sr. MS SQL Server DBA (consultant) in one of the biggest financial institution in the world

20+ years of IT experience (business users, ERPs, databases and applications support, software development, data warehousing, etc.)

15+ years of experience with MS SQL Server

MBA, MCITP: Database Administrator 2005/2008

Industries: banking/finance, energy, education, health care, manufacturing, IT consulting.

Speaker’s Background:

Why; How; What to expect and how it

works; Summary; Q and A.

Agenda:

Consolidation refers to the merger from multiple computer sources into single.

Virtualization refers to the abstraction of computer resources.

Multi-instancing refers to the utilization of the same computer sources by multiple independent SQL Servers

Terms

Dedicated service

Dedicated service

Multi-instancing

Virtualization

Virtualization

Multi-instancing

Unexpected need in new SQL Server without budget for a hardware;

Buzz about virtualization; Enterprise environment; Curiosity;

How did I step in:

Reduce hardware cost Reduce pollution (carbon

footprint) Reduce labor/support cost Increase resources utilization Look nice in other’s eyes

Why to consolidate:

Actually it might not (MSRP pre-configured

estimated):› Enterprise level server: 16 sockets,

196GB (16P) about$175,000.00;› HP ProLiant DL785G5 8380 -128GB (8P)$34,999.00;› HP ProLiant DL380G6 E5504-4GB (1P)$ 2,289.00 x 8 = $ 18,312.00;$ 2,289.00 x 16 = $ 36,624.00;

Reduce hardware cost:

Space: 2U x 8 = 16U vs. 7U Power: 460x8 = 3,680 vs. 1,300 W Cooling cost: vary Reduced carbon footprint (including

manufacturing and usage) Ability to add physical CPUs and/or

memory

Real Benefits:

Cost of annual licensing;› OS – 1 to 8;› SQL Server: 8 x 5,999.00 =

47,992.00 8 x 24,999.00 =

199,992.00 Load performance improvement; Cost of SysAdmin support(1 vs. 8); Cost of DBA time (patch 1 vs. 8); Cost of real estate and other

Reduce support cost:

Data compression; Resource governor; Table partitioning; Hot-add CPU and memory; Policy-based management; Data collector; etc.

Extras bonus from Enterprise version:

Total workload throughput on the system versus total number

of databases.

http://www.dell.com/downloads/global/solutions/ServerConsolidation1.doc

Workload throughput on each database versus the number of

databases on the system

http://www.dell.com/downloads/global/solutions/ServerConsolidation1.doc

Effective memory for procedure cache in different configurations

http://www.dell.com/downloads/global/solutions/ServerConsolidation1.doc

Ability to re-distribute CPUs and memory between instances;

Parallel processing (especially beneficial in non-business hours);

Increase resource utilization:

Now you look GREAT!Or may be not

Multi-instancing vs Virtualization

Feature:Multi-

instancingVirtualization

All eggs in one basket Yes May beUse of many low-level servers as one

No Yes

Security and workload isolation

Not really Yes

System administration saving

Yes Sometimes

DBA time saving Yes NoDeployment time saving No Yes

Multi-instancing vs Virtualization

Feature:Multi-

instancingVirtualization

Patching time saving

OS level Yes Kind ofInstance level

Yes No

Overhead*

CPU No 10%IO No 6-8%

MemoryMay be some for

300 MB for the hypervisor;32 MB for the first GB

Multi-instancing vs Virtualization

Feature:Multi-

instancingVirtualization

Overhead*Memory

OS and related processes

of RAM on each VM;8 MB for every additional GB of RAM on each VM

Network latency

No less than 1 ms

Hardware limitations Server limit64GB limit per VM2 TB limit per Host

* http://msdn.microsoft.com/en-us/library/cc768536(BTS.10).aspx

Install multiple instances; Create multiple TempDBs; Limit CPUs and memory per

instance; Leave enough memory for OS; Monitor performance; Test performance before move to

production; Know your IO system; Set up thresholds;

How:

Model CPU Total Memory

IBM 3850 M2 / x3950 M28 Quad Core

66,583,112

Microsoft Performance Dashboard; Quest Foglight software; Good DBA;

What is not that much:

What is useful:

Single Instance Reports; Profiler; Sysadmins.

Usefulness:

And NOT:

But can be better:

Steady but needs a lot of patience from DBA;

Business has to follow rules; Adds extra work to manage and

keep records on DBs; Harder tune performance; Higher cost of error (including

planning).

How it works :

SQL Server Engines for users are separated on different instances;

SSAS and SSRS are separated from database engines;

Each instance is used by databases of one business unit;

Application has no elevated rights on any shared server;

Have a big boss on your side!

How:

Push back from development team on:› CPU utilization;› IO utilization (IOPS);› Database schema optimization;

› Query tuning;

What to expect:

Push back from business:› DBA has to be involved;› One instance down – all business units might be down;

› IT cost saving <> business cost;

› Is there a shortcut?;

What to expect:

Unexpected errors; A lot of work to build

monitoring and administration tools;

Fun, fun, a lot of fun

What to expect:

Bigger the farm – better savings;

Bigger the farm – bigger the headache;

Not that scary if design and implementation has been done carefully.

Summary:

Internet – find your way, do it yourself

Microsoft – excellent documentation, big community. For example:

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

Hardware Manufactures – hard to find the truth

Consultants – result may be vary

Where to look for more:

Slides can be found on:› http://

indiana.sqlpass.org/Default.aspx?tabid=2742

Email: a_prusakov@yahoo.com

Questions & Open Discussion

Thank You!