Wiki source code of migrate to managed instance

Version 8.25 by Rudi Marais on 2024/03/28 18:49
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. scale db down to minimum
56
57
58
59 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
60 11. reason for this is to identify any procedure/triggers/functions that dont compile
61 1. once this is done examine results and fix stored procedures on database so that they can compile, ie add missing columns or modify code to not require them
62 11. repeat step 1 and 2 until you get zero objects that can't compile
63 1. check sql code for any reserved varables ie... and fix on DB
64 1. run dacpac export
65 11. make sure nothing is running on source DB
66 11. upgrade source DB to high level for quicker export and set back to what it was once done
67 11. cmd
68 1. run dacpac publish to managed instance
69 1.
70
71 * (((
72 (% class="wikigeneratedid" id="Hhttps:2F2Fgithub.com2Fmicrosoft2Fmssql-scripter" %)
73 [[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
74
75
76 )))
77
78 {{code language="sql"}}
79 -- index
80 select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log')
81 from sys.tables
82 where type = 'U'
83 and is_ms_shipped = 0
84
85 --and temporal_type in (0,2);
86
87 -- export
88
89 select '$path = "E:\temp\test\everest_test"' cmd union
90 select '$server = "mytest.database.windows.net"' union
91 select '$username = "sa"' union
92 select '$password = "123456"' union
93 select 'New-Item -ItemType Directory -Force -Path "$path\export_log"' union
94 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
95
96 select export = concat('bcp "', DB_NAME(), '.',
97 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
98 '" out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ',
99 ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
100 ' -S "', '$server','"',
101 ' -N -U "$username" -P "$password"',
102 ' | Out-Null')
103 from sys.tables
104 where type = 'U'
105 and is_ms_shipped = 0
106 --and temporal_type in (0,2);
107
108 -- import
109
110 select '$path = "E:\temp\test\everest_test"' cmd union
111 select '$target_server = "sqlmi-ebs-prod.public.2a5fe6cde9a7.database.windows.net,3342"' union
112 select '$target_db = "TST_Staging_Everest"' union
113 select '$target_username = "import_test"' union
114 select '$target_password = ''123456''' union
115 select 'New-Item -ItemType Directory -Force -Path "$path\import_log"' union
116 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
117
118 select import = concat('bcp "', '$target_db', '.',
119 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
120 '" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ',
121 ' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.log" ',
122 ' -S "', '$target_server','"',
123 ' -N -U "$target_username" -P "$target_password"',
124 ' -b 1000 -E -k -N -q',
125 ' | Out-Null')
126 from sys.tables
127 where type = 'U'
128 and is_ms_shipped = 0
129 --and temporal_type in (0,2);
130
131 {{/code}}
132
133
134 * disable anti-virus real time scan
135 *
136
137 (% class="wikigeneratedid" %)
138 placeholder
139
140 = Exporting =
141
142 * no encrypted objects, they need to be decrypted or removed prior to exporting.
143
144 SQL 2012
145
146
147
148 EXEC sp_configure 'show advanced options', 1;
149 GO
150 RECONFIGURE;
151 GO
152
153 EXEC sp_configure 'xp_cmdshell',1
154 GO
155 RECONFIGURE
156 GO
157
158
159 EXEC XP_CMDSHELL 'net use O: ~\~\zagpebslab.file.core.windows.net\sqlshare /user:"localhost\zagpebslab" "access_key"'
160
161 EXEC XP_CMDSHELL 'Dir O:'
162
163
164 BACKUP DATABASE Everest_TEST
165 TO DISK = N'O:\ENS_Everest_Test_20230721_1806.bak'
166 WITH COMPRESSION,
167 STATS = 10;
168
169
170 GO
171
172
173 SELECT 
174 session_id as SPID, command, a.text AS Query, start_time, percent_complete,
175 dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
176 FROM sys.dm_exec_requests r 
177 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
178 WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
179
180 GO
181
182
183 = Importing =
184
185
186 = Modifying and Troubleshooting =
187
188 * Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
189 ** place in Origin.xml
190 *** <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>
191
192 == Recalculate Signature ==
193
194 * incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
195 * modify Origin.xml
196 ** Checksum attribute for model.xml

Applications

Navigation

Need help?

If you need help with XWiki you can contact: