migrate to managed instance

Version 8.21 by Rudi Marais on 2024/03/28 18:36

 

  1. put db in readonly mode
    1. ALTER DATABASE Everest_Test SET READ_ONLY WITH NO_WAIT
       
  2. scale db to accetable performance ie dtus 500
     
  3. turn off real time scan (anti virus)
     
  4. run bcp extract scripts
    1. everest & docs parallel
    2. extract run from smallest to largest table
       
  5. run schema extracts
    1. not in parallel!
    2. https://github.com/microsoft/mssql-scripter
    3. C:\Users\alliancetest\.mssqlscripter
    4. 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
       
  6. create db
     
  7. create schemas
    1. prep scripts first remove non applicable scripting
    2. document references
    3. make sure ddl audit triggers are disabled
       
  8. disable all indexes except clustered
     
  9. run imports [parallel]
    1. imports runs from smallest to largest table
       
  10. verify record counts from pre and post
     
  11. enable indexes
     
  12. deploy new ddl audit triggers
    ​​​​​​​
  13. enable ddl trigger
     
  14. run db security scripts
    1. creates db groups and ad groups
       
  15. update all settings to new environment
     

  16.  
  1. run extract with sql-script-db.ps1 with validate set to true and raw objects set to true, only need tbl-triggers, functions and sp
    1. reason for this is to identify any procedure/triggers/functions that dont compile
  2. 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
    1. repeat step 1 and 2 until you get zero objects that can't compile
  3. check sql code for any reserved varables ie... and fix on DB
  4. run dacpac export
    1. make sure nothing is running on source DB
    2. upgrade source DB to high level for quicker export and set back to what it was once done
    3. cmd
  5. run dacpac publish to managed instance
  6.  
-- index
select concat('echo ''',SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp ',SCHEMA_NAME(schema_id), '.', name , ''' >> test.log')
from sys.tables
where type = 'U'
and is_ms_shipped = 0

--and temporal_type in (0,2);

-- export

select '$path = "E:\temp\test\everest_test"' cmd union
select '$server = "mytest.database.windows.net"'  union
select '$username = "sa"'  union
select '$password = "123456"'  union
select 'New-Item -ItemType Directory -Force -Path "$path\export_log"'  union
select 'New-Item -ItemType Directory -Force -Path "$path\data"'  

select export = concat('bcp "', DB_NAME(), '.',     
   SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
   '" out "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.bcp" ',
' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)), '.log" ',
   ' -S "', '$server','"',
   ' -N -U "$username" -P "$password"',
' | Out-Null')
from sys.tables
where type = 'U'
and is_ms_shipped = 0
--and temporal_type in (0,2);

-- import

select '$path = "E:\temp\test\everest_test"'  cmd union
select '$target_server = "sqlmi-ebs-prod.public.2a5fe6cde9a7.database.windows.net,3342"' union
select '$target_db = "TST_Staging_Everest"' union
select '$target_username = "import_test"' union
select '$target_password = ''123456''' union
select 'New-Item -ItemType Directory -Force -Path "$path\import_log"' union
select 'New-Item -ItemType Directory -Force -Path "$path\data"'

select import = concat('bcp "', '$target_db', '.',
   SCHEMA_NAME(schema_id), '.', replace(name,'$','`$'),
   '" in "$path\data\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.bcp" ',
' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', BASE64_ENCODE (cast(name as varbinary)) , '.log" ',
   ' -S "', '$target_server','"',
   ' -N -U "$target_username" -P "$target_password"',
' -b 1000 -E -k -N -q',
' | Out-Null')
from sys.tables
where type = 'U'
and is_ms_shipped = 0
--and temporal_type in (0,2);
  • disable anti-virus real time scan
  •  

placeholder

Exporting

  • no encrypted objects, they need to be decrypted or removed prior to exporting.

SQL 2012

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

EXEC XP_CMDSHELL 'net use O: \\zagpebslab.file.core.windows.net\sqlshare  /user:"localhost\zagpebslab" "access_key"'

EXEC XP_CMDSHELL 'Dir O:' 

BACKUP DATABASE Everest_TEST
TO DISK = N'O:\ENS_Everest_Test_20230721_1806.bak'
WITH  COMPRESSION,
STATS = 10;

GO

SELECT 
   session_id as SPID, command, a.text AS Query, start_time, percent_complete,
   dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE') 

GO

Importing

Modifying and Troubleshooting

  • Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
    • place in Origin.xml
      • <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>

Recalculate Signature

  • incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
  • modify Origin.xml
    • Checksum attribute for model.xml
Tags:
Created by Rudi Marais on 2023/05/18 05:54