Wiki source code of migrate to managed instance

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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: