Question

I'm running on MySQL now, but would like to run FogBugz on SQL Server 2008. How do I convert?

Answer 1

Back Up!

Back up your database before the move. Keep the backup of your original DB after the move in case you discover problems later, in particular missing Attachments.

Mind the Versions!

It's very important that you move your data between databases that were created by the same version of FogBugz. e.g. If you are using an older version of FogBugz, upgrade it to the latest version before the move. If you can't do that, make sure to install the same version for the destination database.

MySQL on Windows -> MS SQL Server on Windows

1. Backup your FogBugz database

   mysqldump fogbugz > backup.sql (you probably have to add --user=root --password=rootpass)

2. Make sure FogBugz is at the latest version. If it isn't, upgrade and then backup your database again

3. Convert your MySQL database to Microsoft SQL Server format with ESF Database Convert or a similar tool

4. Follow the steps in this guide for saving and attaching your new SQL Server database. Just skip the stuff in Step 2 about FogBugz On Demand. Do make sure to put your new SQL Server mdf file in the right place.

5. Point FogBugz at the new database:

   1. Log into FogBugz as an administrator

   2. Go to Admin -> Site -> Database tab

   3. Click the SQL Server tab and enter the server name (including the instance, if necessary e.g. MYSERVER\SQLEXPRESS) and the database name

FogBugz will try connecting to the SQL server database as the user you chose for it to run as. If you set the DB owner to the FogBugz user in step 4 above, you should be up and running.

MySQL on Linux/Unix -> MS SQL Server on Windows

If you are moving from MySQL on Linux, the steps are the same, but you will also need to do the following:

1. Clear all users' passwords. The hashing algorithm differs between Windows and *nix versions of FogBugz. Run the following at a MySQL prompt

   UPDATE Person SET sPassword = ''

2. Fix character encoding using this guide to get everything into Unicode (UTF-8), but make sure to turn on MySQL warnings. If you have mixed encodings (some UTF-8 and some latin1) in your database, it can truncate data in fields at the point of the first encoding error.

   If this happens, it means that some of your data is in UTF-8 (text entered through the FogBugz web interface) and some is not (including the text of incoming email messages). You can do the following to selectively convert it, splitting by the fEmail flag. This procedure was developed by a user and he provided this disclaimer: "I may not have perfect recall of exactly what I did, but I think this is pretty close. I obviously wasn't aiming to come up with a fully general solution, so YMMV."

   To do the conversion, try the following:

   1. Divide the tables into 3 categories (non-UTF8, mostly-UTF8 and BugEvent, which contains a mixture of both).

   2. Dump the data into a separate SQL file for each table, treating each category of table slightly differently.

   3. Hand-edit a few records in the Bug table.

   4. Reload the data into a new, all-UTF8 MySQL database.

Your MySQL database is now ready to be converted to SQL Server format. Go back up to step 3 in the Windows -> Windows instructions above.

Answer 2

We use ESF Database Convert to convert MySQL databases into SQL Server databases for loading into FogBugz On Demand. You should be able to do so for your local install, too.

Answer 3

We had the following configuration:

-MySQL

-IIS

-LDAP Authentication (Active Directory)

Here's how we just did it:

1. Run the FogBugz setup and create a brand new database
2. Use Microsoft SQL Server Migration Assistant 2005 for MySQL or create a sql dump to get the DDL of your plugin tables.
3. Download the MySQL ODBC and install it on the new SQL Server.
4. Create a linked server to the MySQL database. http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008

5. Run create and run these stored procs:

   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[CopyFogBugzData]
   @TABLE NVARCHAR(MAX)
   AS
   BEGIN
   DECLARE @sqlCommand nvarchar(max)
   DECLARE @columnList nvarchar(max)

   print @Table

   IF (select count(*) from openquery(fogbugz,'SHOW TABLES FROM fogbugz')where tables_in_fogbugz = @Table) < 1 BEGIN PRINT @Table + ' does not exist in source db'; RETURN -1 END

   SET @ColumnList = REPLACE(
   (
   SELECT
   Column_Name AS [data()]
   FROM
   information_schema.columns
   WHERE table_name = @table
   FOR XML PATH ('')
   ), ' ', ',')

   SET @sqlCommand = N'TRUNCATE TABLE ' + @Table + ' ';
   if OBJECTPROPERTY(object_id(@table), 'TableHasIdentity') = 1
   BEGIN
   SET @sqlCommand = @sqlCommand + N'SET IDENTITY_INSERT ' + @Table + N' on; ' --id and version table is not auto identity
   END

   SET @sqlCommand = @sqlcommand + N' INSERT INTO ' + @Table + N' (' + @columnList + N') '

   SET @sqlCommand = @sqlcommand + N' SELECT ' + @columnList + N' FROM openquery(fogbugz,''select * from ' + @Table + N''');'
   if OBJECTPROPERTY(object_id(@table), 'TableHasIdentity') = 1
   BEGIN
   SET @sqlCommand = @sqlcommand + N' SET IDENTITY_INSERT ' + @Table + N' off; '
   END

   print @sqlcommand;
   EXECUTE (@sqlcommand)

   END

   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CopyALLFogBugzData] AS

   BEGIN

   exec sp_MSforeachtable 'DECLARE @Table NVARCHAR(255); Set @Table = (SUBSTRING(''?'',8, LEN(''?'') - 8)); exec CopyFogBugzData @Table' END GO

These procs will iterate over every table in your new fogbugz database. If this table exists in the source db in MySQL, it'll truncate the destination table and insert the data.