Clustering | SQL Freelancer Blog

Introducing SIOS high availability for SQL Server 2008/R2 in Azure

By bradyupton in AlwaysON, Azure, Clustering, High Availability, Upgrades/Migrations August 12, 2019 0 Comment

Support for SQL Server 2008 and 2008 R2 ended on July 9, 2019. That means the end of regular security updates. However, if you move those SQL Server instances to Azure or Azure Stack, Microsoft will give you three years of extended security updates at no additional cost. If you’re currently running SQL Server 2008 or 2008 R2 and you are unable to update to a later version of SQL Server, you will want to take advantage of this offer rather than running the risk of facing a future security vulnerability. An unpatched instance of SQL Server could lead to data loss, downtime, or a devastating data breach.

If you have a SQL Server failover cluster instance (FCI) or use hypervisor-based high availability, or other clustering technology on-premises for high availability, you’ll probably need the same in Azure. If you need to migrate to Azure/Azure Stack at end of support, and you require high availability for SQL Server 2008/2008 R2, there’s only one solution recommended by Microsoft: SIOS DataKeeper. SIOS DataKeeper enables clustering in the cloud, including the creation of a SQL Server 2008/2008 R2 failover cluster instance, allowing you to achieve your high availability goals.

Click here to view the rest of this post.

SQL Server Clustering Management inside the command line

By bradyupton in Clustering, High Availability June 21, 2012 0 Comment

There are numerous commands you can run to manage a SQL Cluster from the command line. Below I’ll show you a few of these that include status checks and how to failover.

How to View SQL Clusters

This can be run from your workstation to view all SQL Clusters on the network you are connected to.

cluster /list

View Status of Nodes

You can run this to view the status of all nodes in the cluster.

cluster node 
--or 
cluster node /status

View Status of Cluster Groups

You can run this to view the status for all cluster resource groups.

cluster group 
--or 
cluster group /status

View Status of Cluster Networks

You can run this to view the cluster networks status.

cluster network 
--or 
cluster network /status

Click here to view the rest of this post.

Adding Storage to SQL Server 2008 Cluster

By bradyupton in Clustering, High Availability May 17, 2012 0 Comment

Adding a new storage device using Windows 2008 Failover Clustering has been simplified enormously since the Windows 2003 and older clustering technology. In this post, I’ll show you how to add an extra storage device to your configuration. First thing you will need to do is get your SAN administrator to present a new disk to the cluster. Once the new disk is presented, go to Disk Management, and you should see the new disk unallocated. If the disk displays “Offline” simply right click and choose Online.

Next, right click on the disk and choose “New Simple Volume”. Clustering does not support any of the other volume options.

After clicking “New Simple Volume” a wizard will appear.

SimpleVolumeWizard

Click Next and you will see the following page. Choose your volume size and click Next.

VolumeSize

Choose a drive letter, create a mount point on an existing disk or do not assign a drive letter. In this example, I’ll give the disk a drive letter of F. Click Next.

On this screen, you will need to format the volume using NTFS and you can rename the volume to better identify it later on. I will also perform a quick format. Click Next and Finish to format.

The disk should now be online and allocated.

Click here to view the rest of this post.

Managing a Windows and SQL Server Cluster using the Failover Cluster Manager tool

By bradyupton in Clustering, High Availability May 8, 2012 0 Comment

Installing a cluster is just the beginning of a DBA’s administrative duties. In this post, I’ll show you a few management tasks that may help the novice. Most tasks will be carried out using Failover Cluster Management which is the management console built into Windows Server 2008. You can access the Failover Cluster Management in a couple of different ways:

Go to Server Manager, Features, Failover Cluster Manager

Go to Control Panel, Administrative Tools, Failover Cluster Manager

Once you open Failover Cluster Manager let’s see what can be done inside of this console.

View Status of Services and Applications

Inside of Failover Cluster Manager, if you click on Services and Applications you are presented with a wealth of information.

In this example, I have a multiple instance cluster that consists of four instances on a two node active/passive cluster. After clicking Services and applications, you can see in the right pane all the instances, status, type, current owner (or current node that they exist on) and if they are set to auto start. In the right pane, if you click on a specific instance you can see more information regarding this instance at the bottom.

Likewise, if you drilldown from Services and applications and click on an instance name you will see even more information regarding this instance that includes server name, IP address, disk information, and the services that are clustered.

Click here to view the rest of this post.

SQL Server 2008 Cluster Installation

By bradyupton in Clustering, High Availability April 10, 2012 0 Comment

Here’s a quick and dirty look at how to install a two node Active/Passive SQL 2008 cluster on Windows Server 2008.

Environment: Two servers (nodes) running Windows 2008 R2 Enterprise Edition and SQL Server 2008 R2 Enterprise named TSTPSSQLCL03 and TSTPSSQLCL04. I’m creating a named instance named TSTECSSQLv01\ECS. The Systems Administrator has already set up the Cluster Name (TSTPSSQLCLv03), storage, and given me the following IP address to configure my named instance: 10.101.1.69.

Step 1: On the first node (TSTPSSQLCL03) insert installation media and proceed to the SQL Server Installation Center, Installation, click on New SQL failover cluster installation.

