T-SQL-Logging/CreateBaseDatabase.sql at master · DerekHackett/T-SQL-Logging · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
/****** Object:  Database [Base]    Script Date: 3/30/2018 9:48:21 PM ******/
CREATE DATABASE [Base]  (EDITION = 'Basic', SERVICE_OBJECTIVE = 'Basic', MAXSIZE = 2 GB) WITH CATALOG_COLLATION = SQL_Latin1_General_CP1_CI_AS;
GO

ALTER DATABASE [Base] SET COMPATIBILITY_LEVEL = 140
GO

ALTER DATABASE [Base] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [Base] SET ANSI_NULLS OFF
GO

ALTER DATABASE [Base] SET ANSI_PADDING OFF
GO

ALTER DATABASE [Base] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [Base] SET ARITHABORT OFF
GO

ALTER DATABASE [Base] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [Base] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [Base] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [Base] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [Base] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [Base] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [Base] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [Base] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [Base] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [Base] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

ALTER DATABASE [Base] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [Base] SET READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE [Base] SET  MULTI_USER
GO

ALTER DATABASE [Base] SET ENCRYPTION ON
GO

ALTER DATABASE [Base] SET QUERY_STORE = ON
GO

ALTER DATABASE [Base] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 7), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 10, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO)
GO

/*** The scripts of database scoped configurations in Azure should be executed inside the target database connection. ***/
GO

-- ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON;
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 MAXDOP = 0;
GO

-- ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO

-- ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = OFF;
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 SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = OFF;
GO

-- ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = OFF;
GO

ALTER DATABASE [Base] SET  READ_WRITE
GO