Documentation Index

Fetch the complete documentation index at: https://guide.ncloud-docs.com/llms.txt

Use this file to discover all available pages before exploring further.

Getting started

Prev Next

Available in VPC

Getting started explains the tasks you must perform to use Cloud DB for MSSQL properly after reviewing the usage environment and supported specifications and becoming familiar with the overall quickstart and glossary. Getting started covers the following topics:

  • How to create an application server in the NAVER Cloud Platform console.
  • How to test application server connection.
  • How to create a MSSQL Server in the NAVER Cloud Platform console.
  • How to test access to the MSSQL Server.

Once you complete these steps, you have finished the basic process required to use Cloud DB for MSSQL. Afterward, you can perform the following tasks for more efficient server management and operation:

  • Event and notification (alarm) settings using NAVER Cloud Platform's Cloud Insight.
  • Permissions management for using NAVER Cloud Platform's Sub Account.

The above tasks can be done from Cloud Insight or Sub Account after subscribing, rather than in Cloud DB for MSSQL. They are not explained in detail in this guide. For more information, see Cloud Insight user guide and Sub Account user guide.

Create an application server

To create an application server from the NAVER Cloud Platform console:

Note

This section provides a brief overview of how to create an application server. For more information about the full creation procedure, see the Server user guide.

  1. Access the NAVER Cloud Platform console.
  2. Click Region & Platform at the upper right of the console interface.
  3. Select the Region in use and the VPC platform and click [Apply].
  4. Click Menu at the upper left of the console interface.
  5. Navigate to Services > Compute > Server.
  6. Click [Create server].
  7. Select a Windows server image and click [Next].
  8. Enter the required information and click [Next].
  9. If you do not have an existing authentication key, then create a new authentication key and click [Next].
  10. If you do not have any ACG, create a new one.
  11. After the final confirmation, click [Create server].
Caution
  • Select a server image with the Windows type from Step 3 for remote desktop connection.
  • You use the application server ACG name later when adding an ACG to the MSSQL server.

Configure the connection environment

To set up a server access environment in the VPC environment of the NAVER Cloud Platform console, you need to set up a public IP.

Note

This section provides a brief overview of how to set up the access environment. For more information on setting up the access environment, see the following guide:

Set public IP

To set up a public IP:

  1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Compute > Server.
  2. Click Public IP.
  3. Click [Request public IP].
  4. Select the target server, enter a memo, and click [Next].
  5. Click [Create].

Check admin password

To get the admin (root) password required for accessing the application server after setting up the public IP:

  1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Compute > Server.
  2. Click [Server Management and Settings] and click the Check administrator password menu.
  3. Enter the authentication key generated while creating the server.
  4. Click [Check password].
  5. Check the password.

Test the application server connection

To test whether you can successfully connect to the application server using Remote Desktop Connection:

  1. Navigate to Start > All programs > Accessories > Remote desktop connection.
  2. Enter the public IP address for server access in the Computer field.
    clouddbformssql-start-appconnect_vpc_ko
  3. Click [Connect].
  4. When the user credentials popup appears, enter the ID and password obtained from the administrator password check.
    clouddbformssql-start-appconnect2_vpc_ko
  5. Click [OK].
  6. When the Remote Desktop Connection warning window appears, click [Yes (Y)].
    clouddbformssql-start-appconnect3_vpc_ko
Note

You can change the password by selecting Ctrl + Alt + End > Changed a password.

Create MSSQL Server

Some of the services provided by NAVER Cloud Platform require you to go through the subscription procedure from the console before the start of actual usage. However, Cloud DB for MSSQL starts being used as soon as you create an MSSQL server, without a separate service subscription step. To create a MSSQL Server in the NAVER Cloud Platform console:

Note
  • This section provides a brief overview of how to create a DB Server. For more instructions about the full creation process, see Create an MSSQL server.
  • Because Cloud DB for MSSQL does not require a separate subscription step, there is no separate unsubscription procedure. To stop using Cloud DB for MSSQL and prevent further charges, delete the MSSQL server you created and are operating. To delete MSSQL server, see Delete MSSQL server.
  1. In the VPC environment on the NAVER Cloud Platform console, navigate to Services > Database > Cloud DB for MSSQL.
  2. Click the DB Server menu.
  3. Click [Create DB Server].
  4. When the service subscription interface appears, enter the required information and click [Next].
  5. Click [Create].

Set ACG

To set up Access Control Group (ACG) for MSSQL servers from NAVER Cloud Platform console:

Note

This section provides a brief overview of how to set up ACG. For more information on how to set ACG, see ACG user guide.

  1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MSSQL.
  2. Click the ACG menu.
    clouddbformssql-start_acg_vpc_ko
  3. Click and select the ACG to set up, and then click [Set ACG].
    • Select the automatically created MSSQL Server ACG.
  4. Enter the ACG name of the application server in Access source.
  5. Enter the port number in Allowed port.
  6. Click [Add].
  7. Click [Apply].

