Using StrataBugs with SQL Server Express
SQL Server Express is a database server product available from Microsoft. This edition of SQL Server is free to use and provides a database platform for databases up to 10Gb in size. This is a guide to installing and using it with StrataBugs v2.
Downloading the installation package and installing
This is a non-trivial installation, so allow plenty of time to download and configure the software. During the course of installation you may be required to install updates for other dependencies. Go to the Express page: http://www.microsoft.com/sqlserver/en/us/editions/express.aspx. Click the download link, and select the version which matches your server. If you are using a 64-bit server/operating system, use the 64-bit download, otherwise choose the 32-bit.
Select the option "Express with tools". Warning: the download is approx. 700Mb. When the download completes, press Launch in the download manager.
Select the "New installation" option. Select at least the "Database Engine Services" and "Management Tools - Basic" in the feature options setup. Run through all the setup steps. When the setup completes you will have a running instance, with a system database but no database objects.
Migrating an Access database
There are three methods of data migration. The most obvious route would be to use the "Import and Export data" wizard which is available from the menu after you have installed SQL Server. This should NOT be used, however, since it will only import the database tables and their data, but not the set of rules, constraint and relationships which define how the data are related, and protect its integrity.
The second method is to open the Access database using the Access application and use the "Move Data" SQL Server Wizard on the toolbar. This may work, depending on the version of Access (and SQL Server) you are running, but you might also get the message: "The Upsizing Wizard does not work with the version of Microsoft SQL Server to which your Access project is connected. See the Microsoft Office Update Web site for the latest information and downloads". Unfortunately, the web site location has changed, and it is not at all clear if it is possible to resolve, at least using Access 2007 and SQL Server 2012. If this method does work for you, then it is a reliable route to migration (we have used it successfully to SQL Server 2008).
The third method involves downloading another tool the Sql Server Migration Assistant (SSMA) from this link: http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#Access
You will need to download, install locally, and go through a (free) licensing process. Once it's installed and running, you can pick up your sbugs2-0.mdb database file and migrate it to a new database on your newly created instance. This process might take some time to complete, depending on the size of your database.
Once this process has finished, you will now have created the database.
Connecting to the database
Start up StrataBugs v2. On the Manage Connections dialog, Add a new Connection. Type in a name for the connection and press ODBC to create an ODBC data source. You will need to press Add to add a new data source, and select SQL Server Native client as the driver. Specify the name of the data source (which can be the same as the StrataBugs connection name), and the database server name. The next dialog shows the authentication - if you are using separate Windows accounts then it is simpler to authenticate through the Windows usernames.
Since this is a server based database you can set up remote connections from any LAN or WAN client, but you may need to configure your firewall to allow requests through.
Once connected, the database should look identical to how it looked using the local Access database.
Starting up and Shutting down, backup and restore
The database server needs to be started and and shut down - use the SQL Server Configuration Manager application for this. The SQL Server Browser will need to be started, as well as the SQL Server process. There are options here for manual or automatic startup when the system is booted.
There are utilities for backup and restore in the Microsoft SQL Server Management Studio application. You create a backup device and specify the backup media. If you are using the Express edition, you may not be able to start the SQL Server Agent process - but this is possible with other editions and would allow scheduling of backups.