SQL Server Performance

how to keep maintain data in two not identical databases via one application

Discussion in 'ALL SQL SERVER QUESTIONS' started by javed rehman, Oct 17, 2012.

  1. javed rehman New Member

    I have a unique situation that I am hoping will start a conversation in this forum. We have an old app that was written using SQL Anywhere (syabase smaller version) on the dbms side. We have now taken that sql anywhere db and converted it to sql server and our app is able to connect to it and works great. We are now planning to change our client app and rewrite it in more current tool like .net or something against the sql server db we converted from sql anywhere. The problem is that sql anywhere db we converted was created so poorly that creating a brand new app against that poor schema will cause problems in future (major problems becasue this schema basically sucks to put it mildly). We can clean up the schema and create a new database on sql server based on this new schema, the challenge is that now we have to have our current app run against the old db and be in production all along while we develop new app against the new db, once we are done we will just flip the switch and start using the new app against new db but how do we keep the data in sync between these two dbs while we are developing the new app that could take a long time. Besides keeping things in sync also keep in mind that both dbs will have diffrent schemas. We have thought about having triggers on the old db that will have logic to update the new db but that will slow things down and we will have to write tons of triggers, is there anyother way sql server can do this? Sorry for the long question.
  2. FrankKalis Moderator

    Sorry, but this approach sounds not right to me. If I understand correctly, you want to keep the new SQL Server database datawise to be in sync with the old legacy production database while you develop your application against the new SQL Server database?

    First thing I would do, is to have a non-production copy of the old legacy database on a separated server. You can set up a process that refreshes this copy nightly or however frequently you need it. This way you don't touch and affect production in any way. Then you can work on getting the new SQL Server database and the new client application right. During this phase you can develop a migration script for the data that moves the data from the old to the new database. You can even empty your new database and load it with this script from the old production copy upon each refresh. This way you get trust in your migration script. Once the new database and application are ready for production, you can put them onto the production hardware, prepare the rol out of the client application, shutdown the old application and lock the old database, run your migration script and switch on the new application and theoretically that should be it. :)
  3. javed rehman New Member

    thank u very much frank: but I need to explain something that i may have missed in my initial post, we have already migrated the database from our old sql anywhere database to sql server but with the same exact schema. The schema we now have on sql server is working but in no way is optimized. We want to create a new better schema on sql server and develop our new app against the new schema and try to keep things in sync between the old and the new schema on sql server. Now we have thought about two approches:

    1) do a real time update of data between two databases via triggers
    2) develop a batch job (update script) that will update the new database on nightly bases as you suggested.

    which one should be adopted and why?
  4. Shehap MVP, MCTS, MCITP SQL Server

    To make things more clear , production DB have become now SQL Server DB but with poor schema and you are planning to revise its schema on another DB on development but you need both DBs to be in sync …

    If so, I still can see the point view of Frank is still much valid , thereby we have used often by such cases to complete our development phase first then we jump to another phase of

    ETL (Extract Transform Load ) DWH where we can migrate all live Data entity to the new development DB with little bit of down time ,

    In order to do this , you should do the 4 steps below by the same sequence :

    · Drop all FK first with keeping them somewhere

    · Run DTS ( Data transformation service ) using import and export from SSMS or using SSIS package from inside BID ( Business intelligence development)

    · After finish , disable any user activities on DB for few minutes and run a DWH script to migrate all newly inserted or updated or deleted data while DTS run to make sure both data of source and destination is Top identical , to do this you can learn more at my blog :http://www.sqlserver-performance-tuning.com/apps/blog/show/12927173-data-warehousing-workshop-3-4-

    · Then return back FK then enable back users activities

    Kindly wok out it and let me know your feedback

Share This Page