SQL Server Performance

Updating the database structure

Discussion in 'Performance Tuning for SQL Server Replication' started by zizo, Sep 29, 2003.

  1. zizo New Member

    We have a large website of around 100,000 users. The site is in english.
    We are trying to 1 more language to the site, ie: users can browse the website either in English or Arabic.
    So, in our development enviroment (Our local mirror of the database which we work on), we added
    extra fields in each table carrying the arabic data.
    The problem now is how to export this new database structure to the host so that it can be available online
    with no data loss keeping in mind that the 2 databases (Our local development database & the other online one) are inconsistent.
    Do we have to re-do all those changes manually once again @ the online database, or is there a way to preserve the
    online data & @ the same time have the new tables structure.
  2. satya Moderator

    The method I followed earlier is design the new table on the database with new fields and required constraints and use DTS to import the data from old table and assign necessary indexes, drop the old table and rename new table.

    But make sure to test the approach to make sure no downtime or issues.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. Twan New Member

    alternatively

    script the changes to add columns as required and then populate them based on the mutual primary key for each record (assuming that there is one)

    i.e.



    alter table x add( col_arabic nvarchar(500) )

    update x
    set col_arabic = x2.col_arabic
    from db1.dbo.x x,
    db2.dbo.x x2
    where x.pk = x2.pk

    This assumes that both databases are or can be put on the same server (eg. backup/restore of your dev database to the prod server)

    NOTE depending on the number of record involved, you may need to batch up the update to avoid locking problems...

    DTS/rename is another option, in either case scripting is a must I would suggest

    Cheers
    Twan
  4. vbkenya New Member

    Adding new columns to a table does not cause loss of existing data. So if you just want to update the structure of the tables without any movement of data an ALTER TABLE script with the appropriate Arabic column level collation definitions for the new columns will suffice for each table.

    ALTER TABLE TABLE1
    ADD NewColumn nvarchar(200) COLLATION ......

    After that, importing data into these columns (if required) can be achieved by any of the methods described in the preceding posts i.e (DTS, cross-database updates etc.)

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com

Share This Page