High Availability | 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.

Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases

By bradyupton in Database Mirroring, High Availability June 13, 2012 0 Comment

In this post, I am going to outline my environment and then walk through the process of patching mirrored servers.

My test environment consists of two SQL Server 2005 SP2 servers named SSQL1 (principal) and SSQL2 (mirror) that contain eleven mirrored databases. The database mirroring operating mode is set to asynchronous and I’m upgrading to SP4.

Here is a look at the two mirrored instances.

Step 1
Always backup all system and user databases before applying patches.

Step 2
Remote Desktop into the “Mirror” server (SSQL2 in our example) and download/copy the patch to the server.

Step 3
Stop all SQL Services on the “Mirror” server.

Step 4
Run the patch on the “Mirror” server.

Step 5
Once the patch is complete, reboot the “Mirror” server.

Step 6 (optional)
If your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first. To do this we will need to issue the following statement for every database on the “Principal” server that is mirrored.

ALTER DATABASE databasename SET SAFETY FULL

Here are the commands for the 11 databases on my server.

Step 7
The databases might change to “synchronizing” while the transactions catch up. Once all of the databases show “synchronized”, as shown below, we can perform the manual failover.

We can perform the failover using the following statement on the principal server for each database:

ALTER DATABASE databasename SET PARTNER FAILOVER

Here are the commands for the 11 databases on my server.

We can now see that the servers have switched roles.

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 AlwaysOn – Part 2 – Availability Groups Setup

By bradyupton in AlwaysON, High Availability November 22, 2011 0 Comment

In Part 1 we configured prerequisites for SQL Server 2012 new AlwaysOn and in Part 2 we’ll go over the fun stuff…configuring the High Availability Groups.

Create Sample Database and Create Backups

First thing we need to do is connect to the primary server using SSMS and create two databases and back them up to the network share we created. In this example I’ll create database RammerJammer and RollTide and then create database backups.

Specify Name

In SSMS go to Management, right click Availability Groups and click New Availability Group Wizard. Once the wizard appears click Next on the main screen and create a unique Availability Group name on the Specify Availability Group Name screen. I’ll name my group AG-Bama and then click Next.

Select Databases

On the next screen we will need to select our databases that we want added to our availability groups. This screen also has a status column that will let us know ahead of time if our databases meet the prerequisites. I’ll select both databases and click Next.

Specify Replicas

On the next screen click Add Replica… and connect to the other server (Denali2) Replica Mode can be set to Automatic Failover, High Performance, or High Safety.

Automatic Failover: This replica will use synchronous-commit availability mode and support both automatic failover and manual failover.
High Performance: This replica will use asynchronous-commit availability mode and support only forced failover (with possible data loss).
High Safety: This replica will use synchronous-commit availability mode and support only manual failover.

Connection Mode in Secondary Role can be set to Disallow connections, Allow only read-intent connections, or Allow all connections.

Disallow connections: This availability replica will not allow any connections.
Allow only read-intent connections: This availability replica will only allow read-intent connections.
Allow all connections: This availability replica will allow all connections for read access, including connections running with older clients. For this example, I’ll choose Automatic Failover and Disallow connections to my secondary role and click Next.

Click here to view the rest of this tip.

New SQL Server AlwaysOn Feature – Part 1 configuration

By bradyupton in AlwaysON, High Availability November 15, 2011 0 Comment

SQL Server has produced some excellent High Availability options, but I was looking for an option that would allow me to access my secondary database without it being read-only or in restoring mode. I need the ability to see transactions occur and query the secondary database.

Enter SQL Server 2012 AlwaysON High Availability Groups.

The new AlwaysOn feature combines the powers of clustering and mirroring into one High Availability option, but also allows you to interact with the secondary databases something that clustering and mirroring do not allow. In addition, AlwaysOn Availability Groups allows you to configure failover for one database, a set of databases or the entire instance again something you could not do with database mirroring. Another feature is that you can create multiple failover targets where in the past database mirroring only allowed one failover partner.

In this tip I’ll show you a basic look at how to setup SQL Server 2012 AlwaysON Availability Groups. Note that this tip is separated into 2 parts: Part 1 will consist of installing and configuring the prerequisites for AlwaysOn and Part 2 will consist of setting up the Availability Groups and showing how they work.

Let’s get started…

Environment: I have setup two separate VM’s running in VM Workstation with Windows 2008 R2 Enterprise Edition and SQL Server 2008 R2 Enterprise named Denali and Denali2.

First thing we need to do is make sure both servers have .NET Framework 3.0 Features and Failover Clustering installed. To do this, go to Server Manager, Features, Add Feature. Check .NET Framework 3.0 and Failover Clustering and Install as shown below.

Once .NET and Failover Clustering are installed we can configure the cluster. Go to Control Panel, Administrative Tools, Failover Cluster Manager and click Validate a Configuration.

On the “Validate A Configuration Wizard” page click Next, then enter the names of the SQL Servers you want to configure. In this example we are using Denali and Denali2.

Click Next to run through some validation tests. If you receive any errors click View Report to view the errors.

As you can see from above I received errors on my validation, because I am only using one network card. This means I have a single point of failure. In a production environment you would want to fix this, but this is only a warning and does not stop us from configuring clustering. From the main screen click “Create a Cluster”

After clicking “Create a Cluster” and clicking Next on the main page you should see the “Access Point for Administering the Cluster”. Here is where you will type in the name of your cluster. This doesn’t need to be your server name. Use a name to distinguish this cluster from other clusters. In this example, I’ll use DenaliCluster.

Click here to view the rest of this post.

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.

SQL Server Log Shipping

By bradyupton in High Availability, Log Shipping August 24, 2011 0 Comment

Backup Database on Primary Server:

Restore Database on Secondary Server: (RESTORE WITH STANDBY)

(Picture of Object Explorer once restore is complete)

Create a shared folder on the primary server (SQL Server Agent service account must have read/write permissions):

Create a shared folder on the secondary server (SQL Server Agent service account must have read/write permissions):

Enable Log Shipping at the Publisher:

Click “Enable this as a primary database in a log shipping configuration”, then click Backup Settings:

Enter Network Share on Primary Server
Enter Local Folder path on Primary Server
Enter the number of Minutes, Hours, or Days to keep the deleted files and to Alert if no backup occurs
Name the SQL Agent Job and determine a schedule to backup the log files

Click OK

Click Add to add a secondary server:

Click Connect to connect to the secondary server:

Select No, since we initialized the database in the beginning by restoring in Standby Mode, otherwise you can choose one of the other options. Hit OK.

Go to next tab, Copy Files.

Enter Local Folder Path on Secondary server
Enter the number of Minutes, Hours, or Days to delete copied files
Name the SQL Agent Job and determine a schedule to copy the log files

Go to next tab, Restore Transaction Log.

Since we put the database in Standby/Read Only mode select Standby Mode
If you would like to delay restoring the transaction log you can enter a value in the “Delay restoring backups at least” otherwise leave at 0 minutes.
Enter the number of Minutes, Hours, or Days to Alert if no restore occurs
Name the SQL Agent Job and determine a schedule to restore the log files

Hit OK

**OPTIONAL** To create a monitor server click “Use monitor server instance” and then click Settings

Click Connect to connect to a monitor server instance
Select the login method you would like to use to connect to the monitor server.
Enter the number of Minutes, Hours, or Days to delete log file data
Name the SQL Agent Job and determine a schedule to for the alert task

Hit OK twice.