Unable to re-create Availability group with same name

While working in my test environment, we have delete existing Availability group and try creating a new one with same name. Unfortunately It was failing with error  “Failed to create availability group ‘SQL AAG’, because a Windows Server Failover Cluster (WSFC) group with the specified name already exists.”

erroraag

We successfully deleted existing configuration as per Microsoft & Fail-over cluster log does not give any clue about existing AAG group.

Here is the solution.

  1. Drop the availability group if not in previous attempt, refer
  2. Take backup of registry then delete key “HKEY_LOCAL_MACHINE\Cluster \HadrAgNameToldMap” from all nodes participating in cluster.
  3. From SQL configuration manager, uncheck ‘Enable Alwayson Availability Groups’, apply OK. Restart SQL service.config
  4. Check the box ‘Enable Alwayson Availability Groups’ again, Apply-ok, restart SQL service.

Now you should be able to create availability group with same existing name.

successaag

Advertisements

#aag, #alwayson

How to safely decommission spare Exchange 2016 Server from organization.

There are many occasion when an exchange admin needs to decommission an un-used Exchange server from organization that might be installed by mistake and doesn’t full fill business requirement. There are lots of blogs or link present on internet that may guide you to do via ADSI Edit but the preferred way is to do gracefully using front end (exchange PowerShell OR GUI). Following PS script is verified in Exchange 2016 test environment for the same purpose.

$Exch2k16="exmbx2" #Exchange Server name that needs to be removed. 
$DestDb="Mailbox Database 1529611009" #Destination mailbox Database where the mailboxes of server will be moved. 
Get-MoveRequest|Remove-MoveRequest -Confirm:$False #Delete move mailbox request if exists
Get-Mailbox -Server $Exch2k16 |New-MoveRequest -TargetDatabase $DestDb #Move mailboxes to other database. 
Get-Mailbox -AuditLog -Server $Exch2k16 |New-MoveRequest -TargetDatabase $DestDb  #Move Auditing mailbox
Get-Mailbox -Monitoring -Server $Exch2k16 |New-MoveRequest -TargetDatabase $DestDb #Move health/monitoring db
Get-Mailbox -Archive -Server $Exch2k16 |New-MoveRequest -TargetDatabase $DestDb #Move all archives
Get-Mailbox -PublicFolder -Server $Exch2k16 |New-MoveRequest -TargetDatabase $DestDb #Move Publicfolder mailboxes
Get-MailboxDatabase -Server $Exch2k16 |Remove-MailboxDatabase -Confirm:$False #Remove MailboxDB
Get-ReceiveConnector -Server $Exch2k16|Remove-ReceiveConnector -Confirm:$False #Remove Receive connector.  

Now uninstall exchange binaries using following command.

setup /m:uninstall

ExchangeUn.jpg

 

#exchange, #movemailbox

Row Level Security (RLS)

SQL 2016 comes up with a new developer feature called Row Level Security (RLS) that use predicate based security feature for controlling row access on the attribute of user login. Such as an example below:

A DB contain details of all books, author and associated cost. When an author supply his credential he should be able to access only books he wrote with total cost. Such as following query and output.

RLS

RLS works on following two key concepts for implementing security.

Predicate function – An inline table-valued function that implements access control logic (for example, returning a row depending on the principal_name, role, or other attributes of the calling user).

Security predicate – Glues a predicate function to a table (for example, applying a function that checks for rep name or the rep manager role to the Accounts table).

Let’s use following code and screenshot for better understanding:-

--Create sample database. 
USE [master]
CREATE DATABASE RLSDEMO
GO

--Create 'EmployeeData' table. 
USE [RLSDEMO]
CREATE TABLE EmployeeData 
(
       EMPID INT PRIMARY KEY, 
       Firstname VARCHAR(50),
       LastName VARCHAR(50)
)
GO

----Create another table 'books'. 
USE [RLSDEMO]
CREATE TABLE Books
(
       BOOKID INT PRIMARY KEY,
       BookName VARCHAR(100),
       Cost INT,
       AuthorID INT FOREIGN KEY REFERENCES EmployeeData (EMPID)
)      
GO

----Insert records in employee table. 
USE [RLSDEMO]
INSERT INTO EmployeeData VALUES
(1, 'Vikram', 'Jadhav'), 
(2, 'Pradeep', 'Papnai'),
(3, 'Darshan', 'Bandodkar'), 
(4, 'Ajay', 'Jain')
GO 

----Insert records in books table. 
USE [RLSDEMO]
INSERT INTO Books VALUES
(1, 'Troubleshooting Vault Cache', 5000, 1),
(2, 'Troubleshooting Exchange', 12000, 1),
(3, 'PST migration black book', 5000, 1),
(4, 'EV & MAC client', 5000, 2),
(5, 'EV DB on SQL 2K16', 11000, 2), 
(6, 'Storage overview', 4000, 2),
(7, 'FSA crash overview', 6000, 3),
(8, 'FSAUtility basis', 3000, 3),
(9, 'SSMW-Server migration', 1500, 3),
(10, 'Dtrace fundamentals', 2000, 1), 
(11, 'Exchange mail flow', 4000, 1),
(12,'Networking tools NetMON-wireshark',3400, 3)
GO

----Create view for select query simplicity. 
CREATE VIEW BooksCost
AS SELECT 
       EmployeeData.Firstname,
       EmployeeData.LastName,
       Books.BOOKID,
       Books.BookName, 
       Books.Cost
FROM EmployeeData
INNER JOIN Books ON Books.AuthorID=EmployeeData.EMPID
GO

--Create a ‘Manager’ and 3 other users those are also employees and the author of various books. We will assume first name as loginid. 
CREATE USER Manager WITHOUT LOGIN
CREATE USER vikram WITHOUT LOGIN
CREATE USER pradeep WITHOUT LOGIN
CREATE USER darshan WITHOUT LOGIN
GO

---- Grant full access to the manager user, and read-only access to the remaining users(authors). 
GRANT SELECT, INSERT, UPDATE, DELETE ON BooksCost TO Manager
GRANT SELECT ON BooksCost TO vikram
GRANT SELECT ON BooksCost TO pradeep
GRANT SELECT ON BooksCost TO darshan
GO

--- Create a new schema for the predicate function, it NOT mandate but recommended. 
CREATE SCHEMA SEC
GO

--- Create the predicate function
CREATE FUNCTION sec.fn_Author_Predicate(@Username AS varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
-- Return 1 if the connection username matches the @Username parameter
-- or if the connection username is Manager
SELECT
1
AS
Author_predicate_result
WHERE
@Username=USER_NAME()OR USER_NAME()='Manager'
GO

-- Create and enable a security policy adding the function as a filter predicate
USE [RLSDEMO]
CREATE SECURITY POLICY sec.EmployeePolicyFilter
       ADD FILTER PREDICATE sec.fn_Author_Predicate(Firstname) 
       ON dbo.EmployeeData
       WITH (STATE = ON)
GO

Test 1:-

--- As vikram, we get just the rows he own with total books and cost
EXECUTE AS USER = 'vikram'
SELECT Firstname, LastName, BOOKID, BookName, Cost FROM BooksCost 
UNION ALL
SELECT NULL, 'BOOK COUNT', count(bookid), 'TOTAL COST(INR)', sum(COST) FROM BooksCost
REVERT
GO

vikram.jpg

Test 2:-

--- As pradeep, we get just the rows he own with total books and cost
EXECUTE AS USER = 'pradeep'
SELECT Firstname, LastName, BOOKID, BookName, Cost FROM BooksCost 
UNION ALL
SELECT NULL, 'BOOK COUNT', count(bookid), 'TOTAL COST (INR)', sum(COST) FROM BooksCost
REVERT
GO

pradeep.jpg

Test 3:-

--- As darshan, we get just the rows he own with total books and cost
EXECUTE AS USER = 'darshan'
SELECT Firstname, LastName, BOOKID, BookName, Cost FROM BooksCost 
UNION ALL
SELECT NULL, 'BOOKCOUNT', count(bookid), 'TOTAL COST(INR)', sum(COST) FROM BooksCost
REVERT
GO

darshan.jpg

Test 4:-

--- As Manager, we get all rows
EXECUTE AS USER = 'Manager'
SELECT Firstname, LastName, BOOKID, BookName, Cost FROM BooksCost 
UNION ALL
SELECT NULL, 'BOOKCOUNT', count(bookid), 'TOTAL COST(INR)', sum(COST) FROM BooksCost
REVERT
GO

Manager.jpg

Disable / Enable RLS

--- We can toggle the security policy on and off
ALTER SECURITY POLICY sec.EmployeePolicyFilter WITH (STATE = OFF)

---Enable again
ALTER SECURITY POLICY sec.EmployeePolicyFilter WITH (STATE = ON)

More details from MSDN

#rls, #sql, #sql2016

SQL 2016 Live Query Statistics

In my last post we learned how ‘Query Store’ works, now this is the time to show one other amazing feature called ‘Live Query Statistic’ that is also released with SQL 2016. Just to clarify this feature is included with SQL Management studio 2016 that means it will work if it connects to SQL version 2014.

Let’s now see how this feature can be beneficial for DBA/Consultant OR support engineers.

The steps of preparing LAB environment is same as steps mentioned in ‘Query store’ Blog.

/* Please note, the database and tables are design only for training purpose so it doesn't necessarily have the best design.*/
-- Create Database & two tables 'Region' and 'EmployeeData'
USE [master]
CREATE DATABASE QueryStoreDemo
GO

USE [QueryStoreDemo]
CREATE TABLE Region
       (
       ID INT IDENTITY(1,1),
       RNAME VARCHAR (100),
       HQ VARCHAR(100)
       )
GO

USE [QueryStoreDemo]
CREATE TABLE EmployeeCity
       (
       ID INT IDENTITY (1, 1),
       NAME VARCHAR (100),
       City VARCHAR(100), 
       RegionID INT
       )
GO

-- Insert values, the second insertion might take time (3-5 minutes) as we are inserting 6 rows 50K times. 
USE [QueryStoreDemo]
INSERT INTO Region  VALUES
       ('PUNE', 'SOUTH'),
       ('GOA', 'EAST'),
       ('NASHIK', 'WEST'),
       ('NASIK','NORTH'),
       ('SOLAPUR','Center')
GO

USE [QueryStoreDemo]
INSERT INTO EmployeeCity
VALUES
       ('Pradeep', 'Pune', 1),  
       ('Vikram', 'Mumbai', 2),
       ('Darshan', 'Goa', 3),
       ('AJAY', 'Nashik', 4),
       ('Yogesh', 'Solapur', 5)
GO 50000

Continue reading

#live-query-statistics, #query-plan, #sql, #sql2016