Wiki source code of migrate to managed instance

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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: