Version 1.6

date: 10/29/2018

SQL Configuration Guide

Ensuring High Avialability Configuations

Communication Devices Inc

85 Fulton St # 2, Boonton, NJ 07005

Table of Contents

Abbreviations and Tems List

SQL CONFIGURATION MANUAL Overview

Purpose

This manual is meant to go over the recommended configuration of SQL databases in high availability environments. While CDI’s OBM will configure a SQLExpress instance for the user automatically, many want to incorporate Full MS SQL Server instances that are already part of their enterprise. This guide is meant to help guide administrators on how to configure these databases to make them more resilient to faults.

Audience

The manual is written for those with some knowledge of SQL databases and Windows, but a high level of permission in the Windows Domain, preferably Administrator. If the reader does not have Administrator access and has a some knowledge of SQL or Windows it will be very hard to identify and correct the enterprise specific security restrictions that may appear. Unless both servers are running as the same domain account, then raw SQL commands will need to be run.

Product Versions

MS SQL High Fidelity Mechanisms

The following are mechanisms in MS SQL Server that allow for increased availability of databases. Each section will explain what the mechanism is and how to configure it.

Hot Backups: Database Mirroring

MS SQL Server allows for databases to be backup-ed up in real-time so there is minimal, if any, loss of data if a primary database experiences a fault. When the fault is detected the SQL Server will immediately start treating the backup as the primary database. Database Mirroring has been the primary mechanism to accomplish this sin the early 2000’s and is straight forward and reliable. MS is trying to phase this out for their more robust, but complex Always On Availability Groups. AlwaysOn Availability groups require domain wide configurations, so DB Mirroring is preferred.

Setup

1. You must have two instances (Primary and Backup), preferably on different physical servers, of SQL Server 2014+ installed. This guide had two 2017s.  
   
2. You must have SQL Server Management Studio (SSMS) installed on at least one machine that can connect to both instance
   

SQL SERVER PREP

1. The SQL services need to run as a System Users in order to share backup files. To do this open the Services Manager from the Start menu, find the SQL services, open properties, go to the LogOn Tab, and select Local Service Account . Select OK then right click on the service and click Restart.  This must be done on the Primary and Backup host computers.
   

2. You will also need to be able to share file between the two machines running SQL Server so ensure a shared folder or file transfer tool is ready
   
3. For both Servers add sysadmin permissions by
   

1. Connect to them via SSMS
   
2. In the explorer sidebar, expand the server 
   

1. Clicking Security->Login
   
2. Right Clicking ‘NT AUTHORITY\SYSTEM’ and then properties 
   

3. Go to the Sever Roles page and select sysadmin
   

4. Click OK
   

Authentication Configuration Steps

There are three schemes for MS SQL servers to authenticate with each other for mirroring: 1) have both servers running on same domain and/or same account 2) have servers running under accounts in different domains that can authenticate each other and 3) explicitly exchange certificates for authentication (no domain configuration needed).

Option 3 is the most popular because it does not require domain configuration (both servers can just run as local accounts); however, it does involved the most steps. Follow the steps before the “Creating the Mirror Database” section listed here. Once finished continue with the below steps except when configuring the Mirroring in the wizard you should already see the Endpoint name as “Endpoint_Mirroring “.

If you can run the servers under domain accounts (via the services manager) then Option 1 and 2 are less technical. If both servers are running under the same domain user then you can just proceed with the steps below. If they are different users in the same domain then you should be able to continue as well (especially if you are also part of that domain). If it does not work then you will need to follow the same steps as users of different domains. Users is different but accessible domains should run the commands below. Once finished continue with the below steps except when configuring the Mirroring in the wizard you should already see the Endpoint name as “Endpoint_Mirroring “. Reference

On the Primary Server, use SSMS to execute the following commands 

CREATE ENDPOINT Endpoint_Mirroring  

   STATE=STARTED   

   AS TCP (LISTENER_PORT=7022)   

   FOR DATABASE_MIRRORING (ROLE=PARTNER)  

GO  

USE master ;  

GO  

CREATE LOGIN [SAME_OR_DIFF_DOMAIN\SERVER_BACKUP_ACCOUNT] FROM WINDOWS;  

GO  

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SAME_OR_DIFF_DOMAIN\SERVER_ BACKUP_ACCOUNT];  

GO  

On the Backup Server, use SSMS to execute the following commands 

CREATE ENDPOINT Endpoint_Mirroring  

   STATE=STARTED   

   AS TCP (LISTENER_PORT=7022)   

   FOR DATABASE_MIRRORING (ROLE=PARTNER)  

