Search code examples
sql-servervisual-studio-2012azure-sql-databasefederated-table

SQL Azure - Determine which tables are federated

I'm new to SQL Azure and in the early stages of developing an application, so my schema is changing frequently. I started out by creating the root database and executing queries against it like these

CREATE TABLE [dbo].[Clients] (
    [ClientId]    UNIQUEIDENTIFIER NOT NULL primary key clustered default newid(),
    [ClientName]  NVARCHAR (MAX)   NULL
);

go

create federation ClientFederation(cid uniqueidentifier range)
 go

use federation ClientFederation(cid='00000000-0000-0000-0000-000000000000') WITH RESET, FILTERING=OFF
 go

CREATE TABLE [dbo].[Stuff] (
    [StuffId]   uniqueidentifier not null default newid(),
    [ClientId] UNIQUEIDENTIFIER NOT NULL default federation_filtering_value('cid'),
    [StuffName] NVARCHAR (50)    NOT NULL,
    -- bunch (20+) of other fields
    primary key clustered (StuffId, ClientId ASC)
) FEDERATED ON (cid=ClientId);

And that worked out pretty well for the most part. Stuff is just one table among many similar tables in the federation (and not the real name either)

Well, then like I said, my schema is changing quite frequently, so to change the schema, I'm connecting to the federation member in VS2012 and right-clicking on the table and choosing "View Code" which renders something like this:

CREATE TABLE [dbo].[Stuff] (
    [StuffId]   uniqueidentifier not null default newid(),
    [ClientId] UNIQUEIDENTIFIER NOT NULL default federation_filtering_value('cid'),
    [StuffName] NVARCHAR (50)    NOT NULL,
    -- bunch (20+) of other fields
    primary key clustered (StuffId, ClientId ASC)
)

Note, the only thing different is that after the close parentheses, it no longer says FEDERATED ON (cid=ClientId);. I assumed this was because I'm already connected to a specific federation member so it already knew that information. The weird part is when I tried to run some .net code against it. I'd execute the following code from my app:

cn.Execute(string.Format("USE FEDERATION {0}({1}='{2}') WITH RESET, FILTERING={3}", federationName, distributionName, key, filtered ? "ON" : "OFF"));

and then using dapper:

cn.Query("INSERT Stuff(StuffId, StuffName) VALUES (@StuffId, @stuffName); SELECT * FROM Stuff WHERE StuffId=@stuffId", p); // p has the parameters

but then I'd get the following error message:

DML statements are not supported on non-federated tables in a filtered connection.

Wut? My table is federated, remember? Also, similar code worked wonderfully with other tables. The strange thing about Stuff is that its schema changed A LOT recently, so it seems to me like maybe me connecting to the federation member directly in VS2012 and making changes there somehow made it not a federated table anymore (there are 3 types of tables in a federated database: http://convective.wordpress.com/2012/03/05/introduction-to-sql-azure-federations/).

So, since I'm early in development, there really isn't anything important in Stuff so I went ahead and copied its CREATE TABLE code and dropped it completely it from that member, went back to the root database and re-executed the code listed above at the top with the FEDERATED ON (ClientId=cid) statement again and then re-ran the insert statement from my app and it worked wonderfully!!

So, clearly something happened to make my table not be "federated" anymore. In the end my questions are pretty simple:

  • Is there a query that I can run on the root database or maybe on the federation member to tell me which tables are federated and which aren't?
  • Also, can anyone tell me why my once-federated table is not federated anymore? Because obviously, I may make schema changes in the far future and will not be able to just drop the table and start over, so it would be nice to know what I'm doing wrong.
Solution

  • 1 you can look at sys.federated_table_columns to tell which tables are federated in members.

    2 my guess is you are losing the property because VS is recreating the tables without the federated on clause. unfortunately, vs is not integrated fully into SQLazure so yo need to watch out for these scenarios. otherwise, there is no reason to lose or change the federated on property of a table. you can change all other properties on a federated table like evolve your schema etc. but you either create the table federated and it stays that way or you create it as a reference table and it stays that way.

    Hope this helps