migrate to managed instance

Last modified by Rudi Marais on 2024/03/28 18:51

 

  1. put db in readonly mode
    1. stop all applicable services
    2. 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. enable compresssion on applicable tables
    1. tblAudit etc
       
  9. disable all indexes except clustered
     
  10. run imports [parallel]
    1. imports runs from smallest to largest table
       
  11. verify record counts from pre and post
     
  12. enable indexes
     
  13. deploy new ddl audit triggers
    ​​​​​​​
  14. enable ddl trigger
     
  15. run db security scripts
    1. creates db groups and ad groups
       
  16. update all settings to new environment
     
  17. do audit table test
     
  18. scale db down to minimum
     
  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

  •  

-- 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);
  •  

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