MS SQL Redu

Redundant station system

MS SQL Backend can work with the MS SQL cluster. For creating the cluster using AlwaysOn technology. MS SQL Server Standard Edition supported only Basic Availability Groups:

• Limit of two replicas.
• No read access on secondary replica.
• No backups on secondary replica.
• No integrity checks on secondary replicas.
• Support for one availability database.

  

To create MSSQL Server cluster require:

• Windows Server 2016 and above.
• MS SQL Server 2019 Standard Edition

For creation Always On availability group you need create failover cluster (see Create a failover cluster).

To enable Always On Availability Groups using SQL Server Configuration Manager:

1. Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL Server instance where you want to enable Always On Availability Groups.
2. On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and click SQL Server Configuration Manager.
3. In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (“Instance Name”), where "Instance Name" is the name of a local server instance for which you want to enable Always On Availability Groups, and click Properties.
4. Select the Always On High Availability tab.
5. Verify that Windows failover cluster name field contains the name of the local failover cluster. If this field is blank, this server instance currently does not support Always On availability groups. Either the local computer is not a cluster node, the WSFC cluster has been shut down, or this edition of SQL Server that does not support Always On availability groups.
6. Select the Enable Always On Availability Groups check box, and click OK (see figure below).

   

   SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL

   Server service. When the SQL Server service restarts, Always On will be enabled.

   For more information, see Enable or Disable Always On availability group feature.

Requires membership in the sysadmin fixed server role and either “CREATE AVAILABILITY GROUP” server permission, “ALTER ANY AVAILABILITY” GROUP permission, or “CONTROL SERVER” permission.

Also requires “CONTROL ON ENDPOINT” permission if you want to allow Availability Group Wizard to manage the database mirroring endpoint.

Databases in an availability group must be in full recovery model. The database needs to have a current full backup and transaction log backup. These backup files are not used for automatic seeding, but they are required before including the database in an availability group. For details on how to make a full backup and transaction log backup, see Create a Full Database Backup and Back Up a Transaction Log.

Logins don’t automatically sync across replicas. If the logins don’t sync, the application won’t connect to a secondary, which results in login failures. You can sync logins like this:

• Run the following sql script on the primary replica:

  SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+ CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+ N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' FROM master.sys.server_principals AS sp INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid] WHERE sp.name='LoginName';

where 'LoginName' - is the login for synchronization.

• As a result of executing this script, a script will be obtained to recreate the login on the sec-ondary replica. This script will look like this:

  CREATE LOGIN [LoginName] WITH PASSWORD=0x0200FA4BA9433858753F8E66FA327B58ECEE19D0DC8BF8CD7285284E2EBF9E07E0307E0BEC3CADEB5ECC88FDF3FB31124FF365D003AF4514E656F13A64C0F1CF0A997E35ACE3 HASHED, SID=0x5D8A73138DF8C54BB27C822AED041FDF;

  where 'LoginName' - is the login for synchronization.

• Run the resulting script on all secondary replicas.
  Anmerkung: NOTE: If the login already exists, then it must be deleted.

Follow these steps to create an availability group:

1. In Object Explorer, connect to the server instance that hosts the primary replica.
2. Expand the Always On High Availability node and the Availability Groups node.
3. To launch the New Availability Group Wizard, select the New Availability Group Wizard command.
4. The first time you run this wizard, an Introduction page appears.

   

5. On the Specify Availability Group Options page, enter the name of the new availability group in the Availability group name field.

   This name must be a valid SQL Server identifier that is unique on the cluster and in your domain as a whole.

   The maximum length for an availability group name is 128 characters. Next, specify the cluster type. The possible cluster types depend on the SQL Server version and operating system. Choose WSFC.

   

6. On the Select Databases page, the grid lists user databases on the connected server instance that are eligible to become the availability databases. Select database to participate in the new availability group. These database will initially be the initial primary database.

   

7. On the Specify Replicas page, specify and configure primary and secondary replicas for the new availability group. This page contains four tabs.
   1. Replicas Tab Use this tab to specify each instance of SQL Server that will host a secondary replica. Note that the server instance to which you are currently connected must host the primary replica.

   Option name	Description
   Server Instance	Displays the name of the server instance that will host the availability replica.
   Automatic Failover	Determines failover mode. Select "Automatic Failover option" for each replica.
   Synchronous Commit	The availability mode is a replica property that determines whether a given availability replica can run in synchronous-commit mode. For "Automatic Failover" option used "Synchronous-commit" mode.
   Readable Secondary Role	It determines the read availability of the secondary replica. For "Automatic Failover" option and "MSSQL Standard Edition" read access is not allowed.

   

   b. Endpoints Tab Use this tab to verify any existing database mirroring endpoints and also, if this endpoint is lacking on a server instance whose service accounts use Windows Authentication, to create the endpoint automatically. If you using a non-domain account then use Transact-SQL or PowerShell to manually create a database mirroring endpoint that uses a certificate. For more information, see CREATE ENDPOINT (Transact-SQL) and CREATE ENDPOINT (PowerShell).

   Option	Description
   Server Name	Displays the name of a server instance that will host an availability replica.
   Endpoint URL	Displays the actual or proposed URL of the database mirroring endpoint.
   Port Number	Displays the actual or proposed port number of the endpoint.
   Encrypt Data	Indicates whether data sent over this endpoint is encrypted.
   SQL Server Service Account	Username of the SQL Server service account.

   

   c) Backup Preferences Tab

   Use this tab to specify your backup preference for the availability group as a whole and your backup priorities for the individual availability replicas. For MSSQL Server Standard Edition used only Primary replica. You need to set up backup jobs if you want to make backup database.

   Option name	Description
   Prefer Secondary	Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online.
   Secondary only	Specifies that backups should never be performed on the primary replica.
   Primary	Specifies that the backups should always occur on the primary replica.
   Any Replica	Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups.

   

   d) Listener Tab

   Use this tab to create an availability group listener. The listener will provide a client connection point. IP Address of the listener is used in the connection string to connect the database cluster.

   Option name	Description
   Listener DNS Name	Specify the network name of the listener.
   Port	Specify the TPC port used by this listener.
   IP Address	Specify the IP address

   

   For more information, see Listener.

