配置 Azure SQL 数据库
 |
本部分中提到的脚本是示例脚本。PTC 提供这些脚本供 Azure 管理员参考。Azure 管理员必须根据服务目标和所需服务层来设置用于 Windchill 的数据库。
|
1. 创建与新建数据库的连接，然后打开 Query Tab。
 |
或者，您可以右键单击新创建的数据库来刷新数据库并打开新查询。
|
2. 在以下脚本中用数据库名称和密码替换 _DBNAME_ 和 _PASSWORD_：
|
|
创建新 SQL Server 数据库时使用的密码长度必须至少为 8 个字符，且必须包含以下四个集合中三个集合内的字符：大写字母、小写字母、10 以内的数字以及符号。如果密码不符合所要求的条件，则 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 时接收到警告，则可以忽略这些警告。
|