Transcript Consolidate Databases using SQL Server Storyboard Sample

Consolidate Databases using Microsoft SQL
Server
From many to few; learn how to use SQL Server to reduce your database footprint.
Info-Tech Research Group
‹#›
Introduction
Although database consolidation has traditionally been thought of as a risky practice, recent
improvements to SQL Server make it a reliable platform for consolidation. Organizations that are
not yet consolidating are wasting resources.
This Research Is Designed For:
This Research Will Help You:
 Organizations of all sizes that are considering the costs
 Understand the benefits of database consolidation,
and benefits of database consolidation onto SQL Server.
 Organizations looking to advance their database
consolidation strategy into production environments.
 CIOs and data center managers looking to implement a
comprehensive, phased consolidation process and to
avoid common consolidation pitfalls.
from cost reduction to performance benefits.
 Select the right consolidation model based on your
organization’s needs and requirements.
 Dive deeper into major considerations for each stage
of consolidation; including planning, piloting, testing,
upgrades, and decommissioning of servers.
Advice provided in this solution set is specific to consolidation on SQL Server. Although SQL Server is
the most popular platform for consolidation, consolidation on another platform may be a more viable
solution for your needs. If you are looking to consolidate on Oracle, please refer to Info-Tech’s solution
set Assess Oracle’s Role in the Enterprise Database Strategy for Oracle-specific advice.
Info-Tech Research Group
‹#›
Executive Summary
Understand the value of consolidation
• The most common reason for consolidation is reducing TCO; however, there are plenty of other benefits to consolidation;
including more centralized management, reduced data center space requirements, and improved performance.
• Considering consolidation on SQL Server? You are not alone:
o 34% of organizations surveyed have either consolidated or are in the process of consolidation.
o Another 39% are planning to consolidate in the next 18 months.
Create the consolidation strategy
• Consolidation strategy is two-fold: Select your consolidation model based on criteria such as security, manageability, and
high availability and choose your SQL Server edition.
• Consolidation models include database, instance, and virtualization.
o Info-Tech cautions against using the cloud for hosting databases. Revisit this option in 24-36 months.
• Organizations choosing virtualization have the option of consolidating, stabilizing, and then virtualizing or taking a shortcut
and jumping into the virtualization project right away. Info-Tech recommends the former approach.
• Organizations have the option of consolidating on SQL Server 2008R2 or the recently released 2012 edition.
Implement a phased consolidation process
• The planning stage is key to a success process; thoroughly profile candidates for consolidation and prepare for
consolidation-related risks to achieve higher satisfaction with database performance, availability, uptime, and security.
• Follow Info-Tech’s detailed 5-step implementation model to ensure a successful consolidation process.
Info-Tech Research Group
‹#›
Understand the value of consolidation
What’s in this Section:
• Understand the major drivers of consolidation.
• Recognize the features of recent SQL Server versions that
facilitate stable consolidation.
• Consider whether a phased or all-at-once approach is
appropriate.
• Start thinking about best practices and common pitfalls of
Sections:
Understand the value of
consolidation
Create the consolidation
strategy
Implement a phased
consolidation process
consolidation with Info-Tech’s case studies.
Info-Tech Research Group
‹#›
Organizations are wasting significant resources by running
under-utilized servers
Historically, hosting one SQL Server instance per server was the safest
approach. Today, it only leads to an unnecessary number of servers.
• Past problems isolating SQL Server database instances
made hosting multiple databases on the same server a
risky decision. Corruption in one instance often bled over
into other instances. As a result, organizations often
added a dedicated server for each application they were
running, leading to database sprawl.
73% of organizations have consolidated
already or plan to do so in the near
future. If you choose to consolidate, you
aren’t alone!
• This resulted in unnecessary licensing costs, higher
support and facility costs, and under-utilizing the existing
architecture – including servers that often lie completely
idle.
• However, recent improvements in database technologies;
including the ability to easily isolate instances, mean that
organizations can safely consolidate on SQL Server
as long as they follow a structured approach to
ensure the process is done correctly.
N = 82. Source: InfoTech Research Group
Info-Tech Research Group
‹#›
Consolidation benefits can be achieved in most situations
regardless of organization size or location
Consolidation is not just for the large. In fact, small
organizations are most likely to be planning a consolidation in
the near future and medium organizations are most likely to
have already consolidated.
Adoption levels are not significantly
affected by an organization’s
location.
Info-Tech Research Group, n=47.
It's a matter of degree. Any consolidation is better than it was before. Any time you can retire a dedicated server
(whether end-of-life or not) is a win for the IT Department. Consolidation cuts down on power usage, system administrator
effort, hardware support costs, etc.
- IT manager, Healthcare Industry
Info-Tech Research Group
‹#›
Consolidation was once a risky practice, but with recent SQL
Server improvements, this is no longer the case
While pre-2008 versions of SQL Server may have been inappropriate for consolidation, 2008R2 and
2012 are suitable platforms for doing so. Use SQL Server 2008 R2 as the bare minimum for consolidation. If you
are running an earlier version, consider consolidation as you upgrade to achieve even greater savings.
Recent SQL releases contain the following improvements that make it a reliable solution for consolidation:
Storage
Security
High Availability
• Data compression enables more
effective, lower cost storage with
improved performance for large I/O
workloads. Compression also
reduces the cost of business
continuity.
• Backup compression reduces the
size of the backup and the time to
backup. Disk size is often reduced
by 20-60%.
Three levels of isolation:
• Database: access to the database
is controlled on a user-by-user
basis.
•Server: access to the instance is
controlled by using logins.
•Operating System: access to the
OS is controlled by using Windows
or Active Directory accounts.
• Failover clustering offers either
automatic or manual clustering with
greater redundancy and control.
Improvements make it easier to
create and manage clusters.
• Database mirroring ensures the
database remains available at all
times. Improvements include log
stream compression, automatic
suspect page recovery, and clients
do not need to be reconfigured to
access the mirrored database during
failover.
Info-Tech Research Group
• Other important features include
transparent data encryption (TDE)
and SQL Server Audit .
‹#›
Even a relatively conservative consolidation ratio, such as 2:1,
will save organizations a lot in the long term
SQL Server leads the market in terms of TCO, but even though each
individual server is relatively inexpensive, the numbers add up quicky
Even the most conservative of organizations are
easily achieving a 2:1 or 3:1 consolidation ratio.
The opportunity exists to achieve much higher ratios
as well. This will create savings on licensing costs,
power costs, maintenance costs, hardware costs,
staff costs, and training costs.
If your hardware budget allows, you can achieve a
100:1 reduction ratio! I did a theoretical calculation and
purely from a performance capability standpoint, I could
do an entire global consolidation (about 1200 instances)
with only two physical machines.
- Martijn van der Munnik, Technology Consultant
Through consolidation and
virtualization of our SQL Server
databases we reduced our licensing
expenses by 75%.
For example: A mid-size organization is currently running 19
instances of SQL Server. Estimating that each instance of SQL
Server has a 6-year TCO of $78 thousand, then the total cost of their
set-up is is $1.4 million.
- Technology Consultant
If the organization was able to achieve even a 2:1 reduction ratio, it
would be saving over $741 thousand dollars in the same 6 years.
6-Year TCO: 19 X $78K = $1.4M
Info-Tech Research Group
6-year TCO: $659K
Savings of $741K over 6 years
‹#›
There are benefits to consolidation beyond simple cost savings
Consolidation is an effective way to achieve
the following organizational goals:
• Creating a more effective use of space and
hardware in the data center, as well as
decreasing the environmental impact due to
cooling and power usage.
Improved management and standardization
• Leading to more efficient management of the
databases.
• Increasing the ability to implement standard
processes and procedures.
• Increasing the horizontal integration of business
units.
Streamlined technical skills
• Decreasing the need for multiple skill sets.
• Streamlined knowledge requirements for end
users.
Info-Tech Research Group
High
Satisfaction
with Databases
Reduced data center space requirements
Consolidation leads to higher organizational
satisfaction with database infrastructures.
Low
No Database
Consolidation
Looked Into
Database
Consolidation
Planning
Database
Consolidation
In Process of
Database
Consolidation
Database
Consolidated
Status of Database Consolidation
N=67
Organizations that consolidated their databases
reported higher satisfaction with the following:
 Performance
 Uptime
 Availability
 Security