GO  

USE master ;  

GO  

CREATE LOGIN [SAME_OR_DIFF_DOMAIN\SERVER_PRIMARY_ACCOUNT] FROM WINDOWS;  

GO  

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SAME_OR_DIFF_DOMAIN\SERVER_ PRIMARY_ACCOUNT];  

GO  

Mirroring Configuration

1. On the machine that is hosting the OBM App Sever, run the OBM installer. In the top panel of the installer choose to install the OBM Database and point to the Primary SQL Server. After the database is created you can close the installer.
   

2. Open SQL Server Management Studio (SSMS) and connect to the primary server that has the initial database. You will see the following databases upon a successful connection.
   

3. Ensure the Recovery Model for the Database is FULL by right Clicking on the Network Security, selecting properties, going to the Options Page and selecting FULL as the Recovery Model. Click OK.
   

4. Now take a FULL Backup of the database, by right clicking on the database, selecting Tasks-> Backup
   

5. Ensure the Backup type is FULL and save the file to a known location by selecting Add
   

6. Now take a Transaction Backup by repeating the last three steps, but 
   

1. Changing the Backup Type to  Transactional
   
2. Make sure the file that contains the FULL backup is in the bottom menu and selected, both the FULL and Transactional backup can be in the same file
   

7. Ensure the Backup file is accessible by the Backup Server
   
8. Create the mirror DB from the backup by right click on the Backup Server connection in SSMS and selecting restore database
   

9.  In the Backup Menu, Select Device ->…->Add, find the folder that contains the backup file and select OK
   

10. Before starting the restore, go to the Options page and select RESTORE WITH NORECOVERY for Recovery State, then select OK
    

11. Right click on the Database on the Primary SQL server and select Properties
    

12. Select the Mirroring Page
    
13. Select Configure Security and then next
    

14.  Select No to disable ‘witness’ configuration
    

15. Select next for default ‘Principle’ Server settings
    

16. Select the down arrow and find the other SQL server instance that will be used to backup the databases and select connect. You will be prompted with a login screen again. In the example below my instance name was ‘AnotherBackup’. Once you have successfully connected, select Finish (not Next).
    

17. Finally SQL Management Studio will attempt to apply the settings.
    

18. Select Start Mirroring in the next pop-up and if no errors are given, mirroring has begun
    

Role Switching

In the event that there is an error with the primary database, it will be necessary to ‘Role Switch’ the mirrored database into the new primary database. Once the Role Switch is complete the once mirroring database can be used exactly like the primary database. 

Manual Failover (Preferred) – The primary database is reachable 

Steps

1. Open SSMS and connect to the Primary Database
   
2. In the Object Explorer pane, click the server name to expand the server tree.
   
3. Expand Databases, and select the database to be failed over.
   
4. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
   

5. Click Failover.
   

6. A confirmation box appears. The principal server begins by trying to connect to the mirror server by using Windows Authentication. If Windows Authentication does not work, the principal server displays the Connect to Server dialog box. If the mirror server uses SQL Server Authentication, select SQL Server Authentication in the Authentication box. In the Login text box, specify the login account to connect with on the mirror server, and in the Password text box, specify the password for that account.
   

7. If failover succeeds, the Database Properties dialog box closes. The mirror database becomes the principal database and the principal database becomes the mirror.
   
8. If failover fails, an error message is displayed and the dialog box remains open.
   
9. Hit REFRESH
   

10. The Primary is now in the MIRRORED MODE and the Secondary should now be in the PRINCIPLE mode
    

11. Go to the Secondary server and see that it is now in the PRINCIPLE mode
    
12. See Screen
    

Notes

In a Manual failover case, the primary SQL server is reachable and can directly signal to the mirrored backup to become the primary.  You should not need to do anything to the other SQL server directly, this should signal to it to become the primary. Any active App Servers should now point to the once mirrored, now active SQL Server.

Once the former primary site is back up, it should already be in mirrored mode and recover any lost data once reconnected. You may then initiate a manual failover again, with both databases online to switch roles to their original configuration (for more read the Manual Failover section in Overview).

References

1. Overview
   
2. Steps
   

Forced Failover (Emergency)  – Primary Database in not reachable

Steps

1. Connect to the mirror server.
   
2. Issue the following statement:
   

1. ALTER DATABASE NetworkSecurity SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
   

Notes

In the Forced Failover case there is a slight chance for a very small amount of data to be lost (that data which was mid transit during the Primary Servers failure). Given the frequency of write calls to our database this should be minimal.  

