Changes for page migrate to managed instance
Last modified by Rudi Marais on 2024/03/28 18:51
Change comment:
There is no comment for this version
Summary
-
Page properties (3 modified, 0 added, 0 removed)
Details
- Page properties
-
- Title
-
... ... @@ -1,1 +1,1 @@ 1 - migrate to managed instance1 +bacpac - Parent
-
... ... @@ -1,1 +1,1 @@ 1 - technical-documentation.analysis.sql.WebHome1 +Technical Documentation.analysis.sql.WebHome - Content
-
... ... @@ -8,9 +8,9 @@ 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 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 12 + 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 16 16 11. repeat step 1 and 2 until you get zero objects that can't compile ... ... @@ -22,124 +22,14 @@ 22 22 1. run dacpac publish to managed instance 23 23 1. 24 24 25 -* ((( 26 -(% class="wikigeneratedid" id="Hhttps:2F2Fgithub.com2Fmicrosoft2Fmssql-scripter" %) 27 -[[https:~~/~~/github.com/microsoft/mssql-scripter>>https://github.com/microsoft/mssql-scripter]] 25 += = 28 28 29 -* C:\Users\alliancetest\.mssqlscripter 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 31 31 32 - 33 -))) 34 -* ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT 35 - 36 36 37 - 38 -{{code language="sql"}} 39 --- index 40 -select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log') 41 -from sys.tables 42 -where type = 'U' 43 -and is_ms_shipped = 0 44 - 45 ---and temporal_type in (0,2); 46 - 47 --- export 48 - 49 -select '$path = "E:\temp\test\everest_test"' cmd union 50 -select '$server = "mytest.database.windows.net"' union 51 -select '$username = "sa"' union 52 -select '$password = "123456"' union 53 -select 'New-Item -ItemType Directory -Force -Path "$path\export_log"' union 54 -select 'New-Item -ItemType Directory -Force -Path "$path\data"' 55 - 56 -select export = concat('bcp "', DB_NAME(), '.', 57 - SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'), 58 - '" out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ', 59 - ' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ', 60 - ' -S "', '$server','"', 61 - ' -N -U "$username" -P "$password"', 62 - ' | Out-Null') 63 -from sys.tables 64 -where type = 'U' 65 -and is_ms_shipped = 0 66 ---and temporal_type in (0,2); 67 - 68 --- import 69 - 70 -select '$path = "E:\temp\test\everest_test"' cmd union 71 -select '$target_server = "sqlmi-ebs-prod.public.2a5fe6cde9a7.database.windows.net,3342"' union 72 -select '$target_db = "TST_Staging_Everest"' union 73 -select '$target_username = "import_test"' union 74 -select '$target_password = ''123456''' union 75 -select 'New-Item -ItemType Directory -Force -Path "$path\import_log"' union 76 -select 'New-Item -ItemType Directory -Force -Path "$path\data"' 77 - 78 -select import = concat('bcp "', '$target_db', '.', 79 - SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'), 80 - '" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ', 81 - ' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.log" ', 82 - ' -S "', '$target_server','"', 83 - ' -N -U "$target_username" -P "$target_password"', 84 - ' -b 1000 -E -k -N -q', 85 - ' | Out-Null') 86 -from sys.tables 87 -where type = 'U' 88 -and is_ms_shipped = 0 89 ---and temporal_type in (0,2); 90 - 91 -{{/code}} 92 - 93 - 94 -* disable anti-virus real time scan 95 -* 96 - 97 -(% class="wikigeneratedid" %) 98 -placeholder 99 - 100 100 = Exporting = 101 101 102 102 * no encrypted objects, they need to be decrypted or removed prior to exporting. 103 103 104 -SQL 2012 105 - 106 - 107 - 108 -EXEC sp_configure 'show advanced options', 1; 109 -GO 110 -RECONFIGURE; 111 -GO 112 - 113 -EXEC sp_configure 'xp_cmdshell',1 114 -GO 115 -RECONFIGURE 116 -GO 117 - 118 - 119 -EXEC XP_CMDSHELL 'net use O: ~\~\zagpebslab.file.core.windows.net\sqlshare /user:"localhost\zagpebslab" "access_key"' 120 - 121 -EXEC XP_CMDSHELL 'Dir O:' 122 - 123 - 124 -BACKUP DATABASE Everest_TEST 125 -TO DISK = N'O:\ENS_Everest_Test_20230721_1806.bak' 126 -WITH COMPRESSION, 127 -STATS = 10; 128 - 129 - 130 -GO 131 - 132 - 133 -SELECT 134 - session_id as SPID, command, a.text AS Query, start_time, percent_complete, 135 - dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 136 -FROM sys.dm_exec_requests r 137 - CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 138 -WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE') 139 - 140 -GO 141 - 142 - 143 143 = Importing = 144 144 145 145 ... ... @@ -154,3 +154,5 @@ 154 154 * incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1 155 155 * modify Origin.xml 156 156 ** Checksum attribute for model.xml 47 + 48 +