8. On the Select Initial Data Synchronization page, choose how you want your new secondary data-bases to be created and joined to the availability group. Choose one of the following options:
   • Automatic seeding: SQL Server automatically creates the secondary replicas for every database in the group. Automatic seeding requires that the data and log file paths are the same on every SQL Server instance participating in the group. Available on SQL Server 2016 (13.x) and later. See Automatically initialize Always On Availability group.
   • Full database and log backup: Select this option if your environment meets the requirements for automatically starting initial data synchronization (for more information, see Prerequisites, Restrictions, and Recommendations, earlier in this topic). If you select Full, after creating the availability group, the wizard will back up every primary data-base and its transaction log to a network share and restore the backups on every server instance that hosts an secondary replica. The wizard will then join every secondary database to the availability group.
   • Join only: If you have manually prepared secondary databases on the server instances that will host the secondary replicas, you can select this option. The wizard will join the existing secondary databases to the availability group.
   • Skip initial data synchronization: Select this option if you want to use your own database and log backups of your primary databases. For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

     

9. The Validation page verifies whether the values you specified in this Wizard meet the requirements of the New Availability Group Wizard. To make a change, click Previous to return to an earlier wizard page to change one or more values. The click Next to return to the Validation page, and click Rerun Validation.

   

10. On the Summary page, review your choices for the new availability group. To make a change, click Previous to return to the relevant page. After making the change, click Next to return to the Summary page. If you are satisfied with your selections, optionally click Script to create a script of the steps the wizard will execute. Then, to create and configure the new availability group, click Finish.

    

11. The Progress page displays the progress of the steps for creating the availability group (configuring endpoints, creating the availability group, and joining the secondary replica to the group). When these steps complete, the Results page displays the result of each step. If all these steps succeed, the new availability group is completely configured. If any of the steps result in an error, you might need to manually complete the configuration or use a wizard for the failed step. For in-formation about the cause of a given error, click the associated "Error" link in the Result column.

    

When the wizard completes, click Close to exit. For more information, see Creation and configuration of Always On availability groups. You can monitor the status of an availability group using an internal monitor included in SQL Server Management Studio.

Use the listener address (IP address or DNS name) to connect to the cluster, see the Listener Tab for more information.

This property is used to avoid the indefinite movement of a clustered re-source when multiple node failures occur.

Check the "Maximum Failures in the Specified Period property":

1. Start "Failover Cluster Manager".
2. In the navigation pane, click "Roles".
3. In the "Roles" pane, right-click the clustered resource, and then click "Properties".
4. Click the "Failover tab, and check the “Maximum Failures in the Specified Period value".

For details, see Troubleshooting automatic failover problems in SQL Server AlwaysOn environments.

MSSQL Server Availability Group includes work with two network adapters. To verify that the network adapters are included in the cluster

• Open the Failover Cluster Manager.
• Make sure each NIC has a use mode of Cluster Only or Cluster and Client
  • Cluster Only – used for communication only between cluster nodes
  • Cluster and Client – used for communication between cluster nodes and clients.

    

If you want both network adapters to work with both clients and database instances, then you can:

• Enable the use of the "Cluster and Client" mode for both network adapters.

  

• Add a second network address for the listener:
  • During configuring an availability group (see Set up AlwaysOn Availability Group).
  • Adding a new address to an existing group listener:
    • Using Failover Cluster Manager

      

• Using SQL Server Management Studio

  

In the backend connection string, you can specify the listener address in the form:

• DNS name of the listener. In this case, the SQL Server driver will automatically manage the connection. (Example: “ListenerDNSName:port”).
• List of IP addresses. In this case, the choice of adapter and connection will be controlled by the backend. (Example: “ip_first_adapter:port, ip_second_adapter:port”).

• Create a distributed project using the WinCC OA wizard and configure the backend according to chapter Error! Reference source not found.
• Set up a connection string of the backend to all instances (see Error! Reference source not found.).

SQL Server supports the following types of backups:

1. Full backup, A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
2. Differential backup, A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data that has changed since that base.
3. Log backup, A backup of transaction logs that includes all log records that were not backed up in a previous log backup (full recovery model).

   After you decide what types of backups you require and how frequently you have to perform each type, we recommend that you schedule regular backups as part of a database maintenance plan for the database. For information about maintenance plans and how to create them for database backups and log backups, see Maintenance Plans. For details, see Back Up and Restore of SQL Server Databases.

To restore a database in an availability group, follow these steps:

1. Remove the database from the availability group (see Remove a primary database from an Always On availability group).
2. Restore the database.
3. Join the database to the availability group (see Add a Database to an Always On availability group).

For testing purposes the following framework should be used:

• Google test framework.
• ETM Test framework for automated acceptance tests.