Configuring Azure SQL Database
 |
The scripts mentioned in this section are sample scripts. PTC provides this scripts as a reference for your Azure administrators. The Azure administrators must provision the database for Windchill based on service objective and tier of service required.
|
1. Create a new connection to the newly created database and open the Query Tab.
 |
Alternatively, you can refresh databases and open a new query by right-clicking the newly created database.
|
2. Replace _DBNAME_ and _PASSWORD_ with Database Name and Password in the following script:
|
|
The password used while creating new SQL Server database must be at least 8 characters long and must contain characters from three of the four sets — uppercase letter, lowercase letter, any of the base 10 digits, and symbols. If the password does not meet the required criteria then installation of SQL Server database will fail.
|
-- Connect to [_DBNAME_] Database.
-- Dont use the password with special Character $
GO
CREATE USER [_DBNAME_] WITH PASSWORD=N'_PASSWORD_'
GO
EXEC sp_addrolemember N'db_owner', N'_DBNAME_'
GO
CREATE SCHEMA [_DBNAME_] AUTHORIZATION [_DBNAME_]
GO
ALTER USER [_DBNAME_] WITH DEFAULT_SCHEMA=[_DBNAME_]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO
ALTER DATABASE CURRENT SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15, MAX_STORAGE_SIZE_MB = 2048, QUERY_CAPTURE_MODE = AUTO);
GO
exec sp_updatestats;
GO
-- Dont use the password with special Character $
GO
CREATE USER [_DBNAME_] WITH PASSWORD=N'_PASSWORD_'
GO
EXEC sp_addrolemember N'db_owner', N'_DBNAME_'
GO
CREATE SCHEMA [_DBNAME_] AUTHORIZATION [_DBNAME_]
GO
ALTER USER [_DBNAME_] WITH DEFAULT_SCHEMA=[_DBNAME_]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO
ALTER DATABASE CURRENT SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15, MAX_STORAGE_SIZE_MB = 2048, QUERY_CAPTURE_MODE = AUTO);
GO
exec sp_updatestats;
GO
3. To create database role for Windchill, use the following script:
-- Create Database Role for Windchill.
-- Replace [rolam] with a role of your choice which is unique for your database
CREATE ROLE [rolam] AUTHORIZATION dbo
GO
GRANT CREATE FULLTEXT CATALOG TO [rolam]
GO
GRANT CREATE FUNCTION TO [rolam]
GO
GRANT CREATE PROCEDURE TO [rolam]
GO
GRANT CREATE SYNONYM TO [rolam]
GO
GRANT CREATE TABLE TO [rolam]
GO
GRANT CREATE TYPE TO [rolam]
GO
GRANT CREATE VIEW TO [rolam]
GO
GRANT DELETE TO [rolam]
GO
GRANT EXECUTE TO [rolam]
GO
GRANT INSERT TO [rolam]
GO
GRANT REFERENCES TO [rolam]
GO
GRANT SELECT TO [rolam]
GO
GRANT UPDATE TO [rolam]
GO
-- Replace [rolam] with a role of your choice which is unique for your database
CREATE ROLE [rolam] AUTHORIZATION dbo
GO
GRANT CREATE FULLTEXT CATALOG TO [rolam]
GO
GRANT CREATE FUNCTION TO [rolam]
GO
GRANT CREATE PROCEDURE TO [rolam]
GO
GRANT CREATE SYNONYM TO [rolam]
GO
GRANT CREATE TABLE TO [rolam]
GO
GRANT CREATE TYPE TO [rolam]
GO
GRANT CREATE VIEW TO [rolam]
GO
GRANT DELETE TO [rolam]
GO
GRANT EXECUTE TO [rolam]
GO
GRANT INSERT TO [rolam]
GO
GRANT REFERENCES TO [rolam]
GO
GRANT SELECT TO [rolam]
GO
GRANT UPDATE TO [rolam]
GO
4. Run the sql prepared in previous step.
|
|
You can verify the creation of a user in Azure SQL by logging in to the database with a new user login credentials through SSMS.
|
Performance Consideration
For optimal performance, PTC recommends that when data is available in Azure SQL database, execute following scripts as instructed. Ensure that you have installed SQL command-line tool before running the sql scripts. Alternatively, you can execute the sql scripts from SSMS by right-clicking the database and opening a new query tab.
1. Run the generate_scripts.sh available at <WT_HOME>/db/azureSQL/utils using the following command:
generate_scripts.sh "<SERVER NAME>" "<USER>" "<PASSWORD>" "<DATABASE>"
Result: row_compression.sql script is created in the same directory.
2. Run the row_compression.sql script using the following command:
sqlcmd -S <SERVER NAME> -U <USER> -P <PASSWORD> -d <DATABASE> -i row_compression.sql