Shulou(Shulou.com)06/01 Report--

SQL Cluster + Install New Instance + Enable AlwaysOn Availability Groups Feature Main Content Parallel Installations Running Different Versions and Instances of SQL Server Enable AlwaysOn Availability Groups Feature

experimental environment

SQL Server 2012 +SQL Server 2016

Scenario 1: Cluster01-SQL cluster is built based on SQL Server 2012.

hostnameIP instance name port description ADdc1.contoso.com192.168.136.150SQLSQL1.contoso.com192.168.136.151Cluster01-sql install SQL clusterSQL2.contoso.com192.168.136.152Cluster01-sql install SQL cluster01-SQL192.168.136.1571433SQL clusterStorageStorage.contoso.com192.168.136.153iSCSI

Scenario 2: Building on Scenario 1, install SQL Server 2016 and enable the AlwaysOn Availability Groups feature.

hostnameIP Instance Name Port Description ADdc1.contoso.com192.168.136.150SQL1.contoso.com192.168.136.151MSSQLSERVER0251433 Install SQL Standalone Instance SQL2.contoso.com192.168.136.152MSSQLSERVER0251433 Install SQL Standalone Instance AG_Listener192.168.136.1651433 Listener Cluster01-SQL192.168.136.1571433SQL Cluster Storage.contoso.com192.168.136.153iSCSI

Note: You need to mount a disk as the data disk for the second instance.

AlwaysOn does not support SQL clustering. So here it's implemented with the same instance installed independently on each node.

-------------------

Configuration Summary.

Change your service login account.

Modify port numbers to prevent port conflicts. Restart the service.

Connect to the database. You can see different versions of sql and instances on the same server.

Next up is a standalone installation of SQL Server 2016 on SQL2.contoso.com. No more pictures here. (The procedure is the same as installing node1)

Attached:

Parallel use of SQL Server with earlier versions of SQL Server

SQL Server can be installed on computers that are already running instances of earlier versions of SQL Server. SQL Server must be installed as a named instance if a default instance already exists on the computer.

careful

SQL Server SysPrep does not support parallel installation of a prepared instance of SQL Server 2016 and an earlier version of SQL Server on the same computer. For example, you cannot install SQL Server 2016 instances in parallel with prepared instances of SQL Server 2012. However, multiple prepared instances of the same major version of SQL Server can be installed in parallel on the same computer. For more information, see Considerations for Installing SQL Server Using SysPrep.

SQL Server 2016 cannot be installed side-by-side with earlier versions of SQL Server on computers running Windows Server 2008 R2 Server Core SP1. For more information about Server Core installation, see Installing SQL Server 2016 on Server Core.

The following table shows parallel support for SQL Server 2016:

Prevent IP address conflicts

When installing SQL Server failover cluster instances in parallel with separate instances of the SQL Server Database Engine, be careful to avoid TCP port number conflicts on IP addresses. Conflicts typically occur when both instances of the database engine are configured to use the default TCP port (1433). To avoid conflicts, configure an instance to use non-default fixed ports. Configuring fixed ports on a standalone instance is usually the easiest. Configuring the database engine to use different ports prevents accidental IP address/TCP port conflicts that would prevent instances from starting if a SQL Server failover cluster instance fails to a standby node

https://technet.microsoft.com/zh-cn/library/ms143393.aspx

Now that the basic environment installation is complete, it's time to implement the idea.

-----------Start configuring AlwaysOn availability groups-----------

Microsoft SQL Server Management Studio connects to SQL1\MSSQLSERVER02 separately

Create test database "contosoDB2test".

Specify the database path. (Keep the default. It can also be filled in according to the actual environment.)

Backup database.

Create the shared folder "AlwaysOnBAK" on SQL1. Share the file and grant the database administrator and node computers read and write permissions. Such as shared path "\\SQL1\AlwaysOnBAK".

Execute task-backup-full backup.

Backup path: "C:\AlwaysOnBAK\contosoDB2test.bak"

Create availability groups.

Introduction.

Specify a name.

Select the database.

Specify copy. This step requires setup, adding copies and attributes, endpoints, backup options, listeners.

Configure endpoints. The default is retained.

Backup options. Leave the default.

Create an availability group listener. It can also be configured later.

DNS name of listener: AG_Listener

Port: 1433

Network Mode: Static IP: 192.168.136.165

Select Data Synchronization. Specify shared network locations accessible to all replicas: \\sql1\AlwaysOnBAK

Verification. If there is alarm result, please conduct corresponding troubleshooting and re-run verification.

Summary.

Note the lower right corner where scripts can be exported from profiles to files, etc.

Results. Click Done, or select how to execute via the bottom right corner.

Test the AlwaysOn feature.

Create a new query.

use contosoDB2testselect @@servername as [ServerName];go

Manual failover.

Perform a planned failover for this availability group.

Select the new master replica for this availability group. Ask, why is the failover readiness status there "no data loss"? The availability pattern is "synchronous commit." And review, are there any other patterns?

Specifies the connection of the secondary copy.

Summary.

Test results.

END. So far, you have completed the parallel installation and running different versions of SQL instances, and also realized the mixed use of AlwaysOn availability groups under SQL clusters. In fact, if the disk resources are sufficient, it is still possible to build SQL active on the basis of scenarios 1 and 2.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.