Wiki source code of migrate to managed instance

Version 8.16 by Rudi Marais on 2024/03/28 18:32
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!
22
Rudi Marais 8.14 23 1. create db
Rudi Marais 8.15 24
25 1. create schemas
Rudi Marais 8.14 26 11. prep scripts first
27 11. document references
28 11. make sure ddl audit triggers are disabled
Rudi Marais 8.15 29
30 1. disable all indexes except clustered
31
Rudi Marais 8.14 32 1. run imports [parallel]
33 11. imports runs from smallest to largest table
Rudi Marais 8.16 34 ​​​​​​​
35 1. verify record counts from pre and post
36
37 1. enable indexes
38 1. enable ddl trigger
39
40 1.
41
Rudi Marais 8.14 42
43
Rudi Marais 4.1 44 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 45 11. reason for this is to identify any procedure/triggers/functions that dont compile
46 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
47 11. repeat step 1 and 2 until you get zero objects that can't compile
48 1. check sql code for any reserved varables ie... and fix on DB
49 1. run dacpac export
50 11. make sure nothing is running on source DB
51 11. upgrade source DB to high level for quicker export and set back to what it was once done
52 11. cmd
53 1. run dacpac publish to managed instance
54 1.
55
Rudi Marais 8.1 56 * (((
Rudi Marais 8.2 57 (% class="wikigeneratedid" id="Hhttps:2F2Fgithub.com2Fmicrosoft2Fmssql-scripter" %)
58 [[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
59
60 * C:\Users\alliancetest\.mssqlscripter
Rudi Marais 8.7 61 * 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 62
63
Rudi Marais 8.1 64 )))
Rudi Marais 8.12 65 * ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT
66
Rudi Marais 1.1 67
Rudi Marais 8.12 68 {{code language="sql"}}
69 -- index
Rudi Marais 8.8 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
Rudi Marais 8.10 78
Rudi Marais 8.13 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"'
Rudi Marais 8.10 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" ',
Rudi Marais 8.8 89 ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
Rudi Marais 8.5 90 ' -S "', '$server','"',
91 ' -N -U "$username" -P "$password"',
92 ' | Out-Null')
Rudi Marais 8.3 93 from sys.tables
94 where type = 'U'
95 and is_ms_shipped = 0
Rudi Marais 8.5 96 --and temporal_type in (0,2);
Rudi Marais 2.8 97
Rudi Marais 8.3 98 -- import
Rudi Marais 8.10 99
Rudi Marais 8.13 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
Rudi Marais 8.10 106 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
107
Rudi Marais 8.5 108 select import = concat('bcp "', '$target_db', '.',
Rudi Marais 8.8 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" ',
Rudi Marais 8.5 112 ' -S "', '$target_server','"',
113 ' -N -U "$target_username" -P "$target_password"',
114 ' -b 1000 -E -k -N -q',
115 ' | Out-Null')
Rudi Marais 8.3 116 from sys.tables
117 where type = 'U'
118 and is_ms_shipped = 0
Rudi Marais 8.12 119 --and temporal_type in (0,2);
Rudi Marais 8.13 120
Rudi Marais 8.12 121 {{/code}}
Rudi Marais 8.3 122
123
Rudi Marais 8.4 124 * disable anti-virus real time scan
Rudi Marais 8.11 125 *
Rudi Marais 8.3 126
127 (% class="wikigeneratedid" %)
128 placeholder
129
Rudi Marais 1.1 130 = Exporting =
131
Rudi Marais 1.2 132 * no encrypted objects, they need to be decrypted or removed prior to exporting.
Rudi Marais 1.1 133
Rudi Marais 5.1 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'
Rudi Marais 6.1 156 WITH COMPRESSION,
Rudi Marais 7.1 157 STATS = 10;
Rudi Marais 6.1 158
159
Rudi Marais 5.1 160 GO
161
162
Rudi Marais 6.1 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
Rudi Marais 1.1 173 = Importing =
174
175
Rudi Marais 1.3 176 = Modifying and Troubleshooting =
Rudi Marais 2.2 177
Rudi Marais 2.7 178 * Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
179 ** place in Origin.xml
Rudi Marais 2.8 180 *** <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>
Rudi Marais 2.2 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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: