Create Databases in SQLServer Flex Instances

Prerequisites

In order to follow the steps described on this page, the following conditions need to be met:

• Your organization has a customer account.
  (See: Create a customer account)

• You have a User Account with the necessary permissions.
  (See: Create a user account, Identity and Access Management)

• You have a Project in your customer account.
  (See: Create a Project)

• You have created a Service Account.
  (See: Create a Service account)

• You have assigned the required project permissions to this service account.
  (See: Assign permissions to a service account)

• You have created an Access Token for this service account.
  (See: Assign authentication token to a service account)

• You have created a SQLServer Flex Instance.
  (See: Create a SQLServer Flex Instance)

• You are connected to the SQLServer Flex Instance.
  (See: Connect to a SQLServer Flex Instances)

Considerations for Creating Databases on SQLServer Flex

Portal / API

Create a Database via Portal / API

Supported by single and HA instances!

• Database naming: Database names can only contain alphanumeric characters (a-z, A-Z, 0-9 without umlauts) together with “_”, other special characters or space are not allowed.
• Database options/parameters: Creating databases via portal or API supports supplying only database name and owner as parameters, so defining any extra parameters like compatibility level, collations or any other parameter is not supported. (This might change in future versions.)
• Database initial size: When creating databases via portal or API, the databases are being created with two files only (1 data file and 1 log file). The size of these files are:
  • Data file size is 128 MB, with autogrowth of 64 MB.
  • Log file size is 64 MB, with autogrowth of 64 MB.
• Database compatibility level: The database compatibility level is 160 (SQL Server 2022) by default.
• Database collation: Both server and database collations is “SQL_Latin1_General_CP1_CI_AS”.
• Database recovery model: Database recovery model is “FULL” by default.

SQL Server Client Tools / Transact SQL (T-SQL)

Create a Database via SQL Server Client Tools / Transact SQL (T-SQL)

Supported by single instances!

When a user is creating a databases by using client tools for SQL Server or T-SQL, the user is free to set all the above options/properties with customized values. However some restrictions and recommendations need to be taken into consideration:

• Database naming: It is recommended to follow the same approach when creating databases as described above via the portal or the API, avoiding spaces and special characters and using only alphanumeric characters together with ”_”.
• Database files size:Databases can not be created with files less than:
  • 128 MB for data files.
  • 64 MB for log files.
• Database files location: Databasescan be created in certain locations only, with the following recommendations for better disk management:
  • Data files must be created in the following location: /var/opt/mssql/custdata/
  • Log files must be created in the following location: /var/opt/mssql/custlog/
  • It is best not to enter any path information when creating (on PRIMARY / LOG on).
• Database collation: Databases can be created with any customized collation value. However please keep into consideration that server collation value is fixed at the current release with value: “SQL_Latin1_General_CP1_CI_AS”, so the tempdb collation will always be “SQL_Latin1_General_CP1_CI_AS”.
• Database recovery model: Databases should always be in FULL recovery model in order to guarantee the RPO level defined in SLA.
• Database Compatibility Level: the Database Comaptibility Level sets the Database in the proper feature level mode for the SQL Server. This means, that the SQL Server will run this Database with the features developed for the defined compatibility level.

Create a Database

Portal

1. Navigate to SQLServer Flex and select the database server on which you want to create a database.
2. Create the database user for your new database.
   See: Create Logins and Users in SQLServer Flex Instances
3. Select Databases from the menu.
4. Click Create Database.
5. Enter the database details in the form.
   See: Considerations for Creating Databases on SQLServer Flex
   
6. Click Create.

API

API calls can be performed using Postman or curl.

Postman

You can download Postman here

1. Prepare the JSON payload, which is a simple JSON object containing the name of the database and the name of the database owner:

{
"name": "dbname",
"collation": "Latin1_General_CI_AS",
"options": {
"owner": "DBUser",
"collation": "Latin1_General_CI_AS",
"compatibility_level": "160"
}
}

2. Open Postman and create a new Workspace.
3. Name the new Workspace and define the access.
4. Go to Collection and press the ”+” sign to create a new API request.
5. In the request window, select the POST option from the drop-down-list. The needed compatibility Level can be fetched form the API Endpoint: https://mssql-flex-service.api.eu01.stackit.cloud /v1/projects/{projectId}/versions
6. Paste the endpoint URL into the address bar: https://mssql-flex-service.api.eu01.stackit.cloud/v2/projects/{PROJECT_ID}/regions/{REGION}/instances/{INSTANCE_ID}/databases and replace {PROJECT_ID}, {INSTANCE_ID} and {REGION}
7. with the project ID and the instance ID of your SQL Server instance, respectively.

Where do I find the Project ID? The project ID is displayed in the projects list of the Resource manager:

Where do I find the Instance ID? The instance ID has the form of a UUID. It is displayed in the Overview tab of your instance in the STACKIT portal, in the section General information. For some products, the display of the instance ID in the portal is not implemented yet. In these cases the instance ID is visible as part of the instance URL in the address field of your browser when you are on the instance page in the portal, and it can be copied from there: 8. Go to Authorization section and select Bearer Token from the Type drop-down list. 9. Copy your token and past it into the Token section to be used in authentication process when executing the API. 10. Select the _Body* tab. 11. Select raw and paste the JSON payload you prepared before into the input area. 12. Send the request.

curl unter macOS / Linux

1. Open a terminal and paste the code below.
2. Replace {PROJECT_ID}, {INSTANCE_ID}, {REGION} and {TOKEN} with the project ID, the instance ID and the token of your SQL Server instance the database should be created in.
3. Replace demo_db_1 with the database name and “Administrator" with with the database owner name of your choice having ##STACKIT_DatabaseManager## Permissions.
4. Execute the code.

curl --location --request POST 'https://mssql-flex-service.api.eu01.stackit.cloud/v2/projects/{PROJECT_ID}/regions/{REGION}/instances/{INSTANCE_ID}/databases' --header '
Accept: application/json' --header 'Authorization: Bearer {Token}' --data '{
"name": "demo_db_1",
"options": {
"owner": "Administrator",
"collation": "Latin1_General_CI_AS",
"compatibility_level": "160"     }
}' | jq.

curl unter Windows

1. Open a PowerShell session and paste the code below.
2. Replace {PROJECT_ID}, {INSTANCE_ID}, {REGION} and {TOKEN} with the project ID, the instance ID and the token of your SQL Server instance the database should be created in.
3. Replace demo_db_1 with the database name and “Administrator" with with the database owner name of your choice having ##STACKIT_DatabaseManager## Permissions.
4. Execute the code.

curl --location --request POST 'https://mssql-flex-service.api.eu01.stackit.cloud/v2projects/{PROJECT_ID}/regions/{REGION}/instances/{INSTANCE_ID}/databases' --header '
Accept: application/json' --header 'Authorization: Bearer {TOKEN}' --data '{
"name": "demo_db_1",
"options": {
"owner": "DBAdmin",
"collation": "Latin1_General_CI_AS",
"compatibility_level": "160"

}
}' | ConvertFrom-Json | ConvertTo-Json

SQL Server client tools

Databases can also be created on Single Instances by connecting to one of SQL Server client tools, e.g. SQL Server Management Studio, Azure Data Studio or any other client tool that supports connection to SQL Server.

SQL Server Management Studio (SSMS)	Azure Data Studio

Transact SQL (T-SQL)

You can use any SQL Server query execution tool that can execute T-SQL against an SQL Server, e. g. SQL Server Management Studio, Azure Data Studio, or PowerShell.

• Execute the CREATE DATABASE T-SQL command.

For more insights about CREATE DATABASE command and options, please visit Microsoft official documentation for SQL Server.