‹#›
Although it is possible to roll out the entire consolidation
project at once, a phased approach is more successful
Phased Approach
All-At-Once Approach
Organizations have two options for
their consolidation project:
1) All-at-once approach: Organizations identify all the
databases they wish to consolidate and complete the
process in one round. Following the consolidation, they
deal with any points of failure.
2) Phased approach: Organizations consolidate a small
number of non-critical instances first, stabilize and monitor
them, deal with any failure points, and then extend their
consolidation to more complex, mission-critical databases.
Info-Tech recommends taking a phased
approach, if possible. This approach may
take longer in the beginning, but will help
minimize long-term problems and streamline
the process for both IT and the business.
Organizations that choose an all-at-once
approach risk potential failure points affecting
all of their databases, including mission-critical
ones.
Info-Tech Research Group
‹#›
Keep the business informed about changes to business
processes, but don’t let them control the consolidation project
Consolidation doesn’t affect IT alone; make sure the process is transparent to the business as
well. Consolidation does present some risks and the chance that business processes may be affected. Keep business
stakeholders informed about any changes that affect them, and involve them in the testing of end results to ensure the
overall success of your project.
The owner of the process is always IT. The business should act as an advisor rather than a leader.
Process
Role of Business
Role of IT
Planning for
consolidation-related
risks
Approve
Conduct
Gathering Business
Requirements
Provide a description
of user-based needs
Create an inventory of
business requirements
Planning for the pilot
project
Validate the decision
Identify the databases
that will be used during
the pilot
User Acceptance
Testing
Execute
Determine the timelines
and validate the results
Don’t let the business dictate
the consolidation process, the
type of consolidation, or the
database environment that you
set up. Abstract what is
running and what it is
running on.
Learn the business’s needs
and create a solution around
them.
"Typically it is the business that pays for acquiring and installing the servers, and since they pay for them, they don't want to
feel as though they are not getting what they've paid for. You must effectively communicate the benefits of consolidation to
the business stakeholders. They need to be involved in the entire process and once it’s completed, they shouldn't feel like
they've lost anything.“
- Cecil Newton, Director of Technology Services at San Francisco Health Plan
Info-Tech Research Group
‹#›
Info-Tech Research Group Helps IT Professionals To:



Quickly get up to speed
with new technologies

Manage business expectations

Justify IT spending and
prove the value of IT

Train IT staff and effectively
manage an IT department
Make the right technology
purchasing decisions – fast
Deliver critical IT
projects, on time and
within budget
Sign up for free trial membership to get practical
solutions for your IT challenges
“Info-Tech helps me to be proactive instead of reactive –
a cardinal rule in a stable and leading edge IT environment.
•
- ARCS Commercial Mortgage Co., LP
Toll Free: 1-888-670-8889
Info-Tech Research Group
www.infotech.com
‹#›