Step 2: A Setup Support Rules check will run to identify problems that might occur when you install SQL Server Support Rules. Click OK.
Step 3: Install Setup Support Rules.
Step 4: Once Setup Support Files install successfully, click Next.
Step 5: Enter Product Key and click Next.
Step 6: Accept the license terms and click Next.
Step 7: On the Features Selection page, select the features to cluster and click Next.
Step 8: On the Instance Configuration page, specify a SQL Server Network Name and choose whether this installation is a default instance or named instance. You can also change the Instance root directory. Click Next.
Step 9: View Disk Space Requirements and click Next.
Step 10: Choose a Cluster Resource Group and click Next.
Step 11: Choose a Cluster Disk Selection and click Next. (This should’ve been setup by the System Administrator).
Step 12: On the Cluster Network Configuration page, it’s better to remove DHCP and specify a static IP address. (The System Administrator should be able to give you the IP address) Once specified, click Next.
Step 13: Choose a Cluster Security Policy and click Next.
Step 14: Enter service accounts and password for the DB Engine and SQL Agent. You can also use the “Use the same accounts for all SQL Services” button if you want to specify the same account for both services. Click Next.
Step 15: On the Database Engine Configuration page, select Mixed Mode and enter a secure ‘sa’ password. Under Specify SQL Server administrators, enter all users that will need sysadmin permissions. Click the Data Directories tab.
Step 16: Under the Data Directories tab, change the directories to the following and click Next:
Step 17: View Error Reporting Options and click Next.
Step 18: A Cluster Installation Rules check will run to determine if the failover cluster installation will be blocked. Once successful, click Next.
Step 19: Look over the “Summary” page to make sure everything looks ok and click Install.
Step 20: Once the install is complete, click Close. To verify an instance has been created, open Failover Cluster Manager (Administrative Tools) and drilldown under Cluster name.
Step 21: Halfway there. On the second node (TSTPSSQLCL04) insert installation media and proceed to the SQL Server Installation Center, Installation, click on Add node to a SQL Server failover cluster.
Step 22: A Setup Support Rules check will run to identify problems that might occur when you install SQL Server Support Rules. Click OK.
Step 23: Install Setup Support Rules.
Step 24: Once Setup Support Files install successfully, click Next.
Step 25: Enter Product Key and click Next.
Step 26: Accept the license terms and click Next.
Step 27: Select SQL Server Instance name to add a node to. Click Next.
Step 28: Specify Service Account passwords and click Next.
Step 29: View Error Reporting Options and click Next.
Step 30: An Add Node Rules check will run to determine if the add node process will be blocked. Once successful, click Next.
Step 31: Look over the “Summary” page to make sure everything looks ok and click Install.
Step 32: Once installation is complete, click Close. Repeat steps 21 – 31 for each node.

SQL Server High Availability Options

By bradyupton in AlwaysON, Clustering, Database Mirroring, High Availability, Log Shipping, Replication October 24, 2011 0 Comment

Finding the right high availability option can be tricky. The decision really depends on these items:

Needs
Budget
Scope
SQL Server version
Level of automation
Team level support
etc.

At a high level, there are five main high availability options including a new feature set to be release with SQL Server 2012:

Replication
Mirroring
Log Shipping
Clustering
AlwaysON

SQL Server Replication Overview

At a high level, replication involves a publisher and subscriber, where the publisher is the primary server and the subscriber is the target server. Replication’s main purpose is to copy and distribute data from one database to another. There are four types of replication that we will outline:

Snapshot replication
Transactional replication
Merge replication
Peer to Peer replication

Snapshot: Snapshot replication occurs when a snapshot is taken of the entire database and that snapshot is copied over to the subscriber. This is best used for data that has minimal changes and is used as an initial data set in some circumstances to start subsequent replication processes.

Transactional: Transactional replication begins with a snapshot of the primary database that is applied to the subscriber. Once the snapshot is in place all transactions that occur on the publisher will be propagated to the subscriber. This option provides the lowest latency.

Merge: Merge replication begins with a snapshot of the primary database that is applied to the subscriber. Changes made at the publisher and subscriber are tracked while offline. Once the publisher and subscriber are back online simultaneously, the subscriber synchronizes with the publisher and vice versa. This option could be best for employees with laptops that leave the office and need to sync their data when they are back in the office.

Peer to Peer: Peer to Peer replication can help scale out an application. This is because as transactions occur they are executed on all of the nodes involved in replication in order to keep the data in sync in near real time.

Pros and Cons for SQL Server Replication
Pros	Cons
Can replicate to multiple servers	Manual failover
Can access all databases being replicated	Snapshot can be time consuming if you have a VLDB
Replication can occur in both directions	Data can get out of sync and will need to re-sync

SQL Server Database Mirroring Overview

Database Mirroring involves a principal server that includes the principal database and a mirror server that includes the mirrored database. The mirror database is restored from the principal with no recovery leaving the database inaccessible to the end users. Once mirroring is enabled, all new transactions from the principal will be copied to the mirror. The use of a witness server is also an option when using the high safety with automatic failover option. The witness server will enable the mirror server to act as a hot standby server. Failover with this option usually only takes seconds to complete. If the principal server was to go down the mirror server would automatically become the principal.

Click here to view the rest of this post.