Summary

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

Details

Page properties

Content

...	...	@@ -1,15 +1,46 @@
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. 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	+
	23	+1. create db
	24	+
	25	+1. create schemas
	26	+11. prep scripts first
	27	+11. document references
	28	+11. make sure ddl audit triggers are disabled
	29	+
	30	+1. disable all indexes except clustered
	31	+
	32	+1. run imports [parallel]
	33	+11. imports runs from smallest to largest table
	34	+​​​​​​​
	35	+1. verify record counts from pre and post
	36	+
	37	+1. enable indexes
	38	+1. enable ddl trigger
	39	+
	40	+1.
	41	+
	42	+
	43	+
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
...	...	@@ -28,10 +28,14 @@
28	28
29	29	* C:\Users\alliancetest\.mssqlscripter
30	30	* 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
	62	+
	63	+
31	31	)))
	65	+* ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT
	66	+
32	32
33		-(% class="wikigeneratedid" id="H" %)
34		- {{code language="sql"}}-- index
	68	+{{code language="sql"}}
	69	+-- index
35	35	select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log')
36	36	from sys.tables
37	37	where type = 'U'
...	...	@@ -40,9 +40,17 @@
40	40	--and temporal_type in (0,2);
41	41
42	42	-- export
43		-select export = concat('bcp ''', DB_NAME(), '.',
44		- SCHEMA_NAME(schema_id), '.', name,
45		- ''' out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ',
	78	+
	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"'
	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" ',
46	46	' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
47	47	' -S "', '$server','"',
48	48	' -N -U "$username" -P "$password"',
...	...	@@ -53,6 +53,15 @@
53	53	--and temporal_type in (0,2);
54	54
55	55	-- import
	99	+
	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
	106	+select 'New-Item -ItemType Directory -Force -Path "$path\data"'
	107	+
56	56	select import = concat('bcp "', '$target_db', '.',
57	57	SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
58	58	'" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ',
...	...	@@ -64,10 +64,13 @@
64	64	from sys.tables
65	65	where type = 'U'
66	66	and is_ms_shipped = 0
67		---and temporal_type in (0,2);{{/code}}
	119	+--and temporal_type in (0,2);
68	68
	121	+{{/code}}
69	69
	123	+
70	70	* disable anti-virus real time scan
	125	+*
71	71
72	72	(% class="wikigeneratedid" %)
73	73	placeholder