Wiki source code of migrate to managed instance

Version 8.29 by Rudi Marais on 2024/03/28 18:50
Show last authors
1
2
3 * sqlpackage
4 ** [[https:~~/~~/download.microsoft.com/download/7/c/8/7c877288-a0d9-4d22-b1dc-2086e262d82f/x64/DacFramework.msi>>https://download.microsoft.com/download/7/c/8/7c877288-a0d9-4d22-b1dc-2086e262d82f/x64/DacFramework.msi]]
5 ** [[https:~~/~~/learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download>>https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download]]
6 ** C:\Program Files\Microsoft SQL Server\160\DAC\bin
7
8
9 1. put db in readonly mode
10 11. stop all applicable services
11 11. ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT
12
13 1. scale db to accetable performance ie dtus 500
14
15 1. turn off real time scan (anti virus)
16
17 1. run bcp extract scripts
18 11. everest & docs parallel
19 11. extract run from smallest to largest table
20
21 1. run schema extracts
22 11. not in parallel!
23 11. [[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
24 11. C:\Users\alliancetest\.mssqlscripter
25 11. mssql-scripter -S "kula.database.windows.net" -d Everest_Test -U ebsadmin -P xxx -r -f E:\temp\khula_test.sql ~-~-display-progress ~-~-enable-toolsservice-logging
26
27 1. create db
28
29 1. create schemas
30 11. prep scripts first remove non applicable scripting
31 11. document references
32 11. make sure ddl audit triggers are disabled
33
34 1. enable compresssion on applicable tables
35 11. tblAudit etc
36
37 1. disable all indexes except clustered
38
39 1. run imports [parallel]
40 11. imports runs from smallest to largest table
41
42 1. verify record counts from pre and post
43
44 1. enable indexes
45
46 1. deploy new ddl audit triggers
47 ​​​​​​​
48 1. enable ddl trigger
49
50 1. run db security scripts
51 11. creates db groups and ad groups
52
53 1. update all settings to new environment
54
55 1. do audit table test
56
57 1. scale db down to minimum
58
59
60
61 1. run extract with [[sql-script-db.ps1>>doc:technical-documentation.Source Control.migration.sql.sql-db-export.WebHome]] with validate set to true and raw objects set to true, only need tbl-triggers, functions and sp
62 11. reason for this is to identify any procedure/triggers/functions that dont compile
63
64 * (((
65
66 )))
67
68 {{code language="sql"}}
69 -- index
70 select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log')
71 from sys.tables
72 where type = 'U'
73 and is_ms_shipped = 0
74
75 --and temporal_type in (0,2);
76
77 -- export
78
79 select '$path = "E:\temp\test\everest_test"' cmd union
80 select '$server = "mytest.database.windows.net"' union
81 select '$username = "sa"' union
82 select '$password = "123456"' union
83 select 'New-Item -ItemType Directory -Force -Path "$path\export_log"' union
84 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
85
86 select export = concat('bcp "', DB_NAME(), '.',
87 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
88 '" out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ',
89 ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
90 ' -S "', '$server','"',
91 ' -N -U "$username" -P "$password"',
92 ' | Out-Null')
93 from sys.tables
94 where type = 'U'
95 and is_ms_shipped = 0
96 --and temporal_type in (0,2);
97
98 -- import
99
100 select '$path = "E:\temp\test\everest_test"' cmd union
101 select '$target_server = "sqlmi-ebs-prod.public.2a5fe6cde9a7.database.windows.net,3342"' union
102 select '$target_db = "TST_Staging_Everest"' union
103 select '$target_username = "import_test"' union
104 select '$target_password = ''123456''' union
105 select 'New-Item -ItemType Directory -Force -Path "$path\import_log"' union
106 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
107
108 select import = concat('bcp "', '$target_db', '.',
109 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
110 '" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ',
111 ' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.log" ',
112 ' -S "', '$target_server','"',
113 ' -N -U "$target_username" -P "$target_password"',
114 ' -b 1000 -E -k -N -q',
115 ' | Out-Null')
116 from sys.tables
117 where type = 'U'
118 and is_ms_shipped = 0
119 --and temporal_type in (0,2);
120
121 {{/code}}
122
123
124
125 *
126
127 (% class="wikigeneratedid" %)
128 placeholder
129
130 = Exporting =
131
132 * no encrypted objects, they need to be decrypted or removed prior to exporting.
133
134 SQL 2012
135
136
137
138 EXEC sp_configure 'show advanced options', 1;
139 GO
140 RECONFIGURE;
141 GO
142
143 EXEC sp_configure 'xp_cmdshell',1
144 GO
145 RECONFIGURE
146 GO
147
148
149 EXEC XP_CMDSHELL 'net use O: ~\~\zagpebslab.file.core.windows.net\sqlshare /user:"localhost\zagpebslab" "access_key"'
150
151 EXEC XP_CMDSHELL 'Dir O:'
152
153
154 BACKUP DATABASE Everest_TEST
155 TO DISK = N'O:\ENS_Everest_Test_20230721_1806.bak'
156 WITH COMPRESSION,
157 STATS = 10;
158
159
160 GO
161
162
163 SELECT 
164 session_id as SPID, command, a.text AS Query, start_time, percent_complete,
165 dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
166 FROM sys.dm_exec_requests r 
167 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
168 WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
169
170 GO
171
172
173 = Importing =
174
175
176 = Modifying and Troubleshooting =
177
178 * Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
179 ** place in Origin.xml
180 *** <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>
181
182 == Recalculate Signature ==
183
184 * incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
185 * modify Origin.xml
186 ** Checksum attribute for model.xml