Our client has a requirement that certain data that is usually going to be in a central SQL Server database be "movable" on-demand to a local SQL Express instance if a) the network is down or b) they want to take a laptop offline for on-site client work.



I already have my database split into 2 databases, 1 for tables that will ALWAYS be local (this is an app that collects data from medical diagnostic instruments) and 1 for SF role based security (SFS*) and some semi-static lookup tables.



On a daily basis I want the app to replace the local copy of the "movable" database with what is on the server. I'm assuming I can use an INI or XML file to store the last date/time the data was copied down so this would normally happen only once a day, but could be done on demand from the menu as well.



If, at any time, the user needs to run offline either due to the network or server being unavailable OR they need to "load and go" I want to make this process as seamless as possible. Again, the local copy of the data that is normally updated on the server would always get replaced by the server, never the other way. In other words, I'm not trying to "sync" data, just take it offline as needed.



Any thoughts on how to make this simple? (for me and the users ) Again, I have my connections set up to access 2 databases right now and I have 2 connection "sets" - 1 for security and lookups on server and one where all data is in localhost\SQLEXPRESS - I'm just not sure how to do the copy (actually, replace) of the local data on demand or automatically once a day. Should this just be a set of straight SQL commands that are available based on some sort of "online" flag?



Thanks,

Randy

Hi,

Not on the issue of StrataFrame and the connection string but just be aware (maybe you are already) that Express doesn't support SQL Agent. Also, when connecting a front end (Express) to a backend SQL Server 2005 you 'may' need a CAL for the client.

Cheers, Peter

Yeah, not planning to use SQL failover or replication. Just want to build the copy and dynamic connection stuff into the app if possible. Talking very small amounts of data to be copied to each PC once a day. We would keep it all local but they want the convenience of maintaining security and global lookup values centrally but still have the ability to take this data offline if necessary. The application itself is mostly standalone as the data collected from instruments does not need to be shared as it's mostly temporary data until test result output files are created for later uploading into a source control repository.

The local EXPRESS database will not be intimately aware of the server SQL 2005.



Thanks,

Randy

Randy,



As to how to change connections, it seems to me that you have all the BOs you need, i.e. the data on the server and the data locally will both use the same schema, just on different servers (one local, one on a server somewhere). If this is the case, all you'd have to do is create a server datasource, and then instantiate your existing BOs using this new datasource. You could then have one instance of a BO connected to the local data and one to the server.



In AppMain, in SetDataSources, you'd define a data source for the server and for the local sql server express





When you need access to the server data, you'd just create a BO that uses the server key. You could of course also do this via property setting, if the BO is on a form or component. To contrast, you could also have another instance of the same BO, connected locally too.





The MoveData() method would be a custom method that does whatever needs to be done to move the data and is just an example to show that you could have to instances of the same BO, connected to different servers.



I don't know if this is what you'd need, but hopefully it sparks some ideas.

Yes, this definitely gives me a place to start looking. Thanks!

Any time

There are a few things that you'll have to do that are not very easy when using a business object, Randy.  One is that if you are copying the data manually, you'll have to use IDENTITY INSERT ON in order to inser the records (or they'll be assigned new PKs every time you insert a record).  The business objects will not automatically do this, so you'll have to copy the data manually. 

The simplest solution (and the fastest) would probably be to set up an agent task on your main server (when you do maintenance) to take the DB offline and copy it's .mdf file to a network share.  Then, once a day, the clients would detatch their database from within their SqlExpress copies, copy over their copies with the one from the network share, and re-attach it.  SQL Server and SQL Server Express use the same .mdf file format, so detatching one database and re-attaching it is cake... a few simple commands.  It would require less code and would be much faster than running an insert for every record that you need to copy over.

Just another direction you might want to look into