Wiki source code of migrate to managed instance

Version 8.27 by Rudi Marais on 2024/03/28 18:50
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.27 54
Rudi Marais 8.26 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.
64
Rudi Marais 8.1 65 * (((
Rudi Marais 8.2 66 (% class="wikigeneratedid" id="Hhttps:2F2Fgithub.com2Fmicrosoft2Fmssql-scripter" %)
67 [[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
68
Rudi Marais 8.22 69
Rudi Marais 8.1 70 )))
Rudi Marais 1.1 71
Rudi Marais 8.12 72 {{code language="sql"}}
73 -- index
Rudi Marais 8.8 74 select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log')
75 from sys.tables
76 where type = 'U'
77 and is_ms_shipped = 0
78
79 --and temporal_type in (0,2);
80
81 -- export
Rudi Marais 8.10 82
Rudi Marais 8.13 83 select '$path = "E:\temp\test\everest_test"' cmd union
84 select '$server = "mytest.database.windows.net"' union
85 select '$username = "sa"' union
86 select '$password = "123456"' union
87 select 'New-Item -ItemType Directory -Force -Path "$path\export_log"' union
88 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
Rudi Marais 8.10 89
90 select export = concat('bcp "', DB_NAME(), '.',
91 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
92 '" out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ',
Rudi Marais 8.8 93 ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
Rudi Marais 8.5 94 ' -S "', '$server','"',
95 ' -N -U "$username" -P "$password"',
96 ' | Out-Null')
Rudi Marais 8.3 97 from sys.tables
98 where type = 'U'
99 and is_ms_shipped = 0
Rudi Marais 8.5 100 --and temporal_type in (0,2);
Rudi Marais 2.8 101
Rudi Marais 8.3 102 -- import
Rudi Marais 8.10 103
Rudi Marais 8.13 104 select '$path = "E:\temp\test\everest_test"' cmd union
105 select '$target_server = "sqlmi-ebs-prod.public.2a5fe6cde9a7.database.windows.net,3342"' union
106 select '$target_db = "TST_Staging_Everest"' union
107 select '$target_username = "import_test"' union
108 select '$target_password = ''123456''' union
109 select 'New-Item -ItemType Directory -Force -Path "$path\import_log"' union
Rudi Marais 8.10 110 select 'New-Item -ItemType Directory -Force -Path "$path\data"'
111
Rudi Marais 8.5 112 select import = concat('bcp "', '$target_db', '.',
Rudi Marais 8.8 113 SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
114 '" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ',
115 ' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.log" ',
Rudi Marais 8.5 116 ' -S "', '$target_server','"',
117 ' -N -U "$target_username" -P "$target_password"',
118 ' -b 1000 -E -k -N -q',
119 ' | Out-Null')
Rudi Marais 8.3 120 from sys.tables
121 where type = 'U'
122 and is_ms_shipped = 0
Rudi Marais 8.12 123 --and temporal_type in (0,2);
Rudi Marais 8.13 124
Rudi Marais 8.12 125 {{/code}}
Rudi Marais 8.3 126
127
Rudi Marais 8.4 128 * disable anti-virus real time scan
Rudi Marais 8.11 129 *
Rudi Marais 8.3 130
131 (% class="wikigeneratedid" %)
132 placeholder
133
Rudi Marais 1.1 134 = Exporting =
135
Rudi Marais 1.2 136 * no encrypted objects, they need to be decrypted or removed prior to exporting.
Rudi Marais 1.1 137
Rudi Marais 5.1 138 SQL 2012
139
140
141
142 EXEC sp_configure 'show advanced options', 1;
143 GO
144 RECONFIGURE;
145 GO
146
147 EXEC sp_configure 'xp_cmdshell',1
148 GO
149 RECONFIGURE
150 GO
151
152
153 EXEC XP_CMDSHELL 'net use O: ~\~\zagpebslab.file.core.windows.net\sqlshare /user:"localhost\zagpebslab" "access_key"'
154
155 EXEC XP_CMDSHELL 'Dir O:'
156
157
158 BACKUP DATABASE Everest_TEST
159 TO DISK = N'O:\ENS_Everest_Test_20230721_1806.bak'
Rudi Marais 6.1 160 WITH COMPRESSION,
Rudi Marais 7.1 161 STATS = 10;
Rudi Marais 6.1 162
163
Rudi Marais 5.1 164 GO
165
166
Rudi Marais 6.1 167 SELECT 
168 session_id as SPID, command, a.text AS Query, start_time, percent_complete,
169 dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
170 FROM sys.dm_exec_requests r 
171 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
172 WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
173
174 GO
175
176
Rudi Marais 1.1 177 = Importing =
178
179
Rudi Marais 1.3 180 = Modifying and Troubleshooting =
Rudi Marais 2.2 181
Rudi Marais 2.7 182 * Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
183 ** place in Origin.xml
Rudi Marais 2.8 184 *** <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>
Rudi Marais 2.2 185
186 == Recalculate Signature ==
187
188 * incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
189 * modify Origin.xml
190 ** Checksum attribute for model.xml

Applications

Navigation

Need help?

If you need help with XWiki you can contact: