Jump to:

10851 Posts in 2517 Topics by 1793 members

All other Modules

SilverStripe Forums » All other Modules » Sql Server in Silverstripe

Discuss all other Modules here.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 1012 Views
  • Nipun
    Avatar
    Community Member
    1 Post

    Sql Server in Silverstripe Link to this post

    Guys,

    I have my website in Silverstripe and its database is in MySql.

    Now i want to migrate MySql to Sql Server 2008

    I am aware of module in silverstripe.

    My query is :-

    1. What instructions should i keep in mind while doing it
    2. I have queries in Mysql how will that be effected.
    3. What any possible issues i can face?

    Thanks
    Nipun

  • ChubChub
    Avatar
    Community Member
    16 Posts

    Re: Sql Server in Silverstripe Link to this post

    Nipun - did you make the move from MySql to Sql Server? I started with version 2.3x and now running 2.4x and the company here wants to migrate to SqlServer2008. I like the idea that the db is running on mysql, but this is a Microsoft shop. I was hoping that more people were doing this - and more people would comment.

    If you have done this - could you reply and let me (and others) know how the experience went?

    Thanks.
    -johnr

  • apiening
    Avatar
    Community Member
    55 Posts

    Re: Sql Server in Silverstripe Link to this post

    Hey guys,

    DbPlumber has a new feature to copy data across different SilverStripe installations which are using different different adapters. Unfortunately this works for all combinations of adapters in both directions but not to SQL Server. This is due to autogenerate constraines in the identity columns of some tables. SQL Server refuses to set identity values. You could use it anyway but you'd need to tweak the db dump before using it:

    1. make sure both installs have the same code and you don't have obsolete columns in your MSSQL db, run dev/build in both

    2. run the following query to determin identity columns in your MSSQL DbPlumber to determin autogenerate constained columns

    SELECT
       TABLE_NAME + '.' + COLUMN_NAME,
       TABLE_NAME
    FROM
       INFORMATION_SCHEMA.COLUMNS
    WHERE
       TABLE_SCHEMA = 'dbo' AND
       COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

    3. export your MySQL data in MySQL dialect

    4. open the uncompressed file in the editor of your choice

    5. remove MySQL specific SET sql_mode = 'ANSI'; from the beginning of the dump

    INFO: the dump contains a block of SQL commands for every table consisting of a DELETE per table and a INSERT per record

    6. add a

    SET IDENTITY_INSERT "insert_table_name_here" ON;

    before the block for every constrained table

    7. add a

    SET IDENTITY_INSERT "insert_table_name_here" OFF;

    after the block for every constrained table

    8. your dump is now prepared for import, use DbPlumber to import dump

    BACK UP ALL DATA BEFOREHAND / USE AT YOUR OWN RISK !

    Good luck

    Andy

    1012 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.