×
Community Blog Quick Path to Create RDS SQL Server Enterprise Edition Primary Instance and Read-Only Instance

Quick Path to Create RDS SQL Server Enterprise Edition Primary Instance and Read-Only Instance

This tutorial shows you how create RDS SQL Server Enterprise Edition primary instance and read-only instance quickly.

More tutorial around Alibaba Cloud Database, please refer to this link.

Overview

1

Use Terraform to provision ECS and RDS SQL Server database (Primary + Read-Only) on Alibaba Cloud

If you are the 1st time to use Terraform, please refer to this link to learn how to install and use the Terraform on different operating systems.

Run the terraform script to initialize the resources. Please specify the necessary information and region to deploy.

2

After the Terraform script execution finished, the ECS instance and RDS for SQL Server information are listed as below.

3

Execute the commands to install SQL Server command line tool

Following the guide to execute the following commands to install SQL Server command line tool.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo apt install mssql-tools -y

Using the command sqlcmd to connect to the SQL Server primary instance

/opt/mssql-tools/bin/sqlcmd -S <SQL_SERVER_PRIMARY_CONNECTION_STRING>,<SQL_SERVER_PRIMARY_CONNECTION_PORT> -U <USER_NAME> -P '<PASSWORD>' -d <DATABASE_NAME>

Please replace the placeholders of the parameters, such as,

/opt/mssql-tools/bin/sqlcmd -S rm-3ns02f8l01hszpg6w.sqlserver.rds.aliyuncs.com,1433 -U test_sqlserver -P 'N1cetest' -d test_database

Then execute the SQL commands as below to CREATE TABLE, INSERT and SELECT against the SQL Server primary connection to the primary instance.

select @@version
go

Select Name FROM SysObjects Where XType='U' ORDER BY Name
go

DROP TABLE IF EXISTS Departments
CREATE TABLE Departments (DepartmentId   int PRIMARY KEY, DepartmentName varchar(20))
INSERT INTO Departments VALUES (1, 'Mechanical') 
INSERT INTO Departments VALUES (2, 'Chemical') 
INSERT INTO Departments VALUES (3, 'Electronic') 
INSERT INTO Departments VALUES (4, 'Textile') 
INSERT INTO Departments VALUES (5, 'Civil')
go

SELECT * FROM Departments
go

4

Using the command sqlcmd to connect to the SQL Server read-only instance

/opt/mssql-tools/bin/sqlcmd -S <SQL_SERVER_READONLY_CONNECTION_STRING>,<SQL_SERVER_READONLY_CONNECTION_PORT> -U <USER_NAME> -P '<PASSWORD>' -d <DATABASE_NAME>

Please replace the placeholders of the parameters, such as,

/opt/mssql-tools/bin/sqlcmd -S rr-3nsa4tx7i04xb1w00.sqlserver.rds.aliyuncs.com,1433 -U test_sqlserver -P 'N1cetest' -d test_database

Then execute the SQL commands as below. We can see that INSERT is not allowed on the read-only connection string to the read-only instance.

INSERT INTO Departments VALUES (5, 'Civil')
go

SELECT * FROM Departments
go

5

0 0 0
Share on

ApsaraDB

235 posts | 18 followers

You may also like

Comments