Test MSSQL Server connection

To test if you can connect properly to the created MSSQL server by installing the MSSQL client:

  1. Check the DNS name, DB connection port, account ID, and password required to connect to the MSSQL server.
    • Navigate to Services > Database > Cloud DB for MSSQL > DB Server.
    • Click the service name of the MSSQL server to connect to, and then check the following items.
      • DNS name: Check the private domain.
      • DB access port: Check the DB connection port.
    • In [DB management] > DB User management, check the account ID and password.
  2. After connecting to the application server, download SQL Server Management Studio (SSMS) to install it.
  3. Access the SSMS.
  4. Enter the DB server information in the Connect to Server popup.
    database-9-1-501_ko
    • Server type: Database engine.
    • Server name: DNS name and DB connection port (separated by a comma).
      • DNS name: The private domain of the MSSQL server to connect to.
      • DB access port: The connection port of the MSSQL server to connect to.
    • Certificate: SQL server certificate.
    • Login: The user account name of the MSSQL server to connect to.
    • Password: The user account password of the MSSQL server to connect to.
  5. Click [Connect].

Data migration (backup/recovery)

In order to migrate the database operated on-premise to Cloud DB for MSSQL (CDB-MSSQL) or move it from CDB-MSSQL to on-premise, you need to create a window application server. Data migration is performed using the backup and recovery of the SQL server, and you can either proceed with it after uploading the database operated on-premise on the application server or backup the CDB-MSSQL to the application server.

Note
  • The application server has to be in the same VPC network environment as CDB-MSSQL.
  • For recovering the backup using the application server, you need to set "password protected sharing off" at the network and sharing center of the application server.
  • Properly setting the ACG is required to protect the data of the application server. (Application server TCP Port 445 must be open.)
  1. Create an application server

    • Check VPC and subnet of the CDB-MSSQL.
      clouddbformssql-start-vpc_backup

    • Check VPC and Subnet of the application server.
      clouddbformssql-start-vpc_appserver

    • After creating the backup folder on the created application server, set sharing (Everyone, Read/Write).
      clouddbformssql-start-vpc_backup-everyone

    • Turn off the password protected sharing.

      • Control Panel > Network and Internet > Network and Sharing Center > Advanced sharing settings
      • Password protected sharing option > Select Turn off password protected sharing > Apply.
        clouddbformssql-start-vpc_password-setting
  • Set ACG of the application server
    • The inbound port 445 of the application server should be open to VPC range.
      clouddbformssql-start-vpc_appserver-agc-setting_1
  • Set the MSSQL Server's ACG
    • The outbound port 445 of the MSSQL server must be open to the VPC range.
      clouddbformssql-start-vpc_appserver-agc-setting_2

  1. Backup (sample)

    backup database testdb to disk ='\\10.0.1.9\backup\testdb.bak'

    database-mssql-backup-sample_ko

  2. Recovery (sample)

    • When restoring the database, you should recover it to d:\data folder. The database recovered to other directories does not guarantee stability.
    restore database testdb2 from disk ='\\10.0.1.9\backup\testdb.bak'
with
  move 'testdb' to 'd:\data\testdb2.mdf',
  move 'testdb-log' to 'd:\data\testdb2_log.ldf'

    clouddbformssql-start-vpc_restore_ko

Caution
  • The on-premise recovery target database must be backed up with the CDB-MSSQL administrator account as the database owner before it can be used normally.
  • Even if the on-premise and CDB-MSSQL user accounts are the same, if the SIDs are different, the database cannot be used after recovery.
  • See the following script to set the SIDs to be the same and then back up from on-premise and restore to CDB-MSSQL.

SID troubleshooting

This guide walks you through troubleshooting issues due to SID inconsistency in data migration (backup/recovery).

  1. Restore by changing db_owner in the original server (ON-PREMISE → CDB-MSSQL)
Task server Execution script descriptions and reference T-SQL Query examples
CDB-MSSQL
  • Check the SID using a T-SQL command.
    • SELECT SID FROM master.sys.sql_logins WHERE name='dbuserid'
      • dbuserid: Default administrator account you create.
  • SID check result.
    • 0xDBB5F4FE1DBC594FBF806E807AFE72D6
