Always On Technology 2016

Making Basic Availability Groups (Always On Availability Groups)

React as Advanced Availability Groups (Always On Availability Groups)

Always On Basic Availability Groups provide a basic availability solution for SQL Server 2016 and SQL Server 2017 Standard Edition.

A basic availability group supports a failover environment for a single database. It is created and managed much like traditional (advanced) Always On Availability Groups (SQL Server) with Enterprise Edition. The differences and limitations of basic availability groups are summarized in this document.

Features

Always On Basic Availability Groups replaces the deprecated Database Mirroring feature and provides a similar level of feature support. Basic availability groups enable a primary database to maintain a single replica. This replica can use either synchronous-commit mode or asynchronous-commit mode. For more information about availability modes, see Availability Modes (Always On Availability Groups).

The secondary replica remains inactive unless there is a need to failover. This failover reverses the primary and secondary role assignments, causing the secondary replica to become the primary active database. For more information on failover, see Failover and Failover Modes (Always On Availability Groups). Basic availability groups can operate in a hybrid environment that spans on-premises and Microsoft Azure.

Limitations

  • Basic availability groups use a subset of features compared to advanced availability groups on SQL Server 2016 Enterprise Edition. Basic availability groups include the following limitations:
  • Limit of two replicas (primary and secondary).
  • No read access on the secondary replica.
  • No backups on the secondary replica.
  • No integrity checks on secondary replicas.
  • No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).
  • Support for one availability database.
  • Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.
  • Basic availability groups are only supported for Standard Edition servers.
  • Basic availability groups cannot be part of a distributed availability group.

Configuration

An Always-On basic availability group can be created on any two SQL Server 2016 Standard Edition servers. When you create a basic availability group, you must specify both replicas during creation.

To create a basic availability group, use the CREATE AVAILABILITY GROUP Transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). You can also create the basic availability group using the UI in SQL Server Management Studio starting with version 17.8. For more information, see CREATE AVAILABILITY GROUP (Transact-SQL).

Making Standard Edition react as Advanced edition:

By Providing an Extension JOBS We could achieve an advanced availability group features to the basic availability group as following:

A Basic option is configured only one Database to an Availability group.

We will configure a job that will perform failover of all declared groups.

Basic Availability group’s limitation is only one Primary replica and on the secondary replica

By using the same job and script we can achieve a replica chaining Thus: more than one secondary.

By using the same Attitude We also can achieve

  • Backups on a secondary replica
  • Integrity checks on secondary replicas

Which we miss in a standard edition.

How to Reset SA Password In SQL Server Using Single-User Mode?

1. Open Command Prompt in administrator mode:

Type in “Command Prompt”

Right-click

Click “Run as administrator”

Click “Yes” when prompted

2. Stop SQL Instance from running:

Type in “net stop SQL Server (<Instance>)

Press “Enter”

This will stop the currently running SQL Services.

3. Restart SQL in Single-User Mode

Type in “net start MSSQLSERVER -m”SQLCMD”

Press “Enter”

You won’t see any indication that you’re running in Single-User Mode at this point, but you should see the phrase “The SQL Server <MSSQLSERVER> service was started successfully” appear.

4. Connect to SQL:

Type in “sqlcsd”

Press “Enter”

Doing so opens the SQL command line

5. Create a new user and password: You’ll do this with typed commands in the SQL command line:

Type in “create login <Login Name> with password=<’password’> (where “name” is the account name and “password” is the new password

Press “Enter”

Type in “go”

Press “Enter”

6. Add the user to the System Administrator role:

Type in “SP_ ADDSRVROLEMEMBER name, ‘SYSADMIN’” where “name” is the account name

Press “Enter”

Type in “GO”

Press “Enter”

7. Exit the SQLCMD command line:

Type in “Exit”

Press “Enter”

8. Restart SQL in regular mode:

Cancel Single-User mode by typing in

net stop MSSQLSERRVER

Press “Enter”

net start SQL Server (instance)

Press “Enter”

You should see the “The SQL Server <MSSQLSERVER> service was started successfully” phrase appear again

At this point, you can close Command Prompt

9. Open SSMS:

Type SQL server management studio into Start

Click Microsoft SQL Server Management Studio 17 at the top of the Start

10. Elect the correct authentication:

Click the “Authentication”

Drop-down box

Click SQL Server Authentication in the menu

11. Log in with the new user’s credentials:

Click the “Login”

Drop-down box

Click the name of the user you just created

12. Click “connect”: It’s at the bottom of the window/ as long as you adequately entered your username and password, this will open your server’s dashboard.

13. Expand your server’s folder: If your server’s folder in the upper-left side of the window doesn’t have several options below it, click the + icon to the left of it to expand it.

14. Expand the “security” folder: It’s below the server’s name.

15. Expand the “Logins” folder: You’ll find this in the group of options below the “Security” folder.

16. Double-click SA: It’s in the “Logins” group of options. Doing so opens your System Administrator properties window.

17. Enter a new password:

Type your new password into both the “Password” and the “Confirm password” text fields near the top of the window.