Installation of global database

Posted by Phil on Wed 08 of Jul, 2009 21:29 GMT Germany

I have made some changes to the global database installation in version 4.65 beta and now I have integrated these changes into the current version (4.60.0040) as well.

I have been receiving more and more reports of errors connecting to the global database, mostly - I think - under Windows Vista, and it's time to fix this problem.

Up to now, the global database has been implemented using a Microsoft Access 2000 database, using the Jet 4.0 database engine. The Jet database engine is now a deprecated component. You may even have to download and install Jet 4.0 to get this to work at all.

It has always been possible to migrate the database to SQL Server and specify a custom connection string in the Add-In's settings dialog (even in the VB6 Add-In). If several members of a team use the Add-In, this would always be a good idea. About a year ago I wrote an article on how to transfer the global database to SQL server (although I'm not sure that this transfers the constraints and indices correctly).

Basically, I would like to move the global database to SQL Server for all users, but I don't really want to add SQL Server Express to my installation. However, the default installation of Visual Studio installs SQL Server Express 2005 anyway, so most users of the Add-In are going to have it installed.

The new installation contains a database file for SQL Server 2005, which it copies to Common App Data\MultiLang. If SQL Server is installed on the local system, it uses the SMO interface to try to attach the database file to an existing instance of SQL Server.

I can see that some users might object to this. If there are multiple SQL Server instances, then it just uses the first instance that it finds (and can connect to). If you have a better idea, let me know. However, I want an installation which runs without asking cryptic questions and simply installs a system that works.

If attaching the database to SQL Server fails, then the installation falls back to the old Access database.

I know that the current installation has some problems:

  • it only works with SQL Server Express 2005 and not 2008. Apparently if an application references the SQLServer.SMO assembly from SQL Server 2008, it is compatible with SQL Server 2005, but not the other way around. That seems to me to be a screwed up approach to compatibility, but there you go.
  • it does not import data from an existing (access) global database file.


I will be trying to sort out these issues in the next few days (or weeks).

Phil

Reads: 32292
Link
Posted by stegess on Thu 09 of Jul, 2009 13:19 GMT

Phil,
This is great. We really want to use a SQL Server 2005 database that is accessed by multiple
users, so that all developers have access to the same global database. Hopefully
that will be possible?

Any chance that you will roll these changes into the Visual Studio 2003 version? Some of us are still working in that era... sad

Link

Posted by stegess on Wed 29 of Jul, 2009 19:01 GMT


Any thoughs on support of the Visual Studio 2003 version?

Link
Posted by Phil on Wed 29 of Jul, 2009 19:10 GMT Germany

Sorry, I haven't done anything about this yet. It's basically an installation problem. I don't think I will have to change the Add-In at all. I will take a look at it straight away.

Phil

Link
Posted by Phil on Wed 29 of Jul, 2009 19:58 GMT Germany

I have now uploaded a new version for Visual Studio 2003 (4.01.0010), which will use SQL Server for the global database, if possible.

It installs a database file to common application data directory, for example:
c:\Documents and Settings\All Users\Application Data\MultiLang\MultiLang_GlobalDatabase_Data.mdf

(Application Data is actually a hidden directory, although I have no idea why.)

It will attach the database file to a local instance of SQL Server 2005 or 2008 if possible. If this fails, it continues to use the Access database.

If you want to transfer it to an instance of SQL Server on a different system, you can detach the database using Management Studio Express (for example), copy it to another server, and attach it there.

You can configure the connection string in the Add-In's settings dialog. Next to the field for the connection string is a button to start the standard data link dialog.

Phil

Link

Posted by Phil on Sat 03 of Oct, 2009 17:28 GMT Germany

I have changed my mind on this issue and made another (fairly major) change.

Starting with Version 4.65.0018 (for VS 2005/2008) the Add-In supports SQL Server Compact 3.5 for the global database and uses this as the default configuration.

Access and SQL Server are still supported, but the database files are no longer part of the installation.

Since I introduced the version which attached a database file to an existing instance of SQL Server (express), my sales have fallen. I don't know whether there is a causal relationship between the two, but I think there might be. If so, then it is a killer argument against this method.

The new version requires that SQL Server Compact 3.5 be installed on the system. If it is not already installed, then it will be downloaded and installed automatically. This might also cause installation problems, but I think a lot of Visual Studio developers will have it installed already.

I intend to make a separate installation for the SQL Server database file, which will attach it to an SQL Server and update the connection settings for the Add-In. I will also make a link for the database file only, for user who know what they are doing.

The Access version is only supported for backward compatibility.

Note that if you install over an existing version, the connection settings for the global database are not changed, so you will continue to work with the same database as before.

If you have problems with the installation, please let me know.

Phil

Link