How do I say alter tables in remote clients SQLExpress database

[fr2]

Say I have a VB.NET app that uses a SQLExpress database and ship an installation CD to several clients to install the app.

If say later, there is database updates .. say
1. some new columns have been added to some tables
2. maybe new stored procedures created

Question: How can I send these updates to my clients, (like altering the tables .. etc) without the clients loosing previous data? Can u give different scenarios with pros and cons, please?

 

[RiverGuy]

You can write patch or update logic in your VB.Net application to poll your server for update scripts which would get run by your application. As far as not losing data is concerned, there are lots of ways to do this. You could code T-SQL in your script to first backup the old data and then after the DDL statements are issued, insert that data into your tables. Or you could just send out a new database with each "version" of your software and point your application to the new database each time, including a routine in your script to copy data from the old database to the new database.

Personally, I like the idea of sending out a new database with each version (provided you're not releasing updates very frequently) because it would be easier to roll back the changes if you encounter an exception.

 

[bborissov]

In your FrontEnd Update routine (the same you used to update your application) or in the application when it is started the very first time, check it the field(s) exists and add them if necessary.

IF NOT EXISTS(SELECT *
                     FROM INFORMATION_SCHEMA.Columns 
                     WHERE Table_Name  = 'Some_Table' AND
                           Column_Name = 'Some_Column'
   ALTER TABLE Some_Table ADD Some_Column .....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[fr2]

Both sound interesting .. but I think Riverguy I'd like to give your approach some thought ....

by the way has anyone heard of or used Redgate software? Supposed to do a SQLCompare of say 2 databases (A_old, B_new) and then synchronize or update A_old with B_new.


RiverGuy (MIS) 7 Jul 08 12:04
You can write patch or update logic in your VB.Net application to poll your server for update scripts which would get run by your application.
can u elabotare a bit on how to do this .. or perhaps some helpful links to read?

Thanks

 

[bborissov]

Sure, RedGate is a great tool. But what you could do if you have no access to client SQL Server?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[fr2]

ok bborissov:

1. So assuming client can send u client express database, then maybe u can use Redgate to compare and "fix" client database, then send the client database back to client.

2. But if you have many clients then this can be a problem, doing it for everbody. So maybe u can use Redgate to help script your new changes ... then put the script code in say a menu option say (Update database, or PatchUpdate) in your VB.net app, then recompile your app and send new install to client, right?

3. Also RiverGuy ...
RiverGuy (MIS) 7 Jul 08 12:04
You can write patch or update logic in your VB.Net application to poll your server for update scripts which would get run by your application.
can u elabotare a bit on how to do this .. or perhaps some helpful links to read? I'm interested in how to POLL the server, .. or setup the installation from the web .. so it is uniform for all my clients, ... instead of having to deal with each client individually. Thanks

 

[RiverGuy]

fr2,
I think we are making some assumptions that the software you are writing provides no direct path between your SQL Server and the Express installation on the customer's computer. This is because many packaged applications utilizing Express tend to be "black boxes" and the data in the Express database is completely owned by the customer. That being said, using a tool like SQL Compare would work only if you had a connection between your SQL Server and the customer's SQL Server. That's easier said than done because different companies employ different types of firewalls and other networking goodness where it could be tough to access the remote databases. Furthermore, using the Red Gate tool--you would probably have to manually fix all of the customer databases--and I think you would want an automated way to do it.

My suggestion was to create a repository accessible through the internet for updates to your application. This could be stored on a web server. You could add an XML file to the repository to contain metadata about the files which need to be downloaded. Then, write a routine in your VB app to read the metadata and decide if it needs to get a new update if one exists. If so, the VB app downloads the update and patches it. If you keep your DDL scripts in source control or saved somewhere else, then it would be trivial to make these available to the update repository. If you do all your DDL changes through Management Studio/Enterprise Manager then you're either going to have to manually create those changes in T-SQL, or employ a routine as suggested by bborissov to get the changes dynamically. My other suggestion to download a new database each time would save on some coding for the database patch routine. You might even be able to integrate the latter into Click-Once deployment if that works for you.

 

[bborissov]

Let me tell you what I do:
In my Main application class I have an UPDATEDB method.
There I read a script file (pure T-SQL) if it exists.
Then I run it against the database on the client server.
No need of special menu option.
What if your customer start to work w/o tun that menu?
Your application will bomb, isn't it?
It will expect some fields in the database that didn't exists.
So I run that script (w/o user intervention) and go further. I even delete that script file if everything is successful so no need to be ran twice. You could generate that scripts either with SSMS either with Red Gate. With Red-Gate comparison tool it is easier.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.