Wiki source code of migrate to managed instance

Version 8.19 by Rudi Marais on 2024/03/28 18:34
Hide last authors
Rudi Marais 8.14 1
2
Rudi Marais 2.4 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
Rudi Marais 1.1 7
Rudi Marais 8.14 8
Rudi Marais 8.16 9 1. put db in readonly mode
10 11. ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT
11
Rudi Marais 8.14 12 1. scale db to accetable performance ie dtus 500
Rudi Marais 8.15 13
Rudi Marais 8.14 14 1. turn off real time scan (anti virus)
Rudi Marais 8.15 15
16 1. run bcp extract scripts
17 11. everest & docs parallel
Rudi Marais 8.14 18 11. extract run from smallest to largest table
Rudi Marais 8.15 19
20 1. run schema extracts
21 11. not in parallel!
Rudi Marais 8.17 22 11. [[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
23 11. C:\Users\alliancetest\.mssqlscripter
24 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
Rudi Marais 8.15 25
Rudi Marais 8.14 26 1. create db
Rudi Marais 8.15 27
28 1. create schemas
Rudi Marais 8.18 29 11. prep scripts first remove non applicable scripting
Rudi Marais 8.14 30 11. document references
31 11. make sure ddl audit triggers are disabled
Rudi Marais 8.15 32
33 1. disable all indexes except clustered
34
Rudi Marais 8.14 35 1. run imports [parallel]
36 11. imports runs from smallest to largest table
Rudi Marais 8.19 37
Rudi Marais 8.16 38 1. verify record counts from pre and post
39
40 1. enable indexes
Rudi Marais 8.19 41 ​​​​​​​
42 1. deploy new ddl audit triggers
43 ​​​​​​​
Rudi Marais 8.16 44 1. enable ddl trigger
45
Rudi Marais 8.19 46 1. update all settings to new environment
47
Rudi Marais 8.16 48 1.
49
Rudi Marais 8.14 50
51
Rudi Marais 4.1 52 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
Rudi Marais 2.8 53 11. reason for this is to identify any procedure/triggers/functions that dont compile
54 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
55 11. repeat step 1 and 2 until you get zero objects that can't compile
56 1. check sql code for any reserved varables ie... and fix on DB
57 1. run dacpac export
58 11. make sure nothing is running on source DB
59 11. upgrade source DB to high level for quicker export and set back to what it was once done
60 11. cmd
61 1. run dacpac publish to managed instance
62 1.
63
Rudi Marais 8.1 64 * (((
Rudi Marais 8.2 65 (% class="wikigeneratedid" id="Hhttps:2F2Fgithub.com2Fmicrosoft2Fmssql-scripter" %)
66 [[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
67
68 * C:\Users\alliancetest\.mssqlscripter
Rudi Marais 8.7 69 * 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
Rudi Marais 8.12 70
71
Rudi Marais 8.1 72 )))
Rudi Marais 1.1 73
Rudi Marais 8.12 74 {{code language="sql"}}
75 -- index
Rudi Marais 8.8 76 select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log')
77 from sys.tables
78 where type = 'U'
79 and is_ms_shipped = 0
80
81 --and temporal_type in (0,2);
82
83 -- export
Rudi Marais 8.10 84
Rudi Marais 8.13 85 select '$path = "E:\temp\test\everest_test"' cmd union
86 select '$server = "mytest.database.windows.net"' union
87 select '$username = "sa"' union
88 select '$password = "123456"' union
89 select 'New-Item -ItemType Directory -Force -Path "$path\export_log"' union
90 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
Rudi Marais 8.10 91
92 select export = concat('bcp "', DB_NAME(), '.',
93 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
94 '" out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ',
Rudi Marais 8.8 95 ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
Rudi Marais 8.5 96 ' -S "', '$server','"',
97 ' -N -U "$username" -P "$password"',
98 ' | Out-Null')
Rudi Marais 8.3 99 from sys.tables
100 where type = 'U'
101 and is_ms_shipped = 0
Rudi Marais 8.5 102 --and temporal_type in (0,2);
Rudi Marais 2.8 103
Rudi Marais 8.3 104 -- import
Rudi Marais 8.10 105
Rudi Marais 8.13 106 select '$path = "E:\temp\test\everest_test"' cmd union
107 select '$target_server = "sqlmi-ebs-prod.public.2a5fe6cde9a7.database.windows.net,3342"' union
108 select '$target_db = "TST_Staging_Everest"' union
109 select '$target_username = "import_test"' union
110 select '$target_password = ''123456''' union
111 select 'New-Item -ItemType Directory -Force -Path "$path\import_log"' union
Rudi Marais 8.10 112 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
113
Rudi Marais 8.5 114 select import = concat('bcp "', '$target_db', '.',
Rudi Marais 8.8 115 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
116 '" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ',
117 ' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.log" ',
Rudi Marais 8.5 118 ' -S "', '$target_server','"',
119 ' -N -U "$target_username" -P "$target_password"',
120 ' -b 1000 -E -k -N -q',
121 ' | Out-Null')
Rudi Marais 8.3 122 from sys.tables
123 where type = 'U'
124 and is_ms_shipped = 0
Rudi Marais 8.12 125 --and temporal_type in (0,2);
Rudi Marais 8.13 126
Rudi Marais 8.12 127 {{/code}}
Rudi Marais 8.3 128
129
Rudi Marais 8.4 130 * disable anti-virus real time scan
Rudi Marais 8.11 131 *
Rudi Marais 8.3 132
133 (% class="wikigeneratedid" %)
134 placeholder
135
Rudi Marais 1.1 136 = Exporting =
137
Rudi Marais 1.2 138 * no encrypted objects, they need to be decrypted or removed prior to exporting.
Rudi Marais 1.1 139
Rudi Marais 5.1 140 SQL 2012
141
142
143
144 EXEC sp_configure 'show advanced options', 1;
145 GO
146 RECONFIGURE;
147 GO
148
149 EXEC sp_configure 'xp_cmdshell',1
150 GO
151 RECONFIGURE
152 GO
153
154
155 EXEC XP_CMDSHELL 'net use O: ~\~\zagpebslab.file.core.windows.net\sqlshare /user:"localhost\zagpebslab" "access_key"'
156
157 EXEC XP_CMDSHELL 'Dir O:'
158
159
160 BACKUP DATABASE Everest_TEST
161 TO DISK = N'O:\ENS_Everest_Test_20230721_1806.bak'
Rudi Marais 6.1 162 WITH COMPRESSION,
Rudi Marais 7.1 163 STATS = 10;
Rudi Marais 6.1 164
165
Rudi Marais 5.1 166 GO
167
168
Rudi Marais 6.1 169 SELECT 
170 session_id as SPID, command, a.text AS Query, start_time, percent_complete,
171 dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
172 FROM sys.dm_exec_requests r 
173 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
174 WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
175
176 GO
177
178
Rudi Marais 1.1 179 = Importing =
180
181
Rudi Marais 1.3 182 = Modifying and Troubleshooting =
Rudi Marais 2.2 183
Rudi Marais 2.7 184 * Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
185 ** place in Origin.xml
Rudi Marais 2.8 186 *** <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>
Rudi Marais 2.2 187
188 == Recalculate Signature ==
189
190 * incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
191 * modify Origin.xml
192 ** Checksum attribute for model.xml

Applications

Navigation

Need help?

If you need help with XWiki you can contact: