配置 Azure SQL 資料庫
 |
本節中介紹的指令集為範例指令集。PTC 提供此指令集，目的是供 Azure 管理員參考。Azure 管理員必須根據服務目標與所需服務層來為 Windchill 設定資料庫。
|
1. 建立與新建資料庫的新連線，並開啟「查詢」標籤。
 |
或者，您也可以在新建立的資料庫上按一下滑鼠右鍵，來重新整理資料庫並開啟新查詢。
|
2. 透過下列指令集以資料庫名稱和密碼取代 _DBNAME_ 和 _PASSWORD_：
|
|
建立新 SQL Server 資料庫時使用的密碼必須至少為 8 個字元，且必須包含下列四種字元的其中三種：大寫字母、小寫字母、任意十進制數字以及符號。如果密碼不符合所需條件，則 SQL Server 資料庫的安裝將失敗。
|
-- 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. 欲建立 Windchill 的資料庫角色，請使用下列指令集：
-- 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. 執行在上一個步驟中預備的 sql。
|
|
您可以透過經由 SSMS 以新使用者登入認證登入資料庫的方式來核對 Azure SQL 中的使用者建立。
|
效能考量
為取得最佳效能，PTC 建議當 Azure SQL 資料庫中的資料可用時，根據指示執行下列指令集。確保在執行 SQL 指令集之前已安裝 SQL 指令行工具。或者，也可以在資料庫上按一下滑鼠右鍵並開啟一個新查詢標籤，來從 SSMS 執行 sql 指令集。
1. 使用下列指令執行在 <WT_HOME>/db/azureSQL/utils 目錄下提供的 generate_scripts.sh：
generate_scripts.sh "<SERVER NAME>" "<USER>" "<PASSWORD>" "<DATABASE>"
結果：在此目錄下建立 row_compression.sql 指令集。
2. 使用下列指令執行 row_compression.sql 指令集：
sqlcmd -S <SERVER NAME> -U <USER> -P <PASSWORD> -d <DATABASE> -i row_compression.sql
|
|
如果您在執行 row_compression.sql 指令集時收到任何警告，可以將其略過。
|