Question

Are there any issues with the Access Upsizing Wizard (moving a FogBugz database from Access to SQL server).

Answer 1

By using the Access Upsizing Wizard you can move your FogBugz database from Access to SQL server. 

There are many, many sneaky little ways for the "Access Upsize" process to go wrong without being noticed.

If it goes wrong in any one of these ways you will not be able to install licenses.

And, you will not be given an error message as to why the licenses were not installed (due to security involved with installing licenses). You will simply be forever prompted to install new licenses.

Whether you used the upsizing wizard or not, in SQL Server your Licenses table must ultimately have the following design (FogBugz 4.0 shown here):

Note that ixLicense is the identity for the table.

Before contacting technical support, please take a second look at the above screenshot. Note both the data type and the length attributes. Are they the same as in your Licenses table in SQL Server?

The Access Upsizing Wizard (Access2002 SP2) converts the sSignature field (OLE Object) to a field with Data Type 'Image', you will need to change this to Data Type 'binary. 

Instead of using the Upsizing Wizard, we recommend you follow the simple instructions below to import your data.

If you already have a FogBugz installation that uses Access, you can run FogBugz Setup for the purpose of setting everything up in SQL Server (it will create the database, hook up a login for the database, and set up full-text search). If you want to move your FogBugz installation to a new server in the same step as you upgrade from Access to SQL Server, well, then you needed to run Setup anyway to create the IIS components anyway; but if you already have FogBugz installed and don't need a new IIS components created (e.g. a new FogBugz website), you still need to run setup to have it create the database, but after running setup you can remove IIS stuff that Setup created.

1. Run FogBugz setup to create a new installation of FogBugz, specifying SQL Server. Install one license order and log in, to be sure that it successfully created the database for you. The database creation is completed in the browser, not the setup executable (i.e. it is completed by the ASP pages).  
2. Unless you want to use this newly created FogBugz IIS website, you can now remove the web portions of this FogBugz installation: in IIS control panel, delete the FogBugz virtual directory. (If it is the root of the website, just click "remove" for the application.) Delete the FogBugz folder (in Program Files). If you get an "in use" error, stop the FogBugz Maintenence service and the Fog Creek Dispatcho service (if present - this is a FogBugz 3.0-only component) and make sure the virtual directory is deleted and restart IIS, then try again. Leave the FogBugz database intact.
3. In Enterprise Manager go to your FogBugz database, right click on Tables and select 'All Tasks->Import Data'.  You should see the DTS Import/ Export Wizard.
   
   
   
4. When it asks you to choose a Data Source, select "Microsoft Access" and point it to your .mdb file. Click Next.  
   
   
   
5. When it asks to choose a Destination, the default should be correct (i.e. it's the database that you right clicked on in step 3 above).  Double-check to make sure the Data Source is 'Microsoft OLE DB Provider for SQL Server', and that the server listed is the server you want to import to, and finally that the database is 'FogBugz'. Click Next.
   
   
   
   
6. Choose "Copy tables and views" and click Next. Click "Select All".
7. Choose to "Run immediately" and click Next.
8. Take a look at the design of the Licenses table. Look at ixLicense, and look below on that popup - is identity set to yes? It needs to be. If it is not, your scenario is slightly different from what's described above.
9. There might be a few tables that don't import due to NULLs in the columns in the Access database. If so you may need to open the Access database, find those records and change the NULLs to be the default values for those columns. This has been observed to happen with the Person, Project, and Subscription tables. In particular, in the Person table, the person with ixPerson=0 has almost all NULLs (this is a built-in user). For example ixSubscription may be missing from the Subscriptions table, make sure that field exists and is an int with a size of 4. If tables did fail to import, run DTS again following the same steps as above, but this time import only those tables.   

Answer 2

Three easy steps!

1. Install FogBugz to SQL Server. Once you have the browser up asking you for Licenses, you can stop.
2. Grab this truncate.sql script and run it against the newly created database.
3. Use the upsize wizard to just copy the DATA only into the new FogBugz database.

Answer 3

Thanks for the update.

How do you do step 3? Is it being done in Access itself?