Migration Derby zu MS SQL

HowTo Migrate PLANOUT DerbyDB

Karsten.nachbaur@opencom.de

Use Squirrel to export data.

1.Download and install (everything) Squirrel http://squirrel-sql.sourceforge.net/

2.Add Derby-Driver

3.Add alias to destination database

4.Connect to database
DANGER: Stop PLANOUT-Application-Server first.

5.Open the Object-Browser, switch to PLANOUT and open the table folder. Select all Tables.

6.By context menue choose “Scripts” then “Create Data Script”

7.Save the results to a file

Prepare exportfile for MS SQL

1.Open the file with an editor. For example: http://notepad-plus-plus.org/

2.Replace expression ‘"PLANOUT".’                with nothing.

3.Replace expression ‘{ts’                  with ‘CAST(‘

4.Replace expression ‘{d’                  with ‘CAST(‘

5.Replace expression ‘{t’                  with ‘CAST(‘

6.Replace expression ‘}’                        with ‘ AS datetimeoffset)’

7.Search for ‘BLOB(x’ and modify the T_USER-rows like this
,BLOB(x'761C02D6D1EC73DA30A64D15B100D6F7'),
,0x761C02D6D1EC73DA30A64D15B100D6F7,

Prepare PLANOUT-database on MS SQL-Server

1.Install PLANOUT and start PLANOUT-Server to create schema on the database.

2.Stop PLANOUT-Server

3.Start SQL Server Management Studio an logon SQL-Server with user planout

4.Use the destination database
SQL> USE planout
SQL> GO

5.Delete all tablecontent !DANGER! EXEC sp_MSforeachtable "delete from ? "

6.Disable foreign key constraints. EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

7.

Import data

1.Load exportfile with SQL Server Management Studio

2.Execute file

3.Pray

4.At least: EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

 

THAT’S GREAT!