Designing SQL Server Instances

Practice Labs Module
1 hour 10 minutes

The Configure Additional SQL Server Components module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises: Design a Failover Clustering Solution, Design Database Mirroring, Design a High-Availability Solution Based on Replication...

Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *

Already have an account? Sign In »



The Configure Additional SQL Server Components module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Design a Failover Clustering Solution

  2. Design Database Mirroring

  3. Design a High-Availability Solution Based on Replication

  4. Design a High-Availability Solution Based on Log Shipping

  5. Select High-Availability Technologies Based on Business Requirements

Exercise 1 – Design a failover clustering solution

Cluster resource group

A collection of one or more resources that are managed and monitored as a single unit. Resource groups can be started and stopped independently of other groups (when a resource group is stopped, all resources within the group are stopped). In a server cluster, resource groups are indivisible units that are hosted on one node at any point in time. During failover, resource groups are transferred from one node to another.

Cluster setup considerations

Number of nodes

The number of nodes that can be added to the cluster depends on the edition of the operating system (OS) as well as the edition of SQL Server, with a maximum of 16 nodes using SQL Server 2008 Enterprise Edition running on Windows Server 2008. Failover clustering is only supported in the Enterprise and Standard Editions of SQL Server. If you are using the Standard Edition, you are limited to a 2-node cluster. Since failover clustering is also dependant on the OS, you should be aware of the limitations for each edition of the OS as well. Windows Server 2008 only supports the use of failover clustering in its Enterprise, Datacenter, and Itanium Editions. Windows Server 2008 Enterprise and Datacenter Editions both support a 16-node cluster, while the Itanium edition only supports an 8-node cluster.

Service accounts

When the cluster is first set up, you must specify a user account that is a member of the domain. This is the cluster service account. It is a Windows account, meaning it is a Windows login (Windows user login, specifically) as far as SQL Server is concerned. This Windows account has to be a member of the local Administrators group for the operating system on every server that makes up the cluster. And prior to SQL Server 2008, the Administrators group was automatically added to SQL Server and granted sysadmin level rights, meaning when you installed SQL Server, you didn't have to worry about it.

With respect to SQL Server service accounts, and this include SQL Server Agent, SQL Server Full Text (if on 2005/2008), they need to be Windows accounts from the domain, too. The reason is that the operating system actually starts those services using those accounts and they need to be able to run on every server of the cluster where SQL Server is configured to potentially run. If you don't change these from when you set up SQL Server, then the installation will ensure that these accounts have the correct permissions within SQL Server as well.

Before creating an MSCS cluster, you should create a domain user account under which the Cluster service (a special service responsible for managing all cluster-related activities) will run. You will need this account later on when making the MSCS cluster and joining all your nodes to it.

Exercise 2 – Design Database Mirroring

Whether to use a witness server

For optimum high availability using database mirroring, a witness instance is very important, although not mandatory, assuming you want to give up some of the benefits of database mirroring.

In a typical scenario of database mirroring, in which automatic failover is desired, database mirroring must be configured with the high-safety mode, which requires a witness instance running on a third server (not running on the production or mirror instances). This witness instance enables the mirror server to recognize whether to initiate an automatic failover or not. Unlike the two partners, the witness does not serve the databases being mirrored. The only purpose of the witness instance is to monitor the production and mirrored instances, determining whether or not to initiate a failover. If a witness instance is not used, then automatic failover is not available, and failover must be implemented manually.

Windows Server considerations

Database mirroring is an alternative high-availability solution to failover clustering in SQL Server. Database mirroring supports automatic failover, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering.

You can to implement a Mirroring using three SQL Server Instances, where:

  • Principal Server
  • Mirror Server
  • Witness Server

As Principal Server will be default instance - plabsql01, Mirror and Witness servers you should configurate.

Exercise 3 – Design a High-Availability Solution Based on Replication

Different replication types

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing.

Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites.

Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.

Exercise 4 – Design a High-Availability Solution Based on Log Shipping

Manage instance-level objects

Log shipping it is possible to implement between two databases on the same instance. Log shipping has the responsibility to keep the primary database synchronized with all changes, transactions performed with secondary database. However, it is not recommended because if it loses the instance, you will also miss the primary and standby database. Log shipping can certainly be included in the plan of backup / restore databases, is a very good chance of retention of data from various disaster scenarios that might occur, it in automatically saves a copy of the log in another instance, server.

Log shipping is perform or implemented at the database level between primary database and secondary database. Also, it has a opportunity to configure a monitor server, that shows and verify the health of the log shipping session and if any errors happened it can send a notifications.

Changing roles

After you have failed over a SQL Server log shipping configuration to a secondary server, you can configure your secondary database to act as a primary database. Then, you will be able to swap primary and secondary databases as needed. The first time you want to fail over to the secondary database and make it your new primary database, there is a series of steps you must take. After you have followed these initial steps, you will be able to swap roles between the primary database and the secondary database easily.

Exercise 5 – Select High-Availability Technologies Based on Business Requirements

Failover Clustering

This provides server-level redundancy on a certified Microsoft Windows Cluster Services Configuration. A failover cluster is setup with a minimum of two servers. In the cluster setup, each server is called a node. All the nodes are connected to shared disk resources. Only one node is active at a time for a SQL Server instance and serves all the requests for that instance of SQL Server. When the active node fails, failover takes place and one of the other available nodes will become the active node.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.