登入
 
首頁文件庫學習園地下載支援社群論壇
IT 專業人員的技術資源 >
> 
> 
Update schema of deployed DB

問題 Update schema of deployed DB

  • 2008年10月2日 下午 07:23
     
     
    登入以投票
    0

    We are using ClickOnce to deploy a Windows application to many client machines and to allow for updates.  The application is created in Visual Studio 2008 using C#, Windows Forms, and a Sql Server Compact Edition 3.5 database file.  The database file is separate from the application, stored in the user's C:\SomeDirectory folder upon deployment, and not updated via ClickOnce with every new version of the application.  The user will add / remove data in the database file via the user interface of the application.  Occasionally we may need to make schema changes to the database and deploy those to the client machines (e.g. add a new empty table and likely primary & foreign key constraints).  Obviously we can't just write over the database file already on the client machine, or the user would lose all of his data.  What we need conceptually is some sort of merge of the new schema changes to the existing client database file.

     

    First off, is anything like that possible?  And if so, how best can we accomplish it?

     

    Second, is there a better / easier way to solve this problem in general?  This has to be a somewhat common scenario.

     

    We've looked at using the ADO.Net DataSet methods to WriteXmlSchema(), ReadXmlSchema(), and Merge().  However, if I understand correctly how those methods could be employed we would first need to load the entire database into a DataSet.  I've read in other places that doing that is generally not a good idea.  What is the alternative?  And if there is no alternative, how best can the entire database be read into a DataSet?

     

    I found the following information in a blog from Steve Lasker.  Is this the consensus that scripts should be generated and executed via the SqlCe API?  If so, is there an easy way to generate those scripts from an already existing .sdf file?  And possibly to generate a schema difference between two .sdf files?  This scenario would be that we updated an existing database by adding a few tables or something like that and would like to somehow automatically generate the TSQL script necessary to effect those changes to the original database.


    http://blogs.msdn.com/stevelasker/archive/2007/03/31/creating-your-sql-server-compact-edition-database-and-schema-in-code.aspx


    Thanks,
    Kyle

     

所有回覆

  • 2008年10月3日 上午 11:03
     
     
    登入以投票
    0
    I like to include an empty copy of the new database file with the new structure in the latest update.  Then I copy the data from the user's old database to the new empty database. It's just easier for me to wrap my head around instead of learning how to use tsql.
     
  • 2008年10月3日 下午 01:53
     
     
    登入以投票
    0

    Thanks for the reply.

     

    How do you go about copying the data from the user's old database to the new empty database?  Do you use the SqlCe API, ADO.NET, etc.?  Foreign keys would require that tables be copied in a certain order, right?  We have a lot of tables in our database right now. 

     

    Thanks,

    Kyle

     
  • 2008年10月3日 下午 03:10
    版主
     
     
    登入以投票
    0
    There is no tool to sync schemas in SQL Compact, as far as I know. You could maintain a version 1 schema and vesion 2 schema in a SQL Server, and use one of the many available tools to generate a "schema" diff script, that you can then include with the next version of your application, as described in Steve Lasker's blog above.

     

     
  • 2008年10月3日 下午 03:18
     
     
    登入以投票
    0

    Thanks for the quick response, Erik.  This sounds promising.

     

    Would you please explain in more detail how we would go about maintaining the schema versions in SQL Server?  (I'm assuming SQL Server 2008 would be required as we are using SQL Server Compact Edition 3.5?  Is that correct?  I just installed SQL Server 2008 Express Edition with the Management Studio and can load the .sdf file, but there does not appear to be a schema diff option -- maybe I'm missing it.)

     

    Also, what tools are available to generate the schema difference script?

     

    Thanks,

    Kyle

     
  • 2008年10月3日 下午 03:29
    版主
     
     
    登入以投票
    0

    I would have 2 databases in SQL Server Express (not sdf files, but "real" SQL Server databases), one for version 1 and one for version 2. Some of the tools from www.primeworks-mobile.com could help you migrate your sdf file schema to each database, for example Data Port Console. There are many tools to compare schemas, some of my colleagues have used a tool from Red Gate, SQLCompare, but there are many others. You can also track any schema changes by creating a DDL trigger in you version 2 database similar to this, you can the use the resulting table (DdlLog) to create your schema update script.

     

    Code Snippet

    CREATE TRIGGER [DdlLogTrigger]
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    SET NOCOUNT ON
    DECLARE @data XML
    SET @data = EVENTDATA()
    INSERT DdlLog
       (PostTime, DBUser, Event, TSQL)
       VALUES
       (GETDATE(),
       CONVERT(nvarchar(100), CURRENT_USER),
       @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
       @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(3000)') ) ;

    GO
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ENABLE TRIGGER [DdlLogTrigger] ON DATABASE

     

     

     

    Code Snippet

    CREATE TABLE [dbo].[DdlLog](
     [PostTime] [datetime] NULL,
     [DBUser] [nvarchar](100) NULL,
     [Event] [nvarchar](100) NULL,
     [TSQL] [nvarchar](3000) NULL
    )

     

     

     
  • 2008年10月3日 下午 04:21
     
     
    登入以投票
    0

    Yes and yes. It doesn't matter how many you have, but you just have to do them in the right order.  So, if you have a Customer table with a CustomerReferralTypeID field that must come from the CustomerReferralType table you would want to make sure you popultate the CustomerReferralType table before you populate the Customer table.  As you know you'll get errors if you don't

     

    Thanks,

     

    Vince

     

     
  • 2008年10月3日 下午 04:39
     
     
    登入以投票
    0

    I briefly checked out the Red Gate SQL Compare and according to their sales support person it is not compatible with Sql Server Compact Edition 3.5 database files.  I did try out the Data Port Console and was able to generate the TSQL script necessary to completely recreate the entire database, but it did not seem to offer a schema comparison option.  You mentioned many other tools to compare schemas.  What are some of those that will work with Sql Server Compact Edition 3.5 database files?

     

    I think we'd like to stay away from the trigger approach, if possible.  It kind of seems like a hassle to go back and forth between the .sdf file and a database in Sql Server Express.  Neither of us are DBAs, so the less we have to go murking around with code like that above, the better!  Smile

     

    I very much appreciate the help.  Hopefully we'll get this figured out soon.

     

    Thanks,

    Kyle

     
  • 2008年10月5日 上午 09:09
    版主
     
     
    登入以投票
    0
    I am not currently aware of any tools that support SQL Compact schema compare, but there are many tools for schema compare for SQL Server.

     

     
  • 2008年10月5日 下午 11:56
     
     
    登入以投票
    0

    I guess I'll try to write my own schema diff tool using the information_schema views.  Figured I could query both database files' (old and new) information_schema views and determine the differences, e.g. adding / removing tables, columns, constraints, etc.  From that I could write out to a file the TSQL necessary to effect the changes, such that if the changes were run on the old db file it would result in the new db file.  I would strictly deal with schema changes.  As a requirement, I would need for any data stored in the old database to be preserved as a result of this 'update' operation -- unless a table or column was to be removed.  Does this all sound reasonable?

     

    Thanks,

    Kyle

     
  • 2008年10月6日 下午 03:33
    版主
     
     
    登入以投票
    0

    Hi Kyle,

     

    it sounds reasonable, but a lot of work. Could this be made into a general utility for the benefit of the SQl Compact community, even with testing and input from others (codeplex)?

     

     
  • 2008年10月6日 下午 03:55
     
     
    登入以投票
    0

    I don't see why not...  I'm kind of surprised that this sort of functionality does not already exist, given that database schema updates would most certainly be part of many software projects.  That kind of brings me back around full circle to my original question, wondering if this is generally accomplished in some other way.  If this is the way to do this, then how are people out there doing it without this sort of tool?  I doubt that it's that uncommon of a scenario to have a separate database file deployed to each client machine, as it seems that is kind of the intent of Sql Compact Edition.

     

    I have not setup a codeplex project before.  Anything I should know?

     

    Thanks,

    Kyle

     
  • 2008年10月6日 下午 04:49
    版主
     
     
    登入以投票
    0

    Often applications based on SQL Compact (so far) have been Merge Replication clients, where all schema and data is pulled from as central SQL Server. If the schema changes, the application changes as well, so everything is replaced. So, no need for in-place schema migration. I think it depends on your scenario wheter this is the way to do things, but it is certainly a sensible way.

    CodePlex: https://www.codeplex.com/Project/ProjectCreation.aspx and here: http://www.codeplex.com/CodePlex/Wiki/View.aspx?title=CodePlex%20Help%20Wiki

     
  • 2008年10月6日 下午 06:36
     
     
    登入以投票
    0

    It seems like Merge Replication with Sql Compact:

    1. is for both schema and data
    2. would sync data from the client to the server

    Are those two points correct?  We do not want the data replication / synchronization -- only schema.

     

    We basically have the following database requirements:

    1. Each client computer will have its own database locally
    2. The data in each client database is not to be transfered to a central server
    3. There will be schema updates that need to be effected to all client database files, while preserving existing data
    4. The client database schema needs to be in sync with the application version -- we use ClickOnce for the application deployment and updates
    5. The database file will be shared by all users on the computer.  Multiple users may install the application on the computer (they will all have their own version of the application via ClickOnce), but they will all use the same database file.  We planned to handle this by installing the database file to a shared folder on the C:\ drive via the ClickOnce bootstrapper.  Database schema updates would have to be handled outside of ClickOnce.

    Can those requirements be met using Merge Replication?  I guess I just want to make sure we are going down the right path given our requirements.

     

    Thanks,

    Kyle

     
  • 2008年10月6日 下午 06:49
     
     
    登入以投票
    0
    These are all reasons why I recommended deploying a separate, empty, updated database file with your new updated application.  You can then simply copy the data from the old database to the new one and you don't have to worry about all that stuff.  Is it as fast?  No, but you're not going to be doing it that often.
     
  • 2008年10月6日 下午 07:28
     
     
    登入以投票
    0

    OK.  So how do you generate the code necessary to perform the data copying?  We have over fifty tables at this time and there will be more.  There are a lot of foreign key constraints, as an example, that dictate the order in which the data will have to be copied.  And if there are differences in your schema, such as column changes, that could make it difficult to perform simple direct copies from one table to another.  How do you handle those sort of situations? 

     

    Do you disable the constraints, copy the data, and then reenable the constraints again?  In theory, that would allow you to loop through all of the tables in the INFORMATION_SCHEMA.TABLES table of the new database file and for each table name, load the data from the old database file table and copy it into the new database file table.  In practice, though, how would you go about implementing this?  Since it sounds like you have done this many times and you say 'simply copy the data', could you please post some code?  I just don't see it being that simple and still robust in a way that I won't have to go through a manual process of generating the code myself each time.  I must be missing something.  Hopefully I'll be pleasantly surprised!

     

    It's not just that I don't want to manually write the data copy code myself each time because it is tedious and annoying, which it is, but because it is error prone and very inefficient.  If you just have to do something like that once, then fine.  But if you can foresee having to do the same thing potentially many times, which we do, then you should write a tool / utility / script to repeat that work for you.  You can test the script and verify that it is correct and then just run it whenever you need it again.

     

    Thanks,

    Kyle

     
  • 2008年10月7日 上午 05:01
     
     
    登入以投票
    0

    As Eric explained the easiest way is Merge Replication and that is how I am implementing now, but it doesn't sound like it will work for your scenario because it will replicate both data and schema AND your users would have to keep synchronized with the server.  So, I'll try and tell you how I accomplish this in my old VB6 application that uses a jet database. I don't think it's the most elegant solution, but it works. 

     

    First, you need to have a table in your database for versioning.  I called mine "Version".  You can call yours whatever you want.  In that table I kept track of a few things, but the field that I use for this particular situation is actually called "version".  This field just holds a string such as "1.0" or "2.0". 

     

    Before I continue I need to explain the way we receive updates for the application. It's very similar to click once, but home grown.  Basically, I wrote a routine that checks our website for a version file for the current release version of our application (very similar to the database version).  If the version file on the server is greater than the applications actual version then it downloads the latest application update.exe file and then runs it.  In the update.exe file I always had an empty copy of the latest version of our database.  This file always over writes the one on the users computer to make sure they have a copy of the latest version.  So, after the install of the update there would always be a file called "data.sys" in the users directory.  I named it data.sys, but you can name it anything you want.  The reason I named it with the .sys extension was because by default it was hidden from users and they couldn't easily delete it on accident.  Users will do crazy things some times.  Basically, this data.sys file was an empty file of the latest version of our database.  So, it was always there.  You can accomplish this with click once easier than the update method I had using VB6.

     

    Now, each time the application starts you have it check the version of the database.  If the version of the application is compatible with this version of the database then the application will just continue working.  If it's not then I would upgrade the database to the latest.  Basically, I had some code that looked like this:

     

    Dim dbVersion as String

     

    'open database code

     

    'get database version from Version field in Version table and assigne to dbVersion

    dbVersion=VersionField

     

    'Check if database needs to be upgraded

    if dbVersion <> "3.0" then

         'Notify user of upgrade

         messagebox.show("Your database need to be upgraded")

     

        'call upgrade database procedure - You might end up having different versions of UpgradeDatabaseVer? routines

        UpgradeDatabaseVer3

    end if

     

    As far as constraints and all that sort of stuff, yes it is a pain, but only the first time.  You should know from your database design which tables need to be supplied with data first because that data is required in another table to meet restraint requirements. If you miss one you will figure it out pretty quick during testing because it won't work successfully.

     

    So, lets say I have a customer table and a customertype table. The customer table has a customertypeid field that has a relationship and constraint from the customertype table.  This means I must copy the data from the old database to the new one in the customertype table before I can copy the customers.  Otherwise the transfer will fail because of constraints.

     

    Again, it's a some work setup in the beginning, but it's not that bad. And, once you have your routine setup the first time it is pretty easy to add/change.  I know this sounds crazy, but it just feels safer copying the data to a new database rather than messing with the schema in code.  I don't know why, it just doesn't feel right to me the other way. I hope this helps.

     

    Vince

     
  • 2008年10月7日 下午 02:42
     
     
    登入以投票
    0

    Thanks, Vince.  I appreciate your help.

     

    I suppose that as part of the ClickOnce installation / updates we could pull down an empty database file and copy it to the shared folder.  That would always ensure that the database version and the application version were in sync.  But we would have to prevent users from rolling back to the previous version, which can be accomplished with the minimum version.

     

    I guess the part that still gets me is having to write that copy data code.  It's not that I don't know or couldn't figure out the correct order in which to copy the tables based on the constraints -- it's just that I don't want to.    And back to my original point, it is error-prone and will most likely require extensive testing each time to guarantee it won't corrupt the users' data.  If I only had five tables in my database, OK, maybe I would just write the code to copy the tables.  But we could end up with over one hundred tables.  It's not the end of the world to do this, it just seems like there should be a better way.

     

    We'll talk this over and try to decide which way we want to go.

     

    Kyle

     

     
  • 2009年6月2日 下午 11:08
     
     
    登入以投票
    0
    Vince,

    The order problem is easily solved by deleting all the foreign-key constraints in the new database, copying the data for tables in old database to new database, and then re-creating all the foreign-key constraints in the new database.

    The only things you need to keep intact in any database schema/data upgrade scenario is the table schema, and even within that you only need to worry about the columns and their data types. Everything else about the schema like indexes, constraints (unique keys, foreign keys), and even stored procedures or user-defined functions (in case of server-based engines) can be dropped prior to data/schema upgrade and recreated after. It makes for a much cleaner upgrade process that doesn't have to take into account dependencies. Of course, creation of all those artifacts after upgrading the schema/data does have to happen in the correct order.

    Regards,

    Himanshu R. Swami
     
  • 2012年5月9日 下午 01:03
     
     
    登入以投票
    0

    If you use SQL Management Studio to access your database, every time you modify the data tables in there, you need to press the little 'script' icon in the top left.

    This generates a script that will update your database without loosing data (if that's possible).

    Just put that in a file throughout development over and over, or in several files.

    During deployment, run through all the scripts and hey presto, you're done.

    this way, during complicated data table changes, you can copy the customer's data anyway you like, sometimes splitting it off into separate tables etc.