Database Mirroring - Operating Modes

Database Mirroring Operating Modes




We can configure database mirroring for three different operating modes:

High-Safety Mode Or High Availability (synchronous + Automatic Fail Over + Witness Server)

High-Safety Mode OR High Protection (synchronous + Manual Fail Over)

And

High Performance (Asynchronous)


The operating mode governs the way transactions are transferred between the principal and the mirror databases as well as

the failover processes that are available in the database mirroring session.





High Availability Operating Mode



Database mirroring’s High Availability operating mode provides durable, synchronous

transfer between the principal and mirror databases in addition to automatic

failure detection and failover.



SQL Server first writes all transactions into memory buffers within the SQL Server memory

space. The system writes out these memory buffers to the transaction log and then

flushes the log to the data files.



When SQL Server writes the transaction to the transaction log, the system triggers database mirroring to begin transferring the transaction log rows to the mirror. The transaction rows continue to flow to the mirror. When the application issues a commit for the transaction, the transaction is first committed on the mirror database.



An acknowledgement of the commit is sent back to the principal, which then allows the commit to be issued. After the commit is issued on the principal, the acknowledgment is sent back to the application, allowing it to continue processing.



This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.



High Availability operating mode requires a witness server along with the principal and mirror databases for database mirroring to automatically detect a failure at the principal and fail over to the mirror.



A quorum is necessary to prevent the database from being served on more than one instance within the database mirroring session.

If the principal were to fail, and the mirror could not connect to the witness, it would be impossible to reach a quorum, and SQL Server would then not promote the mirror to the principal.





High Performance Operating Mode



Database mirroring’s High Performance operating mode uses a principal and a mirror

database but does not need a witness server. This operating mode provides a warm

standby configuration that does not support automatic failure detection or failover.



High Performance operating mode does not automatically fail over because the application’s

transactions are sent to the mirror asynchronously. Transactions are committed

to the principal database and acknowledged to the application.



A separate process constantly sends those transactions to the mirror, which introduces latency into the

process. This latency prevents a database mirroring session from automatically failing

over because the process cannot guarantee that SQL Server has received all transactions

at the mirror when a failure occurs.





High Protection Operating Mode



Database mirroring’s High Protection operating mode is the same as High Availability

operating mode, except that you do not configure a witness server.



SQL Server transfers transactions synchronously between principal and mirror, but because a two-outof-three quorum cannot be achieved without a witness, failover is manual.



If the principal fails in High Protection operating mode, you must manually promote the mirror to serve the database.

An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.

Work Around:-




Go to the Binaries Location,

>> Go to D:\SQL Server\SQL-2005\Servers\Setup
>> Search sqlncli.msi
>> Double click choose Repair
>> Once Done Try to install, It will succed.


*This might work, only when you installed and uninstalled couple of time with different sql servers editions

Solution:-

If it failed to work as mentioned above

Please visit http://support.microsoft.com/kb/929667
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/16fd90cf-0dbc-4012-9970-6ca29b6f4aef

Upgrading to SQL Server 2012

http://blogs.extremeexperts.com/2012/05/17/upgrading-to-sql-server-2012/
By Vinod Kumar

SSRS Scripting Tool

 Download Here : 


http://weblogs.asp.net/akjoshi/archive/2009/01/29/using-rs-scripter-to-create-deployment-script-for-reporting-services.aspx

Data Source Updation  can be done using batch file provided by this tool.

Package 'Microsoft SQL Management Studio Package' failed to load

"Reinstalling .Net Framework X.X solved the problem" found at:

"http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/75e2f725-338f-41a7-b66b-5fb4be2d339c"

Posted by Duncan Smart on 04/01/2012 at 14:43

I found that renaming the "HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server" registry key solved the issue for me. More info: http://blog.dotsmart.net/2012/01/04/solution-for-package-microsoft-sql-management-studio-package-failed-to-load/ 

 

 

SQL Server Utility Dashboard

The SQL Server Utility dashboard includes the following information:

■ Utility Summary Found in the center of the top row of the Utility Explorer Content
pane, this section is the first place to look. It displays the number of managed instances
of SQL Server and the number of deployed data-tier applications managed by the SQL
Server Utility. Use the Utility Summary section to gain quick insight into the number of
objects being managed by the SQL Server Utility. In Figure 5-6, there are 14 managed instances
and nine deployed data-tier applications displayed in the Utility Summary section.
NOTE After you have reviewed the summary information, it is recommended that
you analyze either the managed instances or deployed data-tier application section
in its entirety to gain a comprehensive understanding of its overall health status. For
example, the first set of the following bullets interpret the health of managed instances.
After managed instances are analyzed and explained, then the health of data-tier
applications is reviewed from beginning to end.

■ Managed Instance Health This section is located in the top-left corner of the Utility
Explorer Content pane and summarizes the health status of all managed instances
of SQL Server in the SQL Server Utility. Health status is illustrated in a pie chart and has
four possible designations:
● Well Utilized The number of managed instances of SQL Server that are not violating
resource utilization policies is displayed.
● Overutilized A SQL Server instance is marked as overutilized if any of the following
conditions are true:

■ CPU resources for the instance of SQL Server are overutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
overutilized.

■ The instance contains data or log files with overutilized storage space.

■ The instance contains data or log files that reside on volumes with overutilized
storage space.
● Underutilized A SQL Server instance is marked as underutilized if it is not
marked as overutilized and any of the following conditions are true:

■ CPU resources allocated to the instance of SQL Server are underutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
underutilized.

■ The instance contains data or log files with underutilized storage space.

■ The instance contains data or log files that reside on volumes with underutilized
storage space.
92 CHAPTER 5 Consolidation and Monitoring
● No Data Available Either data has not been uploaded from a managed instance
or there is a problem with the collection and upload process.
By viewing the Managed Instance Health section, DBAs are able to quickly obtain an
overview of resource utilization across all managed instances within the utility. The
example in Figure 5-6 shows that five managed instances are well utilized, six are overutilized,
none are underutilized, and data is unavailable for three managed instances in
the Managed Instance Health section.

■ Managed Instances With Overutilized Resources This section is found directly
under the Managed Instance Health section. It displays overutilization data for managed
instances of SQL Server based on the following categories:
● Overutilized Instance CPU This represents the number of managed instances
of SQL Server that are violating instance CPU overutilization policies.
● Overutilized Database Files This represents the number of managed instances
of SQL Server with database files that are violating file space overutilization policies.
● Overutilized Storage Volumes This represents the number of managed instances
of SQL Server with database files on storage volumes that are violating file
space overutilization policies.
● Overutilized Computer CPU This represents the number of managed instances
of SQL Server running on computers that are violating computer CPU overutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.

■ Managed Instances With Underutilized Resources This section is located under
the Managed Instances With Overutilized Resources section and displays underutilization
data for managed instances of SQL Server based on the following categories:
● Underutilized Instance CPU This represents the number of managed instances
of SQL Server that are violating instance CPU underutilization policies.
● Underutilized Database Files This represents the number of managed instances
of SQL Server with database files that are violating volume space underutilization
policies.
● Underutilized Storage Volumes This represents the number of managed
instances of SQL Server with database files on storage volumes that are violating file
space underutilization policies.
● Underutilized Computer CPU This represents the number of managed instances
of SQL Server running on computers that are violating computer CPU underutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.
Using the SQL Server Utility Dashboard CHAPTER 5 93

■ Data-Tier Application Health This section is located in the top-right corner of the
Utility Explorer Content pane. Health status is illustrated in a pie chart and has four
possible designations:
● Well Utilized The number of deployed data-tier applications that are not violating
resource utilization policies is displayed.
● Overutilized The number of deployed data-tier applications that are violating
resource overutilization policies is displayed. A deployed data-tier application is
marked as overutilized if any of the following conditions are true:

■ CPU resources for the deployed data-tier application are overutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
overutilized.

■ Storage volumes associated with the deployed data-tier application are
overutilized.

■ The deployed data-tier application contains data or log files that reside on volumes
with overutilized storage space.
● Underutilized The number of deployed data-tier applications that are violating
resource underutilization policies is displayed. A deployed data-tier application is
marked as underutilized if any of the following conditions are true:

■ CPU resources for the deployed data-tier application are underutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
underutilized.

■ Storage volumes associated with the deployed data-tier application are
underutilized.

■ The deployed data-tier application contains data or log files that reside on
volumes with underutilized storage space.
● No Data Available Either data affiliated with deployed data-tier applications has
not been uploaded to the Utility Control Point or there is a problem with the collection
and upload process.
By viewing the Data-Tier Application Health section, DBAs can quickly obtain a holistic
view of resource utilization for all deployed data-tier applications managed by the SQL
Server Utility. In Figure 5-6, there are seven well-utilized and two overutilized data-tier
applications.

■ Data-Tier Applications With Overutilized Resources This section is found
directly under the Data-Tier Application Health section. It displays overutilization data
for deployed data-tier applications based on the following categories:
● Overutilized Data-Tier Application CPU This represents the number of
deployed data-tier applications that are violating data-tier application CPU
overutilization policies.
94 CHAPTER 5 Consolidation and Monitoring
● Overutilized Database Files This represents the number of deployed data-tier
applications with database files that are violating file space overutilization policies.
● Overutilized Storage Volumes This represents the number of deployed datatier
applications with database files on storage volumes that are violating file space
overutilization policies.
● Overutilized Computer CPU This represents the number of deployed data-tier
applications running on computers that are violating computer CPU overutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.