ON-PREMISE
  • Use a command to create a login on the local target server for the migration (the password does not need to match the source server's password).
    • CREATE LOGIN [dbuserid] WITH PASSWORD = 'P@ssw0rd', SID =0xDBB5F4FE1DBC594FBF806E807AFE72D6, DEFAULT_DATABASE=[master], CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF
  • Grant sysadmin permissions to the existing database owner to prevent errors from occurring when the owner changes.
  • In the migration target database [userdatabase], change the database owner to the user created above.
  • On the local target server, access the SA or administrator account and perform the following commands.
    • USE [userdatabase]
      GO
      EXEC sp_changedbowner 'dbuserid'
      GO
  • Back up the database to be migrated.
    • BACKUP DATABASE [userdatabase] TO disk = 'C:\temp\userdatabase.full'
  • Move the backed up database backup file to an application server created on the same VPC range.
CDB-MSSQL
  • Before running RESTORE, check the list of logical files in the backup file.
    • RESTORE FILELISTONLY FROM disk='\\10.0.0.7\backup\userdatabase.full'
      • 10.0.0.7: VPC IP address of the application server you created.
  • Restore the database to the d:\data folder using the confirmed file list (an error may occur if a file with the same name already exists).
    • RESTORE DATABASE userdatabase FROM disk='\\\10.0.0.7\backup\userdatabase.full'
      with
      MOVE 'userdatabase' TO 'd:\data\userdatabase.mdf', MOVE 'userdatabase_log' TO 'd:\data\userdatabase.log.ldf'
      • An error occurs if a file with the same name already exists.
  1. Add db_owner to the server to be recovered (CDB-MSSQL → CDB-MSSQL).
Task content, server, and user Execution script descriptions and reference T-SQL Query examples
  • Check the AServerAdmin SID
  • Run as AServerAdmin on the source server (AServer)
  • Run as AServerAdmin on the source server (AServer) to check the SID.
    • SELECT SID FROM master.sys.sql_logins WHERE name='AServerAdmin'
  • SID check result.
    • 0xB1CFFCE0AEF0234FB49273E7DD713FFA
  • Restore AServerDB (RESTORE)
  • Run as BServerAdmin on the restore target (BServer)
  • Create AServerAdmin on BServer with the same SID as AServerAdmin on the source server
  • Run as BServerAdmin on the restore target (BServer)
  • The password does not need to match the password used on AServer.
    • CREATE LOGIN [AServerAdmin] WITH PASSWORD='P@ssw0rd', SID=0xB1CFFCE0AEF0234FB49273E7DD713FFA, DEFAULT_DATABASE=[master], CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF
      GO
  • Grant the db_owner role to BServerAdmin in AServerDB restored on BServer
  • Run as AServerAdmin on the restore target (BServer)
  • Create a BServerAdmin user in AServerDB and assign the db_owner role.
    • USE AServerDB
      GO
      CREATE USER BServerAdmin FOR LOGIN BServerAdmin
      GO
      ALTER ROLE [db_owner] ADD MEMBER BServerAdmin
      GO
  • Delete the AServerAdmin account created temporarily on BServer
  • After you log in as BServerAdmin on the restore target (BServer), run the command
  • After running KILL on every SPID returned by the SELECT SPID FROM sysprocesses WHERE loginame = 'AServerAdmin' command, delete AServerAdmin.
    • DROP LOGIN [AServerAdmin]
      GO
Note

If the database is unavailable due to a mismatched SID, you can delete the database from the console.

Set Agent Job

On a high-availability server created, the SQL Server Agent Job must be appropriately configured to operate only on the principal database server. Otherwise, the Agent Job copied to the Mirror server operates for the database being mirrored, causing an error.
Refer to the below script to make the settings. The script works in a redundancy configuration server and also on a stand-alone server.

  1. Create database and change option to enable redundancy

    • If the database recovery level is Simple, database redundancy is not available.
    CREATE DATABASE DBTEST1
GO
ALTER DATABASE DBTEST1 SET RECOVERY FULL 
GO

USE DBTEST1 
GO

CREATE TABLE TBLX 
(
IDX INT IDENTITY(1,1)
,CDATETIME DATETIME
,CINT INT
)
GO

CREATE OR ALTER PROC USP_A 
AS 
INSERT INTO TBLX (CDATETIME, CINT) VALUES (GETDATE(), 1)
GO

  2. Set step for Agent Job

    • Specify the database and run it with exec ('databasename.schemaname.userproc').
      clouddbformssql-start-vpc_agentjob_ko
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
DECLARE @DB_NAME VARCHAR(1000) = 'DBTEST1'

IF EXISTS (
    SELECT *
    FROM SYS.DATABASES A 
    WHERE HAS_DBACCESS(DB_NAME(A.[DATABASE_ID])) = 1 
         AND DB_NAME(A.DATABASE_ID) = @DB_NAME
) 
BEGIN 
    EXEC ('DBTEST1.DBO.USP_A')
END 
ELSE 
BEGIN 
    SELECT 'MIRROR SERVER JOB SKIPPED'
END
Business Registration Number: 129-86-31394 E-commerce Permit Number:No. 2009-GyeonggiSeongnam-0510
CEO: Kim Yuwon Address: NAVER Green Factory 17th–26th Floors, Buljeong-ro 6, Bundang-gu, Seongnam-si, Gyeonggi-do 13561, Republic of Korea. Customer Support Number: 1544-5876
© NAVER Cloud Corp. All Rights Reserved.