Question

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

Answer 1

Contact Fog Creek Support

The instructions below are a bit outdated. Please contact our support team at customer-service@fogcreek.com and we'd be happy to provide more detailed instructions for converting your database.

---

NOTE: Previous version of this post included detailed instructions, but they've been removed since they were outdated and didn't always work. Please contact us and we'll work with you to provide more complete instructions.

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.