High Availability
SQL 2012 is a big
step forward for Microsoft Corporation - company is positioning itself to
be a leader in availability and in
growing area of big data. SQL Server
2012 also provides a rich set of features for optimizing database applications
for your Private Cloud.
In one of the Tech Event,
Microsoft showing the power of SQL Server 2012, in one slide they talking about
availability, see the image here.
Do you
know – ‘What is required 9s of
Availability’? - Required 9s
is a term to represent SQL Server up time with SQL Server 2012. The ‘9s’ means seven times of 9 like 99.99999 is the server uptime. Everyone
wants server uptime 99.9999. We can achieve this figure by using SQL Server
2012 HA (High Availability) with
AlwaysOn feature.
99.99999%
("seven nines") means 3.15 seconds downtime in a year, 0.259
seconds in a month & 0.0605 seconds/week.
For more detail on High Availability and Percentage
calculation of availability please visit the following link:
To see the full presentation visit the link: http://www.slideshare.net/markginnebaugh/microsoft-sql-server-2012-cloud-ready
SQL Server Delivers Required 9s will maximize uptime (using AlwaysOn, Window Server Core Support and Online Operation Enhancements) for mission critical applications, Improved Productivity (Using Integrated
Configuration Wizard, Windows PowerShell Support, AlwaysOn Dashboard, System
Center Monitoring) and help for greater
utilization of hardware (using Active Secondaries, Offload Backups, Fast
Application Failover).
1. AlwaysOn Availability Groups: AlwaysOn (AO) is the new high availability technology from Microsoft in SQL Server 2012.
This feature takes database mirroring to
a whole new level.
With
AlwaysOn, users will be able to fail over multiple databases in groups instead
of individually.
Also,
secondary copies will be readable, and can be used for database backups. The
big win is that your DR (Disaster Recovery) environment no longer needs to sit
idle.
The RTO and RPO are the Recovery Time Objective and the Recovery Point Objective. The RTO
is defined as the amount of time that it takes to get the system back online
after a critical system failure. The RPO
is defined as the amount of data which can be lost while bringing the system
back online after a critical system failure.
Neither
of these are numbers which can be defined by anyone in the IT department. Both of these numbers are numbers which need
to be defined by the business unit. Without properly identifying RTOs and RPOs,
IT professionals risk a huge potential mismatch between what they’re able to
recover during a disaster, and what a business needs to effectively survive a
disaster.
The main
differences between HA and DR: High availability (HA) ensures
that the data is available with as little down time as possible. Disaster
recovery (DR) ensures that down time is as short as possible. Once you have an
accepted HA business plan you must still have a DR plan for your data and
resources. You should always plan on a disaster where you will need to get your
business up and running.
2. Windows Server Core Support: What is Window Server Core? -
It is a lightweight version of
windows having minimum number of components to run infrastructure services
like domains, file and print, and IIS. Core is the GUI-less version of Windows
that uses DOS and PowerShell for user
interaction.
It has a
much lower footprint (50% less memory
and disk space utilization), requires fewer patches (Reduce OS patching by 50-60%, so planned downtime also reduced),
and is more secure than the full install. Starting with SQL 2012, it is
supported for SQL Server.
3. Online Operations Enhancement: Now we can maintain uptime
during maintenance operations using enhanced
online operations this will eliminate planned downtime.
- Re-Indexing of large objects:
Indexes that include columns of large object (LOB) data—varchar(max),
nvarchar(max), varbinary(max), or XML data—can now be built, rebuilt, or
dropped online.
If you try to Re-Indexing of large objects In
SQL Server 2008/R2, it will throw the following error:
Msg 2725, Level 16,
State 2, Line 1
An online operation cannot be
performed for index ‘IX_...’ because the index contains column ‘Col Name’ of
data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml,
or large CLR type. For a non-clustered index, the column could be an include
column of the index. For a clustered index, the column could be any column of
the table. If DROP_EXISTING is used, the column could be part of a new or old
index. The operation must be performed
offline.
- Adding non-null columns: Prior
to SQL Server 2012 when we used to add a new non-NULLable column with default values to an existing table a
size-of data operation occurs: every
row in the table is updated to add the default value of the new
column.
For small
tables this is insignificant, but for large tables this can be problematic to
completely the operation because it can take much time and a lock occurs for
long time will cause several problems.
But
starting with SQL Server 2012 the operation is, in most cases, instantaneous: only the table metadata is changed, no
rows are being updated.
Because
column with default value available in the table metadata, this is how SQL Server 2012 knows how to show a
value for newly added column, even though physically missing on the page.
With this
‘magic’ in place the ALTER TABLE will no longer have to update every row in the
table and the operation is fast, metadata-only, no matter the number of rows in
the table. This new behavior occurs automatically, no special syntax or setting is required, and engine will simply do
the right thing.
There is
no penalty from having a missing value in a row. The ‘missing’ value can be
queried, updated, indexed, exactly as if the update during ALTER TABLE really
occurred. There is no measurable performance penalty from having a default
value.
What happens when we update a
row? The ‘default’
value is pushed into the row, even if the column was not modified. Only rows
existing in the table at the time of running ALTER TABLE statement will have
missing ‘default’ values. By contrast the default constraint can be dropped or
modified and new rows inserted after the ALTER TABLE will always have a value
present in row for the new column.
Important: Not all data types and default
values can be added online. BLOB values like varchar(max), nvarchar(max),
varbinary(max) and XML cannot be added online.
4. Multiple Secondaries: One
advantage that AlwaysOn has over
mirroring is that it allows multiple usable
secondaries of the database. SQL Server 2012 has been released with a new
license model. With SQL Server 2012 AlwaysOn's ability to have multiple
secondaries you need to take into account the licensing when you are going to
be implementing multiple secondaries. The license model requires you to license
your Active (Primary) SQL Server in your AlwaysOn Cluster. You are allowed one
Passive (Secondary) server that you do not need to license. If you have more
than one secondary server, you need to license that server whether it is active
or passive.
5. Readable Secondaries: The AlwaysOn Availability Groups active
secondary capabilities include support for read-only access to one or more
secondary replicas (readable secondary replicas). A readable secondary replica
allows read-only access to all its secondary databases. However, readable
secondary databases are not set to read-only. They are dynamic.
A given
secondary database changes as changes on the corresponding primary database are
applied to the secondary database. For a typical secondary replica, the data in
the secondary databases is in near real time.
Furthermore,
full-text indexes are synchronized with the secondary databases. In many
circumstances, data latency between a primary database and the corresponding
secondary database is only a few seconds.
Implementing
read-only access to secondary replicas is useful if your read-only workloads
can tolerate some data latency. Where data latency is unacceptable, consider
running read-only workloads against primary replica.
Note: SQL Server AlwaysOn Active Secondries enable secondary instances to be
utilized for running report queries and backup operations which helps eliminate
idle hardware and improve resource utilization.
6. HA for StreamInsight:
Microsoft StreamInsight provides a
powerful platform for developing and deploying complex event processing (CEP) applications. CEP is a technology for high-throughput, low-latency processing of event streams. Typical event stream
sources include data from manufacturing applications, financial trading
applications, Web analytics, or operational analytics. The StreamInsight stream
processing architecture and the familiar .NET-based development platform enable
developers to quickly implement robust and highly efficient event processing
applications.
The new
StreamInsight capabilities address key customer needs for high availability
management. The next on-premise release provides customers with:
- Resilient
against planned and unplanned downtime with new checkpointing capabilities
- Facilitates
predictive modeling and patter matching with user-defined stream concept
- Improved
monitoring and administration with performance counters
- Easier
development with support for nested event type structures and new LINQ
statements.
7. Database Recovery Advisor: This
feature has introduced significant user
experience enhancements to the ways DBAs can now restore databases using SQL Server Management Studio.
Capabilities include a visual timeline that presents the backup history of the
database and the available points in time to which the user can restore the
database, algorithms to streamline identifying the right sets of backup media
to get the database back to a specific point in time, and page restore dialog
in SSMS to do page-level restores of the database.
8. Enhanced PowerShell Support: Windows
and SQL Server admin should definitely start brushing up on their PowerShell scripting skills.
Microsoft is driving a lot of development effort into instrumenting all of their server-based products with
PowerShell.
SQL 2008 gave DBAs some exposure to it, but there
are many more in cmdlets in SQL 2012.
No comments:
Post a Comment