Wiki source code of migrate to managed instance

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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: