Summary

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

Details

Page properties

Content

...	...	@@ -1,15 +1,35 @@
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. scale db to accetable performance ie dtus 500
	10	+
	11	+1. turn off real time scan (anti virus)
	12	+
	13	+1. run bcp extract scripts
	14	+11. everest & docs parallel
	15	+11. extract run from smallest to largest table
	16	+
	17	+1. run schema extracts
	18	+11. not in parallel!
	19	+
	20	+1. create db
	21	+
	22	+1. create schemas
	23	+11. prep scripts first
	24	+11. document references
	25	+11. make sure ddl audit triggers are disabled
	26	+
	27	+1. disable all indexes except clustered
	28	+
	29	+1. run imports [parallel]
	30	+11. imports runs from smallest to largest table
	31	+
	32	+
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,14 +28,34 @@
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
	51	+
	52	+
31	31	)))
	54	+* ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT
	55	+
32	32
33		-(% class="wikigeneratedid" id="H" %)
34		- {{code language="sql"}}-- export
35		-select export = concat('bcp ', DB_NAME(), '.',
36		- SCHEMA_NAME(schema_id), '.', name,
37		- ' out "$path\data\', SCHEMA_NAME(schema_id), '_', name, '.bcp" ',
38		- ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', name, '.log" ',
	57	+{{code language="sql"}}
	58	+-- index
	59	+select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log')
	60	+from sys.tables
	61	+where type = 'U'
	62	+and is_ms_shipped = 0
	63	+
	64	+--and temporal_type in (0,2);
	65	+
	66	+-- export
	67	+
	68	+select '$path = "E:\temp\test\everest_test"' cmd union
	69	+select '$server = "mytest.database.windows.net"' union
	70	+select '$username = "sa"' union
	71	+select '$password = "123456"' union
	72	+select 'New-Item -ItemType Directory -Force -Path "$path\export_log"' union
	73	+select 'New-Item -ItemType Directory -Force -Path "$path\data"'
	74	+
	75	+select export = concat('bcp "', DB_NAME(), '.',
	76	+ SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
	77	+ '" out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ',
	78	+ ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
39	39	' -S "', '$server','"',
40	40	' -N -U "$username" -P "$password"',
41	41	' | Out-Null')
...	...	@@ -44,12 +44,20 @@
44	44	and is_ms_shipped = 0
45	45	--and temporal_type in (0,2);
46	46
47		-
48	48	-- import
	88	+
	89	+select '$path = "E:\temp\test\everest_test"' cmd union
	90	+select '$target_server = "sqlmi-ebs-prod.public.2a5fe6cde9a7.database.windows.net,3342"' union
	91	+select '$target_db = "TST_Staging_Everest"' union
	92	+select '$target_username = "import_test"' union
	93	+select '$target_password = ''123456''' union
	94	+select 'New-Item -ItemType Directory -Force -Path "$path\import_log"' union
	95	+select 'New-Item -ItemType Directory -Force -Path "$path\data"'
	96	+
49	49	select import = concat('bcp "', '$target_db', '.',
50		- SCHEMA_NAME(schema_id), '.', name,
51		- '" in "$path\data\', SCHEMA_NAME(schema_id), '_', name, '.bcp" ',
52		- ' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', name, '.log" ',
	98	+ SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
	99	+ '" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ',
	100	+ ' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.log" ',
53	53	' -S "', '$target_server','"',
54	54	' -N -U "$target_username" -P "$target_password"',
55	55	' -b 1000 -E -k -N -q',
...	...	@@ -57,12 +57,14 @@
57	57	from sys.tables
58	58	where type = 'U'
59	59	and is_ms_shipped = 0
60		---and temporal_type in (0,2);{{/code}}
	108	+--and temporal_type in (0,2);
61	61
	110	+{{/code}}
62	62
	112	+
63	63	* disable anti-virus real time scan
	114	+*
64	64
65		-
66	66	(% class="wikigeneratedid" %)
67	67	placeholder
68	68
...	...	@@ -123,5 +123,3 @@
123	123	* incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
124	124	* modify Origin.xml
125	125	** Checksum attribute for model.xml
126		-
127		-