Wiki source code of migrate to managed instance

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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: