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
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
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
* 
If you receive any warning while executing the script row_compression.sql, you can ignore them.
Est-ce que cela a été utile ?