Once the Forced Failover is issued, switch all active App Servers to the backup server.

To restore the original site, you will need to take a transition log backup from the working site and restore it on the original site in No Recovery Mode (see Roll Forward Link). Once that is done you should be able to configure the original site as a mirror (just use the Mirror wizard if the endpoint configuration is still there). Once in Mirror mode, you can transition it back into the primary by issuing the backup site Manual Failover, thus making it the mirror again the original site the Primary (see prior section steps). 

References

1. Overview
   
2. Steps
   
3. Roll Forward with Transition logs
   

Installing the OBM on the Secondary server

The OBM on the PRIMARY SERVER is installed as per a standard OBM install using the OBM Manual.

For DB MIRRORING you need to perform a few extra steps to install the OBM on the SECONDARY SERVER

On the Primary Server

1. Open the Service Manager and Stop the OBMService 
   

2. Perform the Manual Failover Procedure detailed above (Mirror becomes Primary, Primary become Mirror)
   

On the Mirror/Secondary Server

1. Run the installer on the Secondary Server – which is now in PRINCIPLE mode
   
2. You could see that the CDI Database may not be pointing to the correct instance of the SQL Server 
   
3. You will also see that the APP server and GUI are not installed.
   

4. To point the DB at the correct instance for the SQL server click on config
   

5. Pull down the Correct Server name and select APPLY SETTINGS
   

6. This will close that window and bring you back to the install menu which should then show the correct instance of the SQL DB and the correct version Installed.
   

1. NOTE: if the database is in Mirror Mode the installer will not detect the database, even if it is pointing at the correct server
   

7. Now install the App Server
   

8. You will be presented with a config menu at the endo of the APP server install
   

9. This may take some time and appear as if it is not responding, but be patient.
   

10. Ensure that the APP Server is pointed to the Secondary SQL Server and click OK
    

11. Now Install the OBM GUI by clicking INSTALL in the OBM GUI section
    

12. When done you will be at a config menu which should not need adjusting
    

The IP Address will say LOCALHOST but once you click it will select the IP address of the APP server that was just installed. You may change this if you want to point to a different APP server – which you should not need to do.

13. SO now you should see the installed version for the Database, the App server and the OBM GUI
    

14. Now to put the systems back in the original  state where the Primary is the PRINCIPAL and the secondary is the MIRROR.  By following the next section
    

Restoring from FAilover back to the orignal config

On the Secondary Server

1. Open the Service Manager and Stop the OBMService 
   

2. Open up SSMS on the secondary server ( which is still acting as the PRINCIPAL )
   

3. Select Mirroring
   
4. Select Failover
   

5. See Message
   

6. Hit Refresh on tree It may take a few minutes to finish before you can hit refresh
   

7. The Primary will now turn to Mirror state and it become the secondary.
   

8. If you run the installer on the now secondary machine you will see that the installer will show Unknown Version for the DB pointed to by the SQL Instance which is the backup server. This is because SQL does not allow access to a DB that is the MIRROR.
   

9. The actual primary server will return to the Principle state automatically.
   

10. If you run SSMS on the Primary you will see that it has returned to the PRINCIPLE state.
    

On the Primary Server

11. You may have to restart the APP server on the primary so that it reconnects to the DB on that server.
    
12. Look for OBMService and restart it by right clicking on the service and selecting RESTART or START
    

13. You have now completed the entire install and also have gone through the FAILOVER Exercise.
    

Setting up datbase maintenance

If there are two databases using “mirroring” then only the PRIMARY DB can be setup for automated backups. The PRIMARY will be backed-up to the MIRROR as well as to a scheduled backup to another location.

Use SQL Server Management Studio 2017 “SSMS” to manage your SQL Database

SSMS 2017 is available for download at no charge from the Microsoft Website.

1. Startup SSMS and log into the SQL Server Instance
   
2. Select the Maintenance Plan Folder
   
3. Select the Maintenance Plan Wizard
   

1. Setup The Maintenance Plan
   

1. Enter a Name
   
2. Recurring Schedule
   
3. Select a Frequency – 1 week recommended
   
4. Select Start date ( today)
   
5. Select OK
   

2. Select Backup Database Full
   
3. Next
   

4. Select the “Network Security” Database
   

5. Select a location to backup the database
   

6. Review the Maintenance plan
   

7. Finish
   

8. To Modify or Execute the plan just right click on it
   

9. The Filename will have the DATE attached and create a new file each time the backup is run
   

10. To restore the database just right click on the Network Security Database and select Restore.
    

11. Select the backup to restore
    

12. Database is restored