1
Vote

Invalid schema generation in Azure Federation databases

description

There seems to be a bug when generating CREATE TABLE clauses in federated databases.

Example:
CREATE TABLE [dbo].[Table](
    ...
)FEDERATED ON (...)

FEDERATED ON (...)
As you can see, the "FEDERATED ON" clause is being duplicated! If the duplicate clause is removed, everything works perfectly.

comments

ghuey wrote Nov 12, 2013 at 3:24 PM

Hi,

I am having problems reproducing this issue. I created a fedRoot db, then created a federation member. I migrated from SQL Server (adding FEDERATED ON) to the script on the ids that I wanted to federate on. That process went well and then I did a split for fun and everything went well. Then I used SQLAzureMW to generate script from my federation to a new federation and everything there worked great as well.

So I am not sure what is going on. Would it be possible for you to send me the generated script so I can look at it?

Thanks for your time,
George

jarete wrote Nov 13, 2013 at 10:51 AM

Hi George,

I can reproduce this bug with following settings in SQLAzureMW GUI:

Target Server: Azure Federation
Script Table / Data: Table Schema Only
(everything else default)

When I select a single Federation Member as a source, the generated SQL will have duplicate FEDERATED ON clauses.

I could not reproduce this bug in SQLAzureMWBatchBackup so it might be an UI problem only.

I noticed there is a small difference in these duplicate clauses. One has brackets in names and the other does not:
)FEDERATED ON ([DistributionName] = [ColumnName])

FEDERATED ON (DistributionName= ColumnName)
If you are still having problems reproducing this issue, I can send you the script privately!

Thanks!

ghuey wrote Nov 13, 2013 at 3:27 PM

Ahhh Ha! I figured it out. I bet you are running with v4.0.16.1 (the code built on SQL Server 2012 libraries). While I was running on v3.9.13.1 (code build on SQL Server 2008 R2 SP1). The difference is that SQL Server 2012 is federation aware and when SMO generates the script, it puts the FEDERATED ON statement on the CREATE TABLE. Whereas SQL Server 2008 R2 SP1 is not federation aware and I had to check federation meta data and figure out which table / column was used for federations and add my own FEDERATED ON clause. So, I now know what I need to fix. If you want to run, use v3.9.13.1 for now and I will post a fix soon.

Thanks for working with me!
George

ghuey wrote Nov 15, 2013 at 6:20 PM

Ok, you should be good to go now. Check out v4.0.17 and let me know how it works.

Thanks,
George