■ Data-Tier Applications With Underutilized Resources This section is located
directly under the Data-Tier Applications With Overutilized Resources section. This
section displays underutilization data of individual instances based on the following
categories:
● Underutilized Data-Tier Application CPU This represents the number of
deployed data-tier applications that are violating data-tier application CPU underutilization
policies.
● Underutilized Database Files This represents the number of deployed data-tier
applications with database files that are violating file space underutilization policies.
● Underutilized Storage Volumes This represents the number of deployed datatier
applications with database files on storage volumes that are violating file space
underutilization policies.
● Underutilized Computer CPU This represents the number of deployed datatier
applications running on computers that are violating computer CPU underutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.

■ Utility Storage Utilization History Located at the bottom-left corner of the Utility
Explorer Content pane, this section uses a time graph to display the storage utilization
history for the amount of storage the SQL Server Utility is consuming in gigabytes.
By using the buttons under the Interval heading , you can view data in the graph by
the following intervals:
● 1 Day Displays data in 15-minute intervals
● 1 Week Displays data in one-day intervals
● 1 Month Displays data in one-week intervals
● 1 Year Displays data in one-month intervals

■ Utility Storage Utilization The bottom-right corner shows a pie chart that displays
the amount of space used and the amount of free space available on the volume hosting
the SQL Server Utility. It is worth noting that the data is refreshed every 15 minutes.
Using the Managed Instances Viewpoint CHAPTER 5 95
This section explained how to obtain summary information for all managed instances of
SQL Server. DBAs seeking more information might be interested in the Managed Instances
node in the tree view of Utility Explorer. This node helps database administers gain deeper
knowledge of health status and resource utilization data for each managed instances of SQL
Server.

Enhancements to High Availability with Windows Server 2008 R2

■ Hot add CPU and memory When using SQL Server 2008 R2 in conjunction
with Windows Server 2008 R2, database administrators can upgrade hardware
online by dynamically adding processors and memory to a system that supports
dynamic hardware partitioning. This is a very convenient feature for organizations
that cannot endure downtime for SQL Server systems running in mission-critical
environments.

■ Failover clustering Greater high availability is achievable for SQL Server R2 with
failover clustering on Windows Server 2008 R2. Windows Server 2008 R2 enhances
the failover cluster installation experience by increasing the number of validation
tests within the Cluster Validation Wizard. Moreover, Windows Server 2008 R2
introduces a Best Practices Analyzer tool to help database administrators reduce best
practice violations. Similar to its predecessor, Windows Server 2008 R2 continues to
supports up to 16 nodes within a failover cluster and organizations can also protect
their applications from site failures with SQL Server multi-site failover cluster support
by using stretched VLANs built on Windows Server support for multi-site clusters.
64 CHAPTER 4 High Availability and Virtualization Enhancements

■ Windows Server 2008 R2 Hyper-V The Hyper-V virtualization technology improvements
in Windows Server 2008 R2 were the most sought-after and anticipated
enhancements for Windows Server 2008 R2. It is now possible to virtualize heavy SQL
Server workloads because Windows Server 2008 R2 scales far beyond its predecessors.
In addition, database administrators can achieve increased virtualization availability by
leveraging new technologies, such as Clustered Shared Volumes (CSV) and Live Migration,
both of which are included in Windows Server 2008 R2. Guest clustering with SQL
Server 2008 R2 in Windows Server 2008 R2 Hyper-V is also supported.

■ Live Migration and Hyper-V By leveraging Live Migration and CSV—two new
technologies included with Hyper-V and failover clustering on Windows Server 2008
R2—it is possible to move virtual machines between Hyper-V hosts within a failover
cluster without downtime. It is worth noting that CSV and Live Migration are independent
technologies; CSV is not required for Live Migration.

■ Cluster Shared Volumes (CSV) CSV enables multiple Windows servers running
Hyper-V to access Storage Area Network (SAN) storage using a single consistent
namespace for all volumes on all hosts. This provides the foundation for Live Migration
and allows for the movement of virtual machines between Hyper-V hosts.

■ Dynamic virtual machine (VM) storage It is possible to add or remove virtual
hard disk (VHD) files and pass-through disks while a VM is running. Support for hot
plugging and hot removal of storage is based on Hyper-V. This is very handy when you
are working with dynamic SQL Server 2008 R2 storage workloads, which are continuously
evolving.

■ Second Level Address Translation (SLAT) Enhanced processor support and
memory management can be achieved with SLAT, which is a new feature supported
with Hyper-V in Windows Server 2008 R2. SLAT leverages Intel Virtualization Technology
(VT) Extended Page Tables (EPT) and AMD-V Rapid Virtualization Indexing (RVI)
technology in an effort to reduce the overhead incurred during mapping of a guest
virtual address to a physical address for virtual machines. This significantly reduces
hypervisor CPU time and saves memory for each VM, allowing the physical computer
to do more work while utilizing fewer system resources.