How to MS SQL DBMS
Introduction
In this how to document it is described how you can order a MS SQL DBMS and the actions which can be executed through the Swisscom portal or vRA UI.
How to execute day 2 actions stored procedure through remote SSMS or SSDT connection can be found here SSMS actions.
For details about managed OS or virtual machine refer please to the user guide Managed OS respectively Virtual Machine.
This user guide will focus on the SQL service details. If nothing else is noted, the guide is valid for all available versions. For topics which are not covered in this user guide you can check the FAQ section.
Create a MS SQL Server
Before start ordering, it is recommended to read the technical product description MS SQL DBMS, in order to understand the differences between the different services and their capabilities.
The blueprints can be found typing in search for instance sql or choosing in service filter:
- Database Services: unmanaged DBMS
- Managed Services: managed DBMS, DBMS DBA mode, SQL AlwaysOn
Once you have chosen the service which fit your needs and you are through the first deployment dialog, you will land on the configuration page. This is divided into two tabs General and Storage.
VM Sizing
In order to avoid capacity or performance bottlenecks it is recommended to carefully plan the resources of your VM.
CPU and Memory
Make sure that you choose the CPU and Memory according to your existing environment or software vendor requirements/recommendations.
Furthermore take in count scale limits which are given through Microsoft, see also scale limits.
Storage
For the type and sizing of the storage please consider:
- Storage performance: check IoPS details in Catalogue of services to chose the right option
- Storage size:
- size your volume with enough space to host the data and try to estimate the data growth.
- for sizing please consider that data, transaction logs and tempDB are stored on separate drives.
- if Networker ABB (agent based backup) is not available on your tenant, consider that 5 backup generations will be stored on drive X:. Even if backup compression is enabled, depending on the stored data types, each backup will take at least about 50% of the original data size (for instance the backup of a 100 GB DB will be about 50/60 GB, but can increase dramatically if the data is in pdf format).
SQL specific settings
For MS SQL Server you can specify the following specific settings:
| Field | Explanation | Remark |
|---|---|---|
| SQL Edition | Standard, Developer and Enterprise Edition are available. | SQL Server editions please refer to the Microsoft documentation. |
| Use Swisscom SQL license | YES: give the choice to obtain the licenses through Swisscom. NO: BYOL (bring your own license) model. | |
| SQL Collation | Collation Latin1*General_CI_AS ( Windows Collation) is set as default option in the drop down list. All others case insensitive collations are available. | From case sensitive collations you are able to select Latin1*General*BIN. For more information about SQL collation please refer to Microsoft documentation. |
| SQL Port* | Default port is 1433. | Available for the unmanaged services only. |
| SQL DBA Name | Name of the SQL login which will be automatically created to allow the first remote access. | |
| SQL DBA Password | Set SQL DBA Password. Swisscom will not record the password. | Password Guidelines:
|
| Agent based backup protection group | None: Networker SQL ABB is not available in your tenant. Policy Name: available policies. | Not available for the unmanaged service only. Backup description |
| Resource Domain | The resource domain is mandatory for managed services. |
When all the mandatory settings are set, click on Submit button to request the MS SQL Server. As it is a custom SQL installation based on best practices, the ordering process might take some time.
For the unmanaged DBMS, as soon as the order is completed, you can login to your VM with the user, you have specified and open SQL Management Studio. The SQL Server is now installed with the settings you have specified before.
Note: Please make sure that you do not delete disks from the Blueprint template. The SQL Server installation expects the disks to be available as pre-configured in the Blueprint. However, you can specify the disk sizes.
Delete a MS SQL Server
To delete a MS SQL Server click on the tab My Items. On the desired server, click on Actions and select Destroy. A new window appears where the deletion has to be confirmed.
Actions for MS SQL Server (Day 2)
All MS SQL services
The following actions are available for all flavours of MS SQL services.
Create a Database
Create a database on the target server along with a new DB user with db_owner rights. The database data files will be configured based on the numbers of the vCPU.
- Click on Actions and select Create MS SQL Database
- Define the name of the database to be created
- Fill in the username/password for the new db_owner role member
- Define minimum and max size for the data files
Note: For unmanaged SQL Server you will have to input an existing VM administrator user and password. No DB user will be created.
Delete a Database
- Click on Actions and select Delete MS SQL Database
- Select the database to delete
- Confirm
Please note that the system loads all DBs which are on the DBMS. Therefore it could take a while until the drop-down shows all available DBs.
Note: For unmanaged SQL Server you will have to input a VM/SQL administrator user and password.
Unmanaged SQL
The action is available for unmanaged SQL server only.
Start/Stop MS SQL Service
For managed or DBA mode MS SQL server please set the VM in Customer Maintenance Mode to be able to reboot it.
- Click on Actions and select Start or Stop DB Service
- Fill in the username/password of an admin
- Select the service you want to change and the desired state
The operation is idempotent which means it does not check for the current status. It will just set it to the desired state you have selected in the dropdown. If the service is already in the desired state, there will be no change.
Managed SQL services
The following actions are available for managed and DBA mode MS SQL server only.
Update MS SQL Server configuration
A predefined set of instance configuration can be modified. The list of the allowed configurations can be found here (Permissions Model). Refer please to the related Microsoft documentation for more detailed explanations.
- Select Update MS SQL Configuration Options.
- In drop-down configuration options select the setting you want to modify (see description for a more user friendly naming). Refer to TPD - Permission Model for the complete list of settings which can be modified.
- Set the wished value and submit.
Note: The field restart needed is an information if the MS SQL server need to be restarted to enforce the configuration.
Set CID Status
Important for customers in the banking sector: To ensure FINMA compliancy regarding CID, Swisscom must know from the customer which VM to secure. If in doubt, please contact your Customer Service Manager at Swisscom.
Import Database
To get an overview of the capabilities and restrictions of this action please refer to the Technical Description (Import/Export DB's).
Select Import MS SQL Database day2 action in the Portal.
- Type the source S3 bucket and the backed-up DB name, e.g. mybucketname/myfilename
- Input the desired DB name. This can be different to the original DB name of the backup
- Type the password for the user which will be created as member of the role db_owner
Note: A S3 bucket is case sensitive. You will get an error if there is a capital letter mismatch.
Permission handling after import
During the DB import a dbo_mydbname with db_owner rights will be created. In order to grant access to the other logins with this user, first you will have to map the logins to the imported DB.
Note: This will not work through the SSMS GUI. In the DB context you can execute the following T-SQL command in order to map other logins:
CREATE USER myuser FROM LOGIN mylogin
CREATE USER [mydomain\myWinUserGroup] FROM LOGIN [mydomain\myWinUserGroup]
Once the login is mapped, you will able to grant the desired permissions through the SSMS GUI.
Mass Migrate MS SQL Databases
An user can use Mass Migrate MS SQL Databases feature to move multiple or very large databases to Swisscom's Enterprise Service Cloud Services.
Import MS SQL Database vs. Mass Migrate MS SQL Databases
Import MS SQL Database:
Prerequisites
- Customer creates a full backup
- Customer switches off the current system
- Customer copies the full backup to Swisscom's S3
Process
- Customer starts migration using the day2 feature (one database at a time) – one database gets restored and its privileges sorted. This step needs to be repeated for each database a customer wants to migrate
- ESC environment is ready to go online
- Process and downtime depend on the size and number of databases as process has to be executed sequentially
Mass Migrate MS SQL Databases:
Swisscom recommends industry best practice "Ola Hallengren" maintenance solution for MS SQL servers.
Prerequisites
- Customer installs locally on the system to be migrated the following customised "Ola Hallengren" SQL scripts for maintenance. To download the scripts, right mouse click here and choose
Save link as... - Customer creates a full backup of all databases targeted for migration
- Note: Customer can keep his current system online. No downtime is needed at this time.
- Customer copy the backups to Swisscom's S3
Process
- Customer starts the day2 action with non-recovery switch and all databases get restored however the databases stay non-recovered
- Customer has the opportunity one or a few days later to make a differential backup and/or log backups. Starts again day2 action and restores with non-recovery databases to bring them closer to the live database version. This step can be repeated as many times as needed. It is also possible to add new database into the process of migration by creating a full backup just for that database.
- On the migration day:
- Customer makes the last differential backup if needed (in case of a big database or many days behind) and log backups for all DBs
- Customer switches off the current system
- Customer copies unaltered backup folder with files to designated Swisscom's S3
- Customer starts day2 action and restores the databases with recovery
- The whole process can last a few days, the downtime though can be very short, usually under 30 minutes regardless of the size of databases or of their amount. The process works in parallel and all the databases will go online at the same time.
Very Important Note: Origin and destination servers MUST have the same collation!
Process in detail
Setup on the customer's side
- Please remove databases you want to migrate from any existing backup process
- Run provided slightly amended "Ola Hallengren" scripts but please update the @BackupDirectory setting to your environment requirements. Start with 1st script.
- The script will create a migrationDB locally that is needed for migration – Please do not delete or alter it!
- Run backups using commands provided in the 2nd script
- Please do not move, add, alter or rename created backups afterwards! The folder and files in this folder need to stay as they were created using the backup commands.
Copy to Swisscom's S3
- Please copy the whole backup folder - @BackupDirectory to the designated S3 bucket using the provided credentials.
Triggering 2nd day action – preparation
This step can be repeated as many times as needed.
- Choose the 2nd day action Mass Migrate MS SQL Databases.
- Fill in the fields.
- Backup folder:
- Important: Server name is case sensitive!
- Example: transfer/YourServerName01
- Backup folder:
Triggering day2 action – going live.
This command will restore all backups that haven’t been applied, sort privileges, clean-up environment and bring database(s) online.
Export Database
To get an overview of the capabilities and restrictions of this action please refer to the Technical Description (Import/Export DB's).
- Select Export MS SQL Database
- Type the name of the backup file you want to create
- Type the destination S3 bucket
Import database from share
If you are not familiar with ESC and/or backup / restore MS SQL Server techniques make sure you read the ESC TPD first.
Prerequisites
- Make sure you can use the SA-ESCCU01-Mgmt account or you own an account member of the role group RG_ESCCU01-Admin, see here in order to be able to browse the domain structure.
- Make sure you own a MS SQL account member of the customerAdmin MS SQL server role.
- Windows OS VM hosting the network share is joined in the same resource domain as the SQL server.
- Make sure you can access the MS SQL target system remotely by MS SQL Management Studio (SSMS).
- Make sure you have enough space on the MS SQL target system to host the new database.
Prepare source system:
Get a Managed Windows Server in the same tenant and business group and set the Managed Windows Server in tempAdmin mode
Create a directory on a local drive (not C:) to be used as import source or export target
Get SQL service account information executing the following T-SQL command in SSMS:
select service_account FROM sys.dm_server_servicesMake sure to be logged to the VM hosting the share with the domain account able to browse the directory. Grant the SQL service account with read permission for backup and, if necessary, write (for restore) permissions on the shared folder.
Enable sharing for the directory and write down the UNC path
Copy the backup file you want to use into the directory and note down the full UNC path including the file name
Execute backup command
For a complete guidance about T-SQL backup command refer please to the official MS documentation.
The example below is to understand a general guide and cannot cover all possible scenarios. It is recommended to have a least a basic understanding how SQL backup and restore work.
A backup file include the data and log file paths of the source DB system, which may conflict with the default data and log path of the target DB server (see path description here. Therefore it is recommended to check first the original file path and execute then a restore command with the MOVE hint (Microsoft reference).
Procedure example to be executed in SSMS:
Display the backup file information (Microsoft reference):
RESTORE FILELISTONLY FROM DISK = "\\mySourceServer\myShare\myFileBackup.bak"compare the the file paths with the file paths described in the TPD (see link above)
if the path don't match, include the MOVE hint to redirect the data and log files to the default paths
MOVE '' TO 'D:\SQL_Server\Default\UserData\.mdf/ndf'for log files you have to move it to a different directory
MOVE '' TO 'E:\SQL_Server\Default\UserLog\.ldf'now write the complete command for a db restore like this
RESTORE DATABASE <databaseName> from disk = '<your UNC path' WITH MOVE '<name>' TO 'D:\SQL_Server\Default\UserData\<databaseName>.mdf', MOVE '<LogicalFileName>' TO 'D:\SQL_Server\Default\UserData\<databaseName>_1.ndf', MOVE '<LogicalFileName>' TO 'D:\SQL_Server\Default\UserData\<databaseName>_2.ndf', MOVE '<LogicalFileName>' TO 'D:\SQL_Server\Default\UserData\<databaseName>_3.ndf', MOVE '<LogicalFileName>' TO 'D:\SQL_Server\Default\UserData\<databaseName>_4.ndf', MOVE '<LogicalFileName>' TO 'D:\SQL_Server\Default\UserData\<databaseName>_5.ndf', MOVE '<naLogicalFileName>' TO 'E:\SQL_Server\Default\UserLog\<databaseName>_6.ldf' GOexecuting the command the DB import will start and take a while depending on DB size
prior to execution the available space on the devices will be checked, if you do not have enough space an error will be outputted. You can put the managed SQL server in Customer Maintenance Mode and increase the disks appropriately
Fix permissions on imported DB
The login executing the restore command will be automatically added to the db_owner role of the imported database
The database users (not logins) of the source system will be imported with the restore command and possible generate the "orphaned user" effect. You will have to distinguish between database user types here:
Windows Domain users: if the logins are already configured on the target system, the logins will be automatically mapped to the users. If the logins are not configured, the users won't work. You can create the logins or clean up the database users.
SQL logins: if the logins are already configured on the target system and you want to use it further, you will have to establish the login-user mapping again with the following T-SQL command
ALTER USER <user_name> WITH Login = <login_name>;Please refer to the official Microsoft documentation.
If the login doesn't exist on the target DB server you can create it with the same password and use the procedure above to map it to the database user or you can drop the existing database user and create a brand new login and map it to the database.
if you want to add a service account for your application from the resource AD you can do it with the following T-SQL command:
USE [master] GO CREATE LOGIN [<domain>\<service account>] FROM WINDOWS WITH DEFAULT_DATABASE= [master] GO USE [<databaseName>] GO CREATE USER [<domain>\<service account>] FOR LOGIN [<domain>\<service account>] GO USE [<databaseName>] GO ALTER ROLE [db_owner] ADD MEMBER [<domain>\<service account>] GO
Manage Permissions
Some actions to grant or revoke permissions like adding a login to a server role cant be executed by members of the customerAdmin server roles, see Permission model directly through SSMS actions with stored procedures.
Alternatively this actions can also be executed through the UI.
- Select then Update MS SQL Permissions
- In Action Type drop-down select the desired action
- In field Login Name input the SQL login or Windows login name. Please note that for domain users or group you don't need to put the name in brackets.
Clone Database
A clone of a database can be created by running a stored procedure as:
exec [scs_AdminDB].[Customer].[sp_Clonedatabase] @db = 'Sample_DB'
or as a day2 action Clone MS SQL Database
In both cases, following will be running in the background:
- DBCC Clonedb command, creates a data-less database as described in the MS Documentation and
- delegates the same ownership of the original database to the cloned database.
The cloned database will be created in the read-only mode and also will have date and time suffix in the name in order to create several versions.
Transparent Data Encryption
Transparent Data Encryption – TDE is encryption at rest and can be used only with Enterprise Edition or Developer Edition license of ESC Managed MS SQL Server.
Please find below operations you can use to encrypt and decrypt databases.
Note: The SQL VM has to be in Customer Maintenance Mode for this action to work.
Enable TDE
There are 2 ways to enable the TDE:
- T-SQL Command:
exec [scs_AdminDB].[Customer].[sp_TDE_Enable_Disable] @enable = 1, @MasterPassword = 'xxxxxxxxxxxxxxxx' - Using the Set MS SQL TDE day2 action in the portal: Choose Yes from drop down box and click submit.
Note: The MS SQL has to be in Customer Maintenance Mode for this action to work.
Note: Please make sure to keep all your passwords in a safe place as Swisscom does not store them.
Encrypt Database
Note: The TDE needs to be enabled for this to work. Note: The MS SQL has to be in "Customer Maintenance Mode" for this action to work.
To encrypt databases there are 2 ways:
- T-SQL Command:
exec [scs_AdminDB].[Customer].[sp_TDE_EncryptDB]<br> @DatabaseToEncrypt = 'CustomerDB', @CertificatePassword = 'xxxxxxxxxxxxxxxxxxxxx', @OutputPath = 'C:\ProgramData\DBCert' - Using the Set MS SQL TDE day2 action in the portal: Select the Database you want to encrypt and set the Certificate Password and confirm it. Then click Submit.
Decrypt Database
Note: The TDE needs to be enabled for this to work. Note: The MS SQL has to be in "Customer Maintenance Mode" for this action to work.
To decrypt one or all databases there are two ways:
T-SQL Command
decrypting one database:
exec [scs_AdminDB].[Customer].[sp_TDE_DecryptAllDB] @alldatabases = 0 @onedbname = 'CustomerDB'
- decrypting all the databases:
exec [scs_AdminDB].[Customer].[sp_TDE_DecryptAllDB] @alldatabases = 1
- Using the Set MS SQL TDE day2 action in the portal: Select Database and Choose Encrypt Database – No and Submit.
Remove TDE
Note: The MS SQL has to be in "Customer Maintenance Mode" for this action to work.
To decrypt all databases and disable TDE on the server there are 2 ways:
- T-SQL Command:
exec [scs_AdminDB].[Customer].[sp_TDE_Enable_Disable] @enable = 0 - Using the Set MS SQL TDE day2 action in the portal: Enable TDE switch to No and submit.
Note: After running this command, it's necessary to have a log backup and to restart the SQL Server service. Running the command through the cloud UI portal, will do that as a part of the process.
TLS Encryption
Enforcing TLS Encryption
Note: The MS SQL has to be in Customer Maintenance Mode for this action to work.
This is possible using the day2 action Set MS SQL TLS Encryption in the portal. If the customer sets the option Force TLS Encryption in the dialog to Yes and submits the request, it will:
- Create a self-signed certificate
- Reconfigure the Networking Protocol to use this certificate
- Set enforce property to yes
- Restart SQL Service
The Certificate will be created with the following properties:
- The Subject property of the certificate will be (FQDN) of the server.
- Certificates will have a validity period of 1 year
- Hash Algorithm: SHA256
- Key Length: 3072bit
- Friendly Name: SCS_SSC_TLS
The certificate will be created on the local computer and trusted root certificate store. The certificate usage will be defined for server authentication with an appropriate validity period. Enhanced Key Usage property of the certificate will be 1.3.6.1.5.5.7.3.1. In order for SQL Server network protocol to be able to use this certificate for encrypting connections, the KeySpec option of the cert will be set to AT_KEYEXCHANGE. KEY_USAGE property will contain key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
Removing TLS Encryption
If Force TLS Encryption is set to "No" and the request is submitted, the action will:
- Reconfigure the Networking Protocol to not use Self-Signed Certificate
- Set enforce property to "No"
- Delete Certificate with friendly name SCS_SSC_TLS from both repositories
- Restart SQL Service
Warning: Both actions are requiring SQL server service to be restarted (it will happen automatically).
Install MS SQL Cumulative Update
This action allows users to install a CU of their choice. A list of available CU's is generated based on what is installed on the instance. It's available for MS SQL 2017 and MS SQL 2019 instances.
Pre-requisites
Before you start make sure that the instance is set to the Customer Maintenance mode.
Important notice
Important details on the Install Cumulative Update action:
- Once the action has completed successfully, it can take up to 12 hours for the list to be updated.
- Normally the latest CU is NOT offered due to limitations of other components (DAM agent).
- This action triggers instance restart.
Managed Agent Based Backup
Used to change Agent Based Backup Policy. If there was no Policy set (Current Policy is None), it will install and activate Agent Based Backup.
- Installation and Activation will be triggered if Current Policy is "None" and a New Agent Based Backup Policy is selected
- Change of Policy will be triggered if DBMS was already protected by Agent Based Backup (Current Policy is not "None") and a New Agent Based Backup Policy is selected
Note: Only changing and enabling Agent Based Backup is supported.
Microsoft OLE DB Provider for DB2
Note: The MS SQL has to be in Customer Maintenance Mode for this action to work.
The Microsoft OLE DB Provider for DB2 lets customers create linked servers to IBM DB2 Databases. This provider is not present by default on Managed MSSQL Services and has to be installed separately.
In order to install this provider, under Set Provider for MS SQL Linked Server day2 action in the Cloud UI portal, choose the DB2 provider from the drop-down list:
The provider that will be installed is a part of common SQL Server Feature Pack. On Managed MS SQL Server 2016 the Microsoft OLE DB Provider for DB2 Version 5 will be installed. Later on, on Managed SQL Server 2017 and newer versions, the OLE DB Provider for DB2 Version 6 will be installed.
Oracle Database Client for Microsoft Windows
Note: The MS SQL has to be in Customer Maintenance Mode for this action to work.
Oracle Database Client for Microsoft Windows can be installed by choosing the adequate option from the provider name drop down list. This client is not present by default on Managed MSSQL Services and has to be installed separately. There are two different versions offered for installation. In order to install this provider, under Set Provider for MS SQL Linked Server choose the Oracle provider from the drop-down list. After a while following fields will be shown: TNSNAMES.ORA, LDAP.ORA and SQLNET.ORA.
The three text fields should contain configuration text that is needed to create linked DBs using MS SQL Management Studio. By choosing the Submit button the whole content of text boxes will be copied to the config files located on the local machine (Path: C:\App\client\network\admin).
Note: No parsing of the content will be done during the configuration. The content will be saved in all three files as it is, without any reformatting or checks. For reconfiguring the config files, please refer to the reconfiguring chapter of this manual.
tnsnames.ora
This tnsnames.ora file is a configuration file that contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.
A net service name is an alias mapped to a database network address contained in a connect descriptor. A connect descriptor contains the location of the listener through a protocol address and the service name of the database to which to connect. Clients and database servers (that are clients of other database servers) use the net service name when making a connection with an application.
For more information visit Oracle Documentation.
ldap.ora
The ldap.ora file contains directory usage configuration parameters. Use the DIRECTORY_SERVERS parameter to list the host names and port number of the primary and alternate LDAP directory servers. For more information visit Oracle Documentation.
sqlnet.ora
The sqlnet.ora file enables you to:
- Specify the client domain to append to unqualified names
- Prioritize naming methods
- Enable logging and tracing features
- Route connections through specific processes
- Configure parameters for external naming
- Configure Oracle Advanced Security
- Use protocol-specific parameters to restrict access to the database
For more information visit Oracle Documentation.
Reconfiguring the Oracle client
To reconfigure the client, simply start the installation of the corresponding client again and the fields will be filled up with already existing content of the files on the local machine.
Upload files to MS SQL
Previous to start the upload action make sure that the files are uploaded on your S3 bucket. All files and folders in the bucket will be copied to the target system onto D:\FileRepository folder.
Please refer to the TPD for the list of the allowed file types and actions.
- On the target VM choose Upload files to MS SQL
- Input the name of the S3 bucket as YourBucketName
- Choose "cleanup" yes or no:
- Yes: all files already stored in the repository folder will be deleted
- No: files already stored in the repository will be not deleted, but existing files with the same name will be overwritten
The action result will be logged into the SQL server log. The log file can be retrieved executing scs_AdminDB.customer.sp_viewSqlLog stored procedure (please see here).
Note: Please note that non allowed files have being deleted along with files which has been detected by the antivirus as infected.
Usage samples
Commands to use the uploaded files can be execute remote through SSMS or SSDT and if WMI access is enabled (see here)also through remote powershell scripting.
The examples below illustrate possible usage of the files.
Bulk insert
Example of bulk insert executing through SSMS, works on Managed SQL and DBA Mode:
BULK INSERT mydb.dbo.mytable FROM 'D:\FileRepository\datafile.csv'
WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ';');
For a comprehensive guide about the BULK INSERT statement refer please to the Microsoft documentation.
Execute SQL script with Powershell in SSMS
Example of executing an sql script through SSMS and powershell (xp_cmdshell must be enabled), works on DBA Mode only:
DECLARE@cmd VARCHAR(2000)
SET @cmd = 'powershell -command "invoke-sqlcmd -inputfile D:\FileRepository\test.sql"'
EXEC xp_cmdshell @cmd
Execute an SQL script remotely with Powershell
The SQL queries can be execute also through remote powershell once the WMI permissions are set. Works on DBA mode only.
Example:
Invoke-SqlCmd -ServerInstance _myinstanceIP_ -Query "xp_cmdshell 'sqlcmd -S . -i D:\FileRepository\test.sql"
Set MS DTC Configuration
Set MS DTC Configuration option is providing possibility for the user to enable and configure DTC options of Operating System. Configuration options available are:
- Remote Administration Access (Enabled – true/false) - this option will enable and disable other dependant sub settings
- Remote Client Access (Enabled – true/false)
- Inbound Transactions (Enabled – true/false)
- Outbound Transactions (Enabled – true/false)
- Authentication Level – (NoAuth, Mutual, Incoming caller)
- LU Transactions (Enabled – true/false)
- XA Transactions (Enabled – true/false)
Manage MS SQL Assembly Certificate
This action will allow you to load or drop a certificate inside the target DBMS. Please note that just one certificate per DBMS is allowed, for more information refer to the TPD.
Pre-requisites
Before you start this 2nd day action make sure that:
- You have uploaded the certificate under D:\FileRepository using the 2nd day action "File Upload MS SQL"
- The certificate has a file suffix .cer
Start the action, in the drop down "Action Type" choose the desired action:
Load (upload a certificate)
- Certificate name: the name which will be used to register the certificate into the master DB.
- Certificate File Name: the name of the certificate file uploaded under D:\FileRepository.
Drop (drop existing certificate)
There are no fields to be filled. The action will drop the configured certificate and login.
Load MS SQL CLR Assembly
This action allow you to upload an assembly which has be signed through a certificate which has previously loaded into the DBMS.
Pre-requisites
Before you start the action make sure that:
- You have set the option clr enabled to 1 (go to Update MS SQL Configuration Options and choose IsSqlClrEnabled. Set to 1 if needed)
- You have uploaded the assembly under D:\FileRepository using the action "File Upload MS SQL"
- The assembly has a file suffix .dll
- The assembly is certificate signed
Start the action:
- In the drop down Database Name choose the target database
- Assembly Name: the object name which will be used to register the assembly in the database
- Assembly File Name: the name of the assembly file uploaded under D:\FileRepository.
Create Extended Events Session
The prerequisites for Extended Events Sessions are met on MSSQL DBMS, DBA Mode and Always On deployments. Extended Events Sessions can be created, altered and deleted in SSMS. However, there are some basic rules to use this feature with our product.
- Only users with customer Admin role can create Extended Events Sessions
- Only event file is allowed as Session Data Storage (no ring buffer)
- The file name on server must be like X:\ExtendedEvents\<NAME>.xel
- The maximum file size cannot be bigger than 1 GB
- The maximum file rollover count must be exactly 6
When defining the capture and the filters please consider the resources needed to capture those events, since this could affect the performance of your workload. Please note that, when creating multiple Extended Events Sessions, every session can use up to 6 GB of diskspace on X: drive. When a session is deleted, the generated .xel-files are automatically removed within one day.
Create MS SQL Server AlwaysOn
Under catalog, managed services, you will find the icon for "Managed MS SQL AlwaysOn 2016/2017/2019".
In the first step, you will have to insert parameters similar to an order of a Managed MS SQL Server 2016/2017/2019.
In the first screen you can define overall settings, please note the following difference:
- SQL DBA name and password will be the same on both system
- AlwaysOn SQL Listener can be up to three
- Resource Domain is mandatory
In the two positions referring to the VM settings you can specify additional configurations. Please note the following points (please refer to the technical product description for deeper understanding of the options):
- CPU minimum value is 8 up to 22
- Memory minimum value is 8 up to 704 GB
- Patching Windows can't be the same for the nodes
- Service level can be standard or advanced only (if you choose "basic" the request will end with an error)
- Nodes location can't be the same
Actions SQL AlwaysOn (day 2)
THe day 2 actions for a managed MS SQL AlwaysOn are executed at deployment level. This means that you will find them clicking on the deployment instead of each individual node's VM.
At VM level you will find the actions for managed SQL mentioned in the previous chapter.
Add database to availability group
Customer can add an existing database to an AG (Availability Group).
Note that only DBs that are eligible to be joined to an AG will be listed, i.e.
DB in simple mode will be skipped
DB already member of an AG will be skipped
Make sure that the DB is on the same node as the AG otherwise an error will be thrown.
- Select Add database to availability group
- In the database drop-down, choose the database you want to add to an AG.
- Choose the AG where you want to join the DB
Move availability group
Moving an availability group can cause a short break of the application.
- Select Move availability group
- In the drop-down, choose the AG you want to move
Note: Please make sure that logins are configured in the target replica (FAQ). You can also use the stored procedure customer.sp_AO_fixLogin to fix the login mapping to the databases.
Remove database from availability group
Removing a database from an AG will remove the DB on the MS SQL AlwaysOn replica. However the database will not be deleted on the primary replica.
- Select Remove database from availability group
- In the drop-down, choose the DB you want to remove from an AG. The current availability group and primary instance will be displayed.
Please note that you execute this action independently from the primary instance placement.
Set availability group replica to readable
The option "readable secondary" will be set on both instances.
- Select Set availability group replica to readable
- In the drop-down, choose the AG you want to set to readable.
To revert the "readable secondary" option to "no" just start and submit the option again.
Note: Setting the replica to readable can affect the system performance, especially if heavy reporting will be executed on it.
Manage Agent Based Backup (AO)
Used to change Agent Based Backup Policy. If there was no Policy set (Current Policy is None), it will install and activate Agent Based Backup.
- Installation and Activation will be triggered if Current Policy is "None" and a New Agent Based Backup Policy is selected
- Change of Policy will be triggered if AlwaysOn Cluster was already protected by Agent Based Backup (Current Policy is not "None") and a New Agent Based Backup Policy is selected
Reconfigure Always On Disk
Used to extend the non system volumes of a single node.
- Which disk has to be extended is given as index number (eg. 1 is D: drive)
- Can only add space to the selected volume (disk index). The action should be repeated for each volume (disk index) if needed.
- When entering New size - this value represents the final volume size