Wiki source code of migrate to managed instance

Version 8.24 by Rudi Marais on 2024/03/28 18:48
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
54
Rudi Marais 8.16 55 1.
56
Rudi Marais 8.14 57
58
Rudi Marais 4.1 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
Rudi Marais 2.8 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
Rudi Marais 8.1 71 * (((
Rudi Marais 8.2 72 (% class="wikigeneratedid" id="Hhttps:2F2Fgithub.com2Fmicrosoft2Fmssql-scripter" %)
73 [[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
74
Rudi Marais 8.22 75
Rudi Marais 8.1 76 )))
Rudi Marais 1.1 77
Rudi Marais 8.12 78 {{code language="sql"}}
79 -- index
Rudi Marais 8.8 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
Rudi Marais 8.10 88
Rudi Marais 8.13 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"'
Rudi Marais 8.10 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" ',
Rudi Marais 8.8 99 ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
Rudi Marais 8.5 100 ' -S "', '$server','"',
101 ' -N -U "$username" -P "$password"',
102 ' | Out-Null')
Rudi Marais 8.3 103 from sys.tables
104 where type = 'U'
105 and is_ms_shipped = 0
Rudi Marais 8.5 106 --and temporal_type in (0,2);
Rudi Marais 2.8 107
Rudi Marais 8.3 108 -- import
Rudi Marais 8.10 109
Rudi Marais 8.13 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
Rudi Marais 8.10 116 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
117
Rudi Marais 8.5 118 select import = concat('bcp "', '$target_db', '.',
Rudi Marais 8.8 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" ',
Rudi Marais 8.5 122 ' -S "', '$target_server','"',
123 ' -N -U "$target_username" -P "$target_password"',
124 ' -b 1000 -E -k -N -q',
125 ' | Out-Null')
Rudi Marais 8.3 126 from sys.tables
127 where type = 'U'
128 and is_ms_shipped = 0
Rudi Marais 8.12 129 --and temporal_type in (0,2);
Rudi Marais 8.13 130
Rudi Marais 8.12 131 {{/code}}
Rudi Marais 8.3 132
133
Rudi Marais 8.4 134 * disable anti-virus real time scan
Rudi Marais 8.11 135 *
Rudi Marais 8.3 136
137 (% class="wikigeneratedid" %)
138 placeholder
139
Rudi Marais 1.1 140 = Exporting =
141
Rudi Marais 1.2 142 * no encrypted objects, they need to be decrypted or removed prior to exporting.
Rudi Marais 1.1 143
Rudi Marais 5.1 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'
Rudi Marais 6.1 166 WITH COMPRESSION,
Rudi Marais 7.1 167 STATS = 10;
Rudi Marais 6.1 168
169
Rudi Marais 5.1 170 GO
171
172
Rudi Marais 6.1 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
Rudi Marais 1.1 183 = Importing =
184
185
Rudi Marais 1.3 186 = Modifying and Troubleshooting =
Rudi Marais 2.2 187
Rudi Marais 2.7 188 * Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
189 ** place in Origin.xml
Rudi Marais 2.8 190 *** <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>
Rudi Marais 2.2 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: