Accessing varbinary federation key programmatically
When we have federation with varbinary federation key and we want to access that federation key from our code in sensible way it’s not that easy...
At least it wasn’t in the beginning.
Hopefully I wasn’t the only one who faced this problem…
Issue here is that when we access this varbinary (Actually stored as sqlvariant in case of federation) column from code it comes as byte array
Now when we try to convert this byte array to varchar it gives junk values
At this point I didn’t have idea to which format I need to convert.
Byte array is of no use as I can’t make sense out of it.
If I run select query on varbinary column in SQL I get output which is something like.. 0x5F7D658DF4AEAC (doesn’t look very friendly right??)
So now the problem is to get value which is similar to ‘0x5F7D658DF4AEAC’ in code so that we can do comparison and get to federation member using this federation key
How to create federation with varbinary federation key:
https://windowsazurecat.com/2011/09/sql-azure-federations-entity-framework-code-first/
Script is added at the end, use that to create federation….
Once federation is created run following command against federation root
SELECT * FROM sys.federation_member_distributions
GO
As you see range_low and range_high is hex string…
This values has data type of sqlvariant and when we access them in code they come as byte array which need to be converted in understandable form…
Below code helps in conversion
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace Azure_federation
{
class Program
{
static void Main(string[] args)
{
SqlConnection sqlconn=new SqlConnection();
string conn_str = "";
//Connection string for SQL Azure
conn_str = "Data Source=axdfescsd.database.windows.net;Initial Catalog=AdventureWorks2012_Fed;User Id=*******@axdfescsd;Password=******;";
sqlconn.ConnectionString=conn_str;
try
{
sqlconn.Open();
//Connect to federation root
SqlCommand sql_comm = new SqlCommand("USE FEDERATION ROOT WITH RESET", sqlconn);
sql_comm.ExecuteNonQuery();
//Select federation range_low
sql_comm.CommandText = "SELECT range_low FROM sys.federation_member_distributions";
SqlDataReader data = sql_comm.ExecuteReader();
while (data.Read())
{
//Range_low will be of type object convert it to byte array
Byte[] arr = (Byte[])data["range_low"];
string s = "";
//Convert each element of byte array to hex format
foreach (var b in arr)
{
s += string.Format("{0:x2}", b);
}
Console.WriteLine(“0x”+s);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
finally
{
sqlconn.Close();
Console.ReadLine();
}
}
}
}
When I run this code output will be something like:
0x
0x0000000a
0x00000014
0x0000001e
0x00000028
0x00000032
0x0000003c
0x00000046
0x00000050
So finally we are getting string which is comparable to values present in SQL Table.
It took me almost two days to figure that I need to convert Byte array into hex string and not string!!!
By the time I called customer with this finding he had already figured out this!!!
His code was little different that what I got but on similar line..
string fedstring;
//Conversion to byte array
byte[] buffer = fedreader["range_low"] as byte[];
//Here comes the different stuff
if (BitConverter.IsLittleEndian)
Array.Reverse(buffer);
if (buffer.Length==0)
fedstring = "0x";
else
{
//Convert byte buffer into long value
long value = BitConverter.ToInt64(buffer, 0);
//long to hex string
fedstring = string.Format("0x{0:X16}", value);
}
So basically we need to convert byte array to hex string and no need scratch head by converting it to string…..
Best way of not getting into this trap is use INT as federation key!!!!!!!
No more varbinary and byte array simple INT (Killing the problem from root!!!).
And in case you plan to use varbinary as federation key bookmark this page right now J
-- Create the federation named FED_1.
-- Federate on a varbinary(8) with a distribution key named range_id
CREATE FEDERATION FED_1 (range_id varbinary(8) RANGE)
GO
-- Connect to the first and only federated member
USE FEDERATION FED_1 (range_id = 0)
WITH FILTERING = OFF, RESET
GO
-- Create the table in the first federated member, this will be a federated table.
-- The federated column in this case is customer_id.
CREATE TABLE Orders
(
order_id bigint not null,
customer_id varbinary(8),
total_cost money not null,
order_date datetime not null,
primary key (order_id, customer_id)
) FEDERATED ON (range_id = customer_id)
GO
-- Insert 160 values into the federated table
DECLARE @i int
SET @i = 0
WHILE @i < 80
BEGIN
INSERT INTO Orders VALUES (@i, cast(@i as varbinary(8)), 10, getdate())
INSERT INTO Orders VALUES (@i+1, cast(@i as varbinary(8)), 20, getdate())
SET @i = @i + 1
END
GO
--Checks whether SPLIT or DROP on federation has finished or not
CREATE PROCEDURE WaitForFederationOperations
( @federation_name varchar(200) )
AS
DECLARE @i INT
SET @i = 1
WHILE @i > 0
BEGIN
SELECT @i = COUNT(*) FROM SYS.dm_federation_operations
WHERE federation_name = @federation_name
WAITFOR DELAY '00:00:01'
END
-- Create 8 federated members using the SPLIT command
-- Split must be run at the root database
USE FEDERATION ROOT WITH RESET
GO
-- range_low = -9223372036854775808, range_high = 10
ALTER FEDERATION FED_1 SPLIT AT (range_id=0x0000000A)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
-- range_low = 10, range_high = 20
ALTER FEDERATION FED_1 SPLIT AT (range_id=0x00000014)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
-- range_low = 20, range_high = 30
ALTER FEDERATION FED_1 SPLIT AT (range_id= 0x0000001E)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
-- range_low = 30, range_high = 40
ALTER FEDERATION FED_1 SPLIT AT (range_id= 0x00000028)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
-- range_low = 40, range_high = 50
ALTER FEDERATION FED_1 SPLIT AT (range_id= 0x00000032)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
-- range_low = 50, range_high = 60
ALTER FEDERATION FED_1 SPLIT AT (range_id= 0x0000003C)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
-- range_low = 60, range_high = 70
ALTER FEDERATION FED_1 SPLIT AT (range_id= 0x00000046)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
-- range_low = 70, range_high = 80
ALTER FEDERATION FED_1 SPLIT AT (range_id= 0x00000050)
GO
EXEC WaitForFederationOperations 'FED_1'
GO
Create an Orders table at the root, this table will not be federated and is used for demonstration purposes only. Typically one would not include a regular table at the root with the same name as a federated table.
USE FEDERATION ROOT WITH RESET
GO
CREATE TABLE Orders
(
order_id bigint not null,
customer_id varbinary(8),
total_cost money not null,
order_date datetime not null,
primary key (order_id, customer_id)
)
GO
INSERT INTO Orders VALUES (-1, Substring(cast(10 as varbinary(8)),0,8), -10, getdate())
INSERT INTO Orders VALUES (-1, Substring(cast(20 as varbinary(8)),0,8), -10, getdate())
INSERT INTO Orders VALUES (-1, Substring(cast(30 as varbinary(8)),0,8), -10, getdate())
GO