Wednesday, November 14, 2012

Follow Up: FileTable with AlwaysOn AGs – Bug

I wrote a post a while back talking about two issues with FileTable in SQL right now and said that they would be fixed in SP1… Well… Service Pack 1 is released now, but unfortunately one of the fixes didn’t make it in, as it posed different problems, up to creating BSODs on the system. The checkpoint issue is fixed, so failover works seamlessly now, what is still missing is the sync issue.

The way this leftover issue presents itself is during a failover scenario where clients still can connect to the old primary. If the old primary is completely unreachable your data will be safe. So I know the workaround is shitty, but for now I can only advise you to not do manual failovers and in case you need to move a DB for maintenance reasons you do so by either cutting the network link to the old primary box or send it into a BSOD. Again, this is shitty, but at least it is safe. And it will still give you high availability…

I’ll keep you posted on the progress of the resolution.

Wednesday, October 17, 2012

Is that DB online?

Ever had the need to figure out if the DB you are connecting to is actually online? Like for example if you have a SQL Agent Job that runs on top of a mirrored database? That is a fairly easy task after all when you only consider Mirroring or LogShipping, in that case you could just query sys.databases and look at the state. But once you have AlwaysOn involved the thing gets a little tricky, as AlwaysOn Availability Groups don’t set the state right…

So here is the simple way of doing it right for all versions starting with Yukon up to Denali:

CREATE FUNCTION [dbo].[ufn_IsDatabaseOnline](
    @dbname sysname) RETURNS bit
AS
BEGIN
    DECLARE @RetVal bit
    SET @RetVal=0
    DECLARE @Role int

    select @Role=mirroring_role from sys.database_mirroring m
    inner join sys.databases d ON m.database_id=d.database_id
    where mirroring_guid is not null
    AND d.name=@dbname

    -- Check if Mirror and Principal
    if (@Role IS NOT NULL)
        if (@Role=1)
            SET @RetVal=1
    IF (SELECT @@MICROSOFTVERSION / 0x01000000)>=11
    BEGIN
        -- Only check HADRON if Version is 11 or higher
        select @Role=rs.role from sys.dm_hadr_database_replica_states dr
        inner join sys.dm_hadr_availability_replica_states rs on dr.group_id=rs.group_id
        inner join sys.databases d ON dr.database_id=d.database_id
        WHERE dr.is_local=1 AND rs.is_local=1
        AND d.name=@dbname

        -- Check if HADRON and Principal
        if (@Role IS NOT NULL)
            if (@Role=1)
                SET @RetVal=1
    END

    -- Handle Non-Mirrored/HADRed DBs
    IF (@Role IS NULL)
    BEGIN
        IF(SELECT state FROM Sys.databases WHERE name=@dbname)=0
            SET @RetVal=1
    END

    RETURN @RetVal
END

Monday, August 13, 2012

Copying permissions

As it gets asked time and again on forums… Here is a script to copy all permissions from a database:

SET NOCOUNT ON
DECLARE    @OldUser sysname, @NewUser sysname

DECLARE PC CURSOR FOR SELECT name FROM sys.database_principals WHERE Principal_ID>4 AND is_fixed_role=0

OPEN PC
FETCH NEXT FROM PC INTO @OldUser

WHILE @@FETCH_STATUS=0
BEGIN

SET    @NewUser = @OldUser

SELECT '-- '+@OldUser AS '-- Current User'

SELECT    'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'


SELECT    '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'


SELECT    'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE    USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC


SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.objects AS obj
    ON perm.major_id = obj.[object_id]
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
    sys.columns AS cl
    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE    usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC


SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1)
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
WHERE    usr.name = @OldUser
AND    perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

FETCH NEXT FROM PC INTO @OldUser
END
CLOSE PC
DEALLOCATE PC

Friday, July 27, 2012

FileTable with AlwaysOn AGs – Bug

After a long while of testing and troubleshooting we identified two bugs in SQL Server that can hit you if you run FileTables in conjunction with AlwaysOn Availability Groups. One concerns the way Checkpoints are done in FileStream, which could lead to very long failover times, the other one concerns a handle issue that could lead to the system running out of sync without noticing.

Right now there is no feasible workaround to both problems, but Microsoft has already confirmed that both issues will be fixed in Service Pack 1.

If you should encounter this problem and need help working around it please ping me and I will share the necessary details.

Monday, April 23, 2012

Logshipping without a Windows Fileserver

I’ve been quiet for quite a while now, but not due to lack of activity… I struggled a long time with a problem in my logshipping configuration: The fileserver as a single point of failure in the middle. Now, finally, I found a solution for that, which doesn’t require the use of expensive disk subsystems to get geo-strechted. And (as always when I have such ideas…) I tried to put it into a blog post, unfortunately only to get to a point where I gave up because of the simple complexity of the idea. Does it mean I gave up the project? Of course not… I just changed the delivery model.

So behold… My first whitepaper:

Logshipping Fileserver within SQL

Enjoy.

Monday, February 6, 2012

Optimized… in Assembler

OK, now for the fanatics as always: The same thing I just posted, but in assembler:

BitReverse PROC
PUSH r13

MOV rax, rcx
MOV r13, rcx
SHR rax, 1
AND rax, 0x5555555555555555
AND r13, 0x5555555555555555
SHL r13, 1
OR rax, r13

MOV r13, rax
SHR rax, 2
AND rax, 0x3333333333333333
AND r13, 0x3333333333333333
SHL r13, 2
OR rax, r13

MOV r13, rax
SHR rax, 4
AND rax, 0x0F0F0F0F0F0F0F0F
AND r13, 0x0F0F0F0F0F0F0F0F
SHL r13, 4
OR rax, r13

MOV r13, rax
SHR rax, 8
AND rax, 0x00FF00FF00FF00FF
AND r13, 0x00FF00FF00FF00FF
SHL r13, 8
OR rax, r13

MOV r13, rax
SHR rax, 16
AND rax, 0x0000FFFF0000FFFF
AND r13, 0x0000FFFF0000FFFF
SHL r13, 16
OR rax, r13

MOV r13, rax
SHR rax, 32
SHL r13, 32
OR rax, r13

POP r13

ret

BitReverse ENDP

Optimizing the BitReverse

Well well well, seems finally a way emerged to do the reversing without the need of a loop. And here is the trick:

Figure this simple starting value (16 bit for demo, works the same with 64 of course):
1010000010100000
Now let’s do a little bit math:
1) We just flip all odd and even bits using a 0101010101010101 mask. For this we use the original value twice, one time shifted left by 1 bit, one time shifted right, then ANDed with the mask and back ORed together. Here is the code:
i=((i>>1) & 0x5555) | ((i & 0x5555)<<1);
What does that do? Let’s break it up:
i>>1 translates to: 0101000001010000. Now do the AND 0x5555:
0101000001010000
0101010101010101
result:
0101000001010000.
The original ANDed with 0x5555 means:
1010000010100000
0101010101010101
result:
0000000000000000
Now OR those together and you get 0101000001010000

2) Now we use 0011001100110011 as bit mask and do it again, basically flipping pairs:
i=((i>>2) & 0x3333) | ((i & 0x3333)<<2)
First half:
0101000001010000 >>2 –> 0001010000010100
AND 0011001100110011
result: 0001000000010000
Second half:
0101000001010000 AND
0011001100110011
result: 0001000000010000 << 2 : 0100000001000000
Now OR those:
0001000000010000
0100000001000000
result: 0101000001010000

3) Now we swap nibbles, but I think you got the point…
0101000001010000 –> 0000010100000101

4) Last we swap the bytes. (Which in this case results in no change.)

Voila, the bits are flipped…

Here is the sample code for 64 bit in C#:

[SqlFunction]
public static Int64 Reverse(Int64 inVal)
{
    ulong Result=(ulong) inVal;
    // odd and even
    Result=((Result>>1)& 0x5555555555555555)|((Result & 0x5555555555555555)<<1);
    // pairs
    Result=((Result>>2)& 0x3333333333333333)|((Result & 0x3333333333333333)<<2);
    // nibbles
    Result=((Result>>4)& 0x0F0F0F0F0F0F0F0F)|((Result & 0x0F0F0F0F0F0F0F0F)<<4);
    // bytes
    Result=((Result>>8)& 0x00FF00FF00FF00FF)|((Result & 0x00FF00FF00FF00FF)<<8);
    // words
    Result=((Result>>16)& 0x0000FFFF0000FFFF)|((Result & 0x0000FFFF0000FFFF)<<16);
    // double words
    Result=(Result>>32)|(Result<<32);

    return (Int64) Result;
}

One thing: If you (like me) want to flip only 63 bits (to keep the value positive) just add Result=Result<<1; before the even/odd flip.

Thanks very much to the anonymous poster that brought up the idea.