# Create SQL Server VM and necessary databases in a tenant subscription

If you have an **existing SQL server**, you can choose to use that SQL server instead of creating newly. Ensure SQL server authentication is enabled and you have **ANY valid SQL server authentication credential** available. The minimum recommended hardware configuration of the SQL server is 4 cores vCPU and 8 GB RAM.

**Step A** below allows to create a **new SQL Server VM** in Azure Stack for the given tenant subscription, SQL admin credential and 3 new databases necessary for VConnect RP installation.

 Proceed to **Step B** to ensure enabling necessary port and create appropriate new databases before proceeding to the installation.  

## HOST VM

On **AzS Development Kits**, sign in to the physical host. 

On **multi-node integrated systems**, the host must be a system that can access admin and tenant endpoints.

### Step A. Create all new SQL Server VM and necessary databases in a given tenant subscription

1. Go to the install folder that was unzipped and used in the previous installation step [AzureStack Certification Authority certificate](https://docs.cloudassert.com/azure-stack-hub-multi-stamp-management/installation/prerequisites/get-azurestackcertificationauthority.cer) (c:\CloudAssert\VConnectMSM)
2. Open a new elevated (administrative) PowerShell  console and change to the above directory where the files have been extracted (c:\CloudAssert\VConnectMSM). Use a new window to avoid problems that might arise from incorrect PowerShell modules that are already loaded on the system.
3. Run SetupSqlServer.ps1 and specify the required parameters

```
.\SetupSqlServer.ps1 -DefaultProviderSubscriptionId "" -AdminArmEndpoint "" -ServiceAdminDirectoryTenantName "" -ServiceAdmin "" -ServiceTenantDirectoryTenantName "" -TenantSubscriptionId "" -ServiceTenant "" -VmLocalAdminUser "caadmin" -VConnectDatabaseName "CloudAssert.VConnect" -SqlUser "" -SetupBillingAndDacm $True -BillingDatabaseName "CloudAssert.Billing" -DacmDatabaseName "CloudAssert.Dacm" -SqlPort 1433

Eg:
.\SetupSqlServer.ps1 -DefaultProviderSubscriptionId "26ec7c55-594d-432f-a728-2c072b942acd" -AdminArmEndpoint "https://adminmanagement.redmond.corp.contosotest.com" -ServiceAdminDirectoryTenantName "contosotest.onmicrosoft.com" -ServiceAdmin "cloudassert@contosotest.onmicrosoft.com" -ServiceTenantDirectoryTenantName "contosotest.onmicrosoft.com" -TenantSubscriptionId "80de4d0c-ae28-46eb-aab7-fc10aa92cebb" -ServiceTenant "cloudassert@contosotest.onmicrosoft.com" -VmLocalAdminUser "caadmin" -VConnectDatabaseName "CloudAssert.VConnect" -SqlUser "cadbadmin" -SetupBillingAndDacm $True -BillingDatabaseName "CloudAssert.Billing" -DacmDatabaseName "CloudAssert.Dacm" -SqlPort 1433
```

| Parameter Name                                                                   | Description                                                                                                                                                           | Required   |
| -------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------- |
| <p><strong>DefaultProvider</strong></p><p><strong>SubscriptionId</strong></p>    | Go to Admin Portal -> Login as Service Admin -> Subscriptions -> Default Provider Subscription -> Get the GUID                                                        | *Required* |
| **AdminArmEndpoint**                                                             | Admin ARM end point URL                                                                                                                                               | *Required* |
| <p><strong>ServiceAdminDirectory</strong></p><p><strong>TenantName</strong></p>  | Directory tenant name Of the service admin user                                                                                                                       | *Required* |
| **ServiceAdmin**                                                                 | Go to Admin Portal -> Login as Service Admin -> Subscriptions -> Default Provider Subscription -> Check if role is **Owner.** Eg: <admin@contosotest.onmicrosoft.com> | *Required* |
| <p><strong>ServiceTenantDirectory</strong></p><p><strong>TenantName</strong></p> | Directory tenant name Of the service tenant user. If not provided, ServiceAdminDirectoryTenantName will be used                                                       | *Optional* |
| **TenantSubscriptionId**                                                         | GUID of the Tenant Subscription where Cloud Assert components need to be deployed                                                                                     | *Required* |
| **ServiceTenant**                                                                | Tenant Subscription administrator account login user name. If not provided above ServiceAdmin credential can be used.                                                 | *Optional* |
| **VmLocalAdminUser**                                                             | SQL Server VM local admin user name. Eg: caadmin                                                                                                                      | *Required* |
| **VConnectDatabaseName**                                                         | Friendly name Eg: CloudAssert.VConnect                                                                                                                                | *Required* |
| **SqlUser**                                                                      | SQL Server authentication user name Eg: cadbadmin                                                                                                                     | *Required* |
| **SetupBillingAndDacm**                                                          | Set this value as **true** for installing Multi Stamp Management components                                                                                           | *Required* |
| **BillingDatabaseName**                                                          | Billing database name Eg: CloudAssert.Billing                                                                                                                         | *Required* |
| **DacmDatabaseName**                                                             | DACM database name Eg: CloudAssert.DACM                                                                                                                               | *Required* |
| **SqlPort**                                                                      | The default port is 1433. This can be changed if required.                                                                                                            | *Optional* |
| **Uninstall**                                                                    | For installation, no need to pass this parameter. For un-install, set it to **true** to un-install SQL server.                                                        | *Optional* |

SQL server installation and database creation may take 20-30 minutes.

4\. Once deployment successfully completes and VM successfully created, Go to **All Resources** -> Select the SQL Virtual Machine just created and note down the **Public IP Address**. (This is needed during installation to be passed to the scripts.)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-MKJVirN0eiahVAMCX_A%2F-MKJXh4UyiyQKh_rn4zV%2FCASQL.PNG?alt=media\&token=cc9aac79-64cd-441c-971c-5495770c5c9f)

5\. Remote in to SQL server -> **SSMS** -> Login using the **SQL server authentication credential** provided in the above script during creation **\[Ensure the mode of signing in is SQL server authentication and NOT windows authentication]**&#x20;

6\. Verify following 3 databases are successfully created

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8B5JavqYPIwW1ITYlg%2Fimage.png?alt=media\&token=59f1ad9a-5640-4080-a83c-9e2931cd79d5)

7\. Verify SQL Inbound Rule is configured

Go to **Windows Firewall** -> **Inbound Rules** -> Verify SQL Inbound Rule for 1433 exists

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8BIjcxRVkX-zJBtaN3%2F-M8BNoce8yKM1E2TDrka%2Fimage.png?alt=media\&token=b6fd3da3-4e6b-418d-9632-84923c450801)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8BIjcxRVkX-zJBtaN3%2F-M8BM2DzyPI_dcOz1FnW%2Fimage.png?alt=media\&token=c728dbde-3fa3-409e-b54f-1ce683fa9f29)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8BIjcxRVkX-zJBtaN3%2F-M8BMBZOi3T-5rZRa_4n%2Fimage.png?alt=media\&token=ba9a408b-7803-46d4-ae08-e2eedde84b1a)

New SQL Server and necessary components are successfully created, please proceed to [Deployment](https://docs.cloudassert.com/azure-stack-hub-multi-stamp-management/installation/deployment).&#x20;

### Step B. If using existing SQL Server, ensure inbound rule and necessary databases are created to proceed

1. Remote in to SQL server -> Windows Firewall -> Inbound Rules. Verify a SQL inbound rule allowing port 1433 exists

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8BIjcxRVkX-zJBtaN3%2F-M8BNz6kPWH3SzyFAM80%2Fimage.png?alt=media\&token=5656ef84-0350-4c02-8f61-6a566f612c4b)

2\. If above rule is not present, follow below steps to create a new inbound rule

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8B3AnDQKUEN4jsXJBx%2Fimage.png?alt=media\&token=00c47c37-c1f4-4214-a6ed-506243a9a08b)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8B3CiJh94fXDYj7hk8%2Fimage.png?alt=media\&token=9de65219-710a-4601-b4d4-6e4ba841180d)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8B3EKTlMA0uL_5vaWO%2Fimage.png?alt=media\&token=f7179b87-048d-4b3a-a9f6-e16f29230978)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8B3FviT2k9moGaJUcZ%2Fimage.png?alt=media\&token=bfe64689-e527-437b-b730-77d249a4b047)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8B3HiC1NN5J_QzIx93%2Fimage.png?alt=media\&token=aa954a99-8915-469b-ab90-3f5b68b9661e)

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8B3LiBBGzbWdPlLftX%2Fimage.png?alt=media\&token=89c52270-3989-4ddc-ac70-c0e7980e6daa)

3\. Remote in to SQL server -> **SSMS** -> Login using the **SQL server authentication credential**  **\[Ensure the mode of signing in is SQL server authentication and NOT windows authentication]**&#x20;

4\. Create **3 new databases** named **CloudAssert.VConnect, CloudAssert.Billing, CloudAssert.DACM**

&#x20;       In **SQL Server** -> **Databases** -> Right Click -> **New Database...**

![](https://2459906120-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M6voWNOkQYKxJnx1zI4%2F-M8Avu0I406P6wV968Om%2F-M8BDWUXUHdYC0pU6-Gb%2Fimage.png?alt=media\&token=decdf700-75cb-44f8-bb64-7a495391c5ce)