Summary

• Page properties (1 modified, 0 added, 0 removed)

Details

Page properties

Content

...	...	@@ -1,15 +1,61 @@
1		-* compressed file
2		-* contains ddl and dml data
3		-* doesnt support encrypted objects
4		-* cant have any stored procs with string that contain $( character sequence reserved sequence
5		-** break up sequence ie 'test$(test' become 'test$' + '(test'
6		-* [[https:~~/~~/learn.microsoft.com/en-us/azure/azure-sql/database/database-import>>https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import]]
	1	+
	2	+
7	7	* sqlpackage
8	8	** [[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]]
9	9	** [[https:~~/~~/learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download>>https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download]]
10	10	** C:\Program Files\Microsoft SQL Server\160\DAC\bin
11		-* turn off real time scan (anti virus)
12	12
	8	+
	9	+1. put db in readonly mode
	10	+11. stop all applicable services
	11	+11. ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT
	12	+
	13	+1. scale db to accetable performance ie dtus 500
	14	+
	15	+1. turn off real time scan (anti virus)
	16	+
	17	+1. run bcp extract scripts
	18	+11. everest & docs parallel
	19	+11. extract run from smallest to largest table
	20	+
	21	+1. run schema extracts
	22	+11. not in parallel!
	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
	26	+
	27	+1. create db
	28	+
	29	+1. create schemas
	30	+11. prep scripts first remove non applicable scripting
	31	+11. document references
	32	+11. make sure ddl audit triggers are disabled
	33	+
	34	+1. enable compresssion on applicable tables
	35	+11. tblAudit etc
	36	+
	37	+1. disable all indexes except clustered
	38	+
	39	+1. run imports [parallel]
	40	+11. imports runs from smallest to largest table
	41	+
	42	+1. verify record counts from pre and post
	43	+
	44	+1. enable indexes
	45	+
	46	+1. deploy new ddl audit triggers
	47	+​​​​​​​
	48	+1. enable ddl trigger
	49	+
	50	+1. run db security scripts
	51	+11. creates db groups and ad groups
	52	+
	53	+1. update all settings to new environment
	54	+
	55	+1. scale db down to minimum
	56	+
	57	+
	58	+
13	13	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
14	14	11. reason for this is to identify any procedure/triggers/functions that dont compile
15	15	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
...	...	@@ -26,10 +26,71 @@
26	26	(% class="wikigeneratedid" id="Hhttps:2F2Fgithub.com2Fmicrosoft2Fmssql-scripter" %)
27	27	[[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]]
28	28
29		-* C:\Users\alliancetest\.mssqlscripter
	75	+
30	30	)))
31	31
	78	+{{code language="sql"}}
	79	+-- index
	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
32	32
	85	+--and temporal_type in (0,2);
	86	+
	87	+-- export
	88	+
	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"'
	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" ',
	99	+ ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
	100	+ ' -S "', '$server','"',
	101	+ ' -N -U "$username" -P "$password"',
	102	+ ' | Out-Null')
	103	+from sys.tables
	104	+where type = 'U'
	105	+and is_ms_shipped = 0
	106	+--and temporal_type in (0,2);
	107	+
	108	+-- import
	109	+
	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
	116	+select 'New-Item -ItemType Directory -Force -Path "$path\data"'
	117	+
	118	+select import = concat('bcp "', '$target_db', '.',
	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" ',
	122	+ ' -S "', '$target_server','"',
	123	+ ' -N -U "$target_username" -P "$target_password"',
	124	+ ' -b 1000 -E -k -N -q',
	125	+ ' | Out-Null')
	126	+from sys.tables
	127	+where type = 'U'
	128	+and is_ms_shipped = 0
	129	+--and temporal_type in (0,2);
	130	+
	131	+{{/code}}
	132	+
	133	+
	134	+* disable anti-virus real time scan
	135	+*
	136	+
	137	+(% class="wikigeneratedid" %)
	138	+placeholder
	139	+
33	33	= Exporting =
34	34
35	35	* no encrypted objects, they need to be decrypted or removed prior to exporting.
...	...	@@ -87,5 +87,3 @@
87	87	* incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
88	88	* modify Origin.xml
89	89	** Checksum attribute for model.xml
90		-
91		-