Create SQL Server VM and necessary databases in a tenant subscription

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.

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. 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 (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 "" -AdminPassword '' -ServiceTenantDirectoryTenantName "" -TenantSubscriptionId "" -ServiceTenant "" -TenantPassword '' -VmLocalAdminUser "caadmin" -VmLocalAdminPass '' -SqlDbName "" -SqlUser "" -SqlPass '' -SetupBillingAndDacm $True -BillingDatabaseName "CloudAssert.Billing" -DacmDatabaseName "CloudAssert.Dacm"

Eg:
.\SetupSqlServer.ps1 -DefaultProviderSubscriptionId "26ec7c55-594d-432f-a728-2c072b942acd" -AdminArmEndpoint "https://adminmanagement.redmond.corp.contosotest.com" -ServiceAdminDirectoryTenantName "contosotest.onmicrosoft.com" -ServiceAdmin "[e-mail]" -AdminPassword 'TestPass@1234' -ServiceTenantDirectoryTenantName "contosotest.onmicrosoft.com"-TenantSubscriptionId "80de4d0c-ae28-46eb-aab7-fc10aa92cebb" -ServiceTenant "[e-mail]" -TenantPassword 'TestPass@1234' -VmLocalAdminUser "caadmin" -VmLocalAdminPass 'TestPass@1234' -SqlDbName "CloudAssert.VConnect" -SqlUser "cadbadmin" -SqlPass 'TestPass@1234' -SetupBillingAndDacm $True -BillingDatabaseName "CloudAssert.Billing" -DacmDatabaseName "CloudAssert.Dacm"

Parameter Name	Description	Required
DefaultProvider SubscriptionId	Go to Admin Portal -> Login as Service Admin -> Subscriptions -> Default Provider Subscription -> Get the GUID	Required
AdminArmEndpoint	Admin ARM end point URL	Required
ServiceAdminDirectory TenantName	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: [e-mail]	Required
AdminPassword	Password for the above ServiceAdmin	Required
ServiceTenantDirectory TenantName	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
TenantPassword	Tenant Subscription administrator account login user password.If not provided above ServiceAdmin credential can be used.	Optional
VmLocalAdminUser	SQL Server VM local admin user name. Eg: caadmin	Required
VmLocalAdminPass	Password for above SQL Server VM local admin user	Required
SqlDbName	Friendly name Eg: CloudAssert.VConnect	Required
SqlUser	SQL Server authentication user name Eg: cadbadmin	Required
SqlPass	Above sql user credential password	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
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.)

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]

6. Verify following 3 databases are successfully created

7. Verify SQL Inbound Rule is configured

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

New SQL Server and necessary components are successfully created, please proceed to Deployment.

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

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

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]

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

In SQL Server -> Databases -> Right Click -> New Database...