Wiki source code of migrate to managed instance

Last modified by Rudi Marais on 2024/03/28 18:51

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