SQL Azure Database Creation Script
As of the 2022 release, SQL Azure is supported as a database provider for Meridian Enterprise Server. However, there are a few limitations to this support:
This script creates configuration and Explorer databases in Azure SQL server and configures your Meridian installation to use Azure databases. You also have the option to create a new Azure resource group and SQL server.
If you decide not to use this option, your existing group or server will be used. The databases will be created with a performance level of S0, but you can scale up your databases to another size later.
After setup is complete, you can use the CreateRepository script switch to create a new Explorer repository.
The following requirements must be met to successfully use this script.
-
PowerShell 5.1 or higher is required, but we recommend using PowerShell 7
-
Az and SqlServer PowerShell modules
If you run the script, it will detect if the modules are not available.
The following script files can be found in C:\Program Files\BC-Meridian\Enterprise Server\AzureSql after installing Meridian:
-
AzSqlConfig.json: Configuration file in JSON format
This is a sample configuration file. You are not required to use this specific file.
-
AzureSqlSetup.ps1: PowerShell script
-
CreateExplorerDb.sql: Explorer Repository SQL script
The following parameters can be defined in the configuration file.
Configuration file settings
| subscriptionId |
Subscription ID for the Azure SQL server ($subscriptionId = $config.AzSql.subscriptionId) |
| location |
Location of the Azure SQL server ($location = $config.AzSql.location) |
| resourceGroupName |
Resource group name of the Azure SQL server ($resourceGroupName = $config.AzSql.resourceGroupName) |
| sqlServerName |
SQL Server name |
| startIp |
Starting IP address of the Azure SQL server ($startIp = $config.AzSql.startIp) |
| endIp |
Ending IP address of the Azure SQL server ($endIp = $config.AzSql.endIp) |
| SqlLogin |
The SQL Server account username you want to use |
| configDatabaseName |
Configuration database name |
| repoDatabaseName |
Explorer repository database name |
| explorerRepositoryName |
Explorer repository display name |
| serverDnsName |
Server DNS name for public URL of Hyperion. If empty, the Server FQDN name will be used. |
The following switches and arguments can be used in the AzureSqlSetup Powershell script.
Switches
Arguments
-
ConfigFile: Required. This is the path to the configuration file.
-
CreateResGroup: Optional, used to create a resource group. This is set to yes by default.
-
CreateSqlSever: Optional, used to create a SQL server. This is set to yes by default.
Example argument 1
This example sets up Azure SQL using an existing resource group and creates a new SQL server if one does not exist.
.\AzureSqlSetup.ps1 -SetupAzSql -ConfigFile AzSqlConfig.json false
Example argument 2
This example creates a new Explorer repository.
.\AzureSqlSetup.ps1 -CreateRepository -ConfigFile AzSqlConfig.json
To implement this configuration:
-
Navigate to C:\Program Files\BC-Meridian\Enterprise Server\AzureSql.
-
Extract the files to a folder on your Meridian Enterprise Server.
The location you extract to is not important, but you will need to navigate to this location in PowerShell later. In our case, we extracted the files to a sub-folder in our C drive.
-
Open AzSqlConfig.json in any editor.
-
Refer to the Configuration file settings table above and define your configuration settings.
-
Save your changes.
-
Run PowerShell as an Administrator.
-
Navigate to the folder where you extracted the script files.
-
Run AzureSqlSetup.ps1 using the SetupAzSql script argument defined in the Script arguments section above.
You are prompted to sign in to Azure.
-
Sign in to your Azure account.
Once you are successfully signed in, you are presented with a setup configuration summary.
-
Check the summary to ensure your settings are properly configured.
-
Type Yes and press Enter on your keyboard to continue.
If you want to change your configuration settings, type No instead.
-
Type the password you want to use for your SQL account, and then press Enter on your keyboard.
Make sure to use a password you will remember, or store it in a safe location, such as your organization's password manager application.
The script executes. It may take some time to complete. Do not close the application.
When the script is successful, it will return the message, "Azure SQL Setup finished."
-
Run AzureSqlSetup.ps1 using the CreateRepository script argument defined in the Script arguments section above.
You are prompted to sign in to Azure.
-
Sign in to your Azure account.
Once you are successfully signed in, you are presented with a setup configuration summary.
-
Check the summary to ensure your settings are properly configured.
-
Type Yes and press Enter on your keyboard to continue.
If you want to change your configuration settings, type No instead.
-
Enter the password for your SQL account.
The script executes. It may take some time to complete. Do not close the application.
When the script is successful, it will return the message, "Script done".
The following scenarios may occur when attempting to implement SQL Azure.
-
When trying to install a PowerShell module, the following error might occur: “No match was found for the specified search criteria and module name…”
To resolve this issue, specify TLS 1.2 for the .NET security protocol using the following command:
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
-
The SQL statement Invoke-Sqlcmd might fail with the following error: “A parameter cannot be found that matches parameter name ‘ConnectionString'“
This happens when an old SQL module is installed (SQLPS). To resolve this issue, install the new SqlServer module with -AllowClobber option enabled:
Install-Module -Name SqlServer -AllowClobber
Prerequisites