I am very excited that my new book, Expert Scripting and Automation has hit No 1 hot new release on Amazon.com.
Even more pleasing is that today I have seen the print copy for the first time!
SQL Server - Down & Dirty
Friday 12 August 2016
My new book hit No 1 hot new release
Saturday 6 August 2016
Expert Scripting & Automation
I'm very please to announce that my new book, Expert Scripting and Automation for SQL Server DBAs has now been published.
It is already available on Amazon.com as both an e-book and a print book, and will hit the UK shelves in the coming week.
I don't believe that there is any other book quite like this one on the market, so I really hope you all enjoy the read!
It is already available on Amazon.com as both an e-book and a print book, and will hit the UK shelves in the coming week.
I don't believe that there is any other book quite like this one on the market, so I really hope you all enjoy the read!
Tuesday 12 July 2016
Using Row-Level Security with HierarchyID
When I first heard about Row-Level Security (RSL), one of the first use cases I though of, was to satisfy queries against a hierarchical table. For example, imagine that you had a table of employees details; you could use RSL to limit the rows that each employee could view, to include only those employees who report to them.
So lets have a look at how you could achieve this, by using the HumanResources.Employee table in the AdventureWorks2016CTP3 database.
In order to implement RSL, we will need two objects. The first is a Security Predicate. This consists of an inline table-valued function, which determines if a row should be accessible. The second, is a Security Policy. The Security Policy is a new artifact type in SQL Server 2016, and binds the Security Predicate to a table.
The script below creates the Security Predicate in a schema named Security.
So lets have a look at how you could achieve this, by using the HumanResources.Employee table in the AdventureWorks2016CTP3 database.
In order to implement RSL, we will need two objects. The first is a Security Predicate. This consists of an inline table-valued function, which determines if a row should be accessible. The second, is a Security Policy. The Security Policy is a new artifact type in SQL Server 2016, and binds the Security Predicate to a table.
The script below creates the Security Predicate in a schema named Security.
CREATEFUNCTION Security.fn_securitypredicate(@OrganizationNode HIERARCHYID)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
FROM HumanResources.Employee e1
WHERE @OrganizationNode.IsDescendantOf(OrganizationNode) = 1
AND LoginID = 'adventure-works\' + SUSER_SNAME() ;
GO
The function accepts a single parameter, of the type HIERARCHYID. This is a complex data type, implemented through SQLCLR, which provides a number of methods, which can be used to determine position within a hierarchy, as well as hierarchy ancestry. A full method reference for the data type can be found at https://msdn.microsoft.com/en-us/library/bb677193.aspx.
Using SCHEMABINDING on the function means that columns refferenced by the Security Predicate cannot be altered, but simplifies security administration, as the user who implicitly calls the function, will not need permissions to any tables or functions that are referenced by the Security Predicate.
The query uses the IsDecendantOf method, against the @OrganizationNode parameter (which will represent the OrganizationNode column of each row within the Employees table, to find all descendants of the row, where the LoginID column corresponds with the User ID of the user that has run the query. The concatenation of 'adventure-works\' is used to make the value returned by the SUSER_SNAME() function match the values stored in the table, where the domain of the user is also recorded. 1 is returned, for each row that matches the criteria, which tells the Security Policy that the row can be accessed.
The script below creates the Security Policy.
CREATE SECURITY POLICY Security.EmployeeSecurityPolicy
ADD FILTER PREDICATE Security.fn_securitypredicate(OrganizationNode) ON HumanResources.Employee
WITH (STATE=ON, SCHEMABINDING=ON) ;
The Security Policy is also created in the Security schema. Creating RSL objects in a separate schema is a best practice, as it simplifies security administration. The ADD FILTER PREDICATE syntax performs several functions.
Firstly, it specifies that the predicate should be used to silently filter rows, as opposed to a BLOCK predicate, which will stop DML statements beling issued against rows, and return an error message.
Secondly, it binds the Security Predicate to the HumanResources.Employee table.
Thirdly, it passes the OrganizationNode column, from the HumanResources.Employee table, to the Security Predicate function.
The WITH statement specifies that both STATE and SCHEMABINDING are ON. STATE will determine if the Policy is enabled on creation. SCHEMABINDING will determine if a Security Predicate MUST use SCHEMABINDING, or if it is optional.
I talk more about RSL, in my upcoming book, Securing SQL Server, which will be published by Apress, in early 2017.
Thursday 7 July 2016
Regular Expressions in SQL Server
In my last post ddl triggers as work-around for policy limitations, I discussed how triggers can be used to enforce database settings are not changed. This cannot be achieved through Policy Based Management, because the Database facet does not support On Change: Log or On Change: Prevent modes.
I also noted that I was not happy with the pattern matching that I originally used within the trigger, to resolve the issue. Therefore, in this post, I will talk about how we can use CLR to gain the benefits of regular expressions in SQL Server.
So, the challenge is....We want a trigger that will ensure that no statement can turn off the AUTO CREATE STATISTICS, AUTO UPDATE STATISTICS or AUTO UPDATE STATISTICS ASYNC settings of any database within the instance. The pattern matching offered by the LIKE predicate in T-SQL works well for changes made through the GUI, but if a change is scripted, then a developer may have used an extra space, a tab, or a line break, etc.
The first thing that we need to do to work around this, is create a CLR function, that will accept the SQL Statement from the EVENTDATA() function as a parameter and check the patter against the regex libraries. Below is the c# method to achieve this.
I also noted that I was not happy with the pattern matching that I originally used within the trigger, to resolve the issue. Therefore, in this post, I will talk about how we can use CLR to gain the benefits of regular expressions in SQL Server.
So, the challenge is....We want a trigger that will ensure that no statement can turn off the AUTO CREATE STATISTICS, AUTO UPDATE STATISTICS or AUTO UPDATE STATISTICS ASYNC settings of any database within the instance. The pattern matching offered by the LIKE predicate in T-SQL works well for changes made through the GUI, but if a change is scripted, then a developer may have used an extra space, a tab, or a line break, etc.
The first thing that we need to do to work around this, is create a CLR function, that will accept the SQL Statement from the EVENTDATA() function as a parameter and check the patter against the regex libraries. Below is the c# method to achieve this.
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static int
CheckMatch(SqlString statement)
{
Regex rgxUpdate = new Regex("AUTO_UPDATE_STATISTICS[_ASYNC]{0,}[\r\n\t{0,}[
\n\t\t]{0,}OFF", RegexOptions.IgnoreCase
| RegexOptions.Singleline);
Int16 retcounter = 0;
string stringstatement = Convert.ToString(statement);
Match matchUpdate = rgxUpdate.Match(stringstatement);
if (matchUpdate.Success)
{
retcounter++;
}
Regex rgxCreate = new Regex("AUTO_CREATE_STATS[\r\n\t{0,}[
\n\t\t]{0,}OFF", RegexOptions.IgnoreCase
| RegexOptions.Singleline);
Match matchCreate = rgxCreate.Match(stringstatement);
if (matchCreate.Success)
{
retcounter++;
}
if (retcounter > 0)
{ return 1; }
else
{ return 0; }
}
}
There are a few things to pay particular attention to in this code. Firstly, notice that we are importing the following .NET base libraries:
System.Data.SqlTypes
Microsoft.SqlServer.Server
System.Text.RegularExpressions
The first of these libraries, is used because we are working with the sqlstring data type, The second library is required because we are creating a function that will be used by SQL Server and the third library is required to use the regex classes.
The next thing to note in this code is the regex options. We are using regex options to treat the SQL Statement as a single line and to make the pattern match case insensitive.
Once we have built the project, we will need to create an assembly within SQL Server. This can be created using the CREATE ASSEMBLY DDL statement, as demonstrated below.
CREATE ASSEMBLY CheckStats
FROM 'c:\sql_assemblies\checkstats.dll'
WITH PERMISSION_SET = SAFE;
The script assumes that we have built the project as CheckStats.dll to the c:\sqlassemblies folder.
Now that the assembly has been built, we can create the CLR function, which will call the c# method within the assembly. The script below demonstrates how to create the function.
CREATE FUNCTION dbo.CheckMatch
(
@Statement NVARCHAR(MAX)
)
RETURNS INT
AS EXTERNAL NAME CheckStats.UserDefinedFunction.CheckMatch;
Notice that we reference the method, using the 3-part name Assembly.Class.Method
Finally, we can create the server scoped DDL trigger that will respond to ALTER DATABASE statements and call the CLR function. This is demonstrated below.
CREATE TRIGGER StatisticsManagement
ON ALL SERVER
AFTER ALTER_DATABASE
AS
BEGIN
IF (SELECT dbo.CheckMatch(UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))) = 1
BEGIN
PRINT 'Auto Stats Settings MUST Be Configured As ON';
ROLLBACK;
END
END
You can read more about DDL Triggers in my book Pro SQL Server Administration, available from apress.com, Amazon and most book shops.
Tuesday 5 July 2016
DDL Triggers as a Work Around for Policy Based Management Limitations
A college recently asked for my help in solving a problem with SQL Server Policy Based Management. He had a requirement to enforce a policy that prevent the auto update and auto create statistics settings being changed on production databases during code deployments. The problem is, that these settings are in the Database facet and this facet does not support On Chage:Prevent or On Change Log.
You can determine which actions are supported by which facets, by using the below query:
The trigger pulls the statement executed from the EVENTDATA() function and then uses the LIKE predicate to pattern match the strings that would turn off the auto update and auto create statistics options. The event data is returned as an XML document and the schema can be found at http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd. Therefore, we need to use the value() Xquery method, to extract the data.
After writting this trigger, it played on my mind. Whilst this trigger will always work for changes made through the GUI, there is a chance that it will not work, if the change is made through a script. This is because of the way the LIKE predicate pattern matches the statement. For example, what is a script included an additional space before OFF?
In my next post, I will talk about how I resolved the this, by using a CLR function.
I talk more about DDL triggers and Policy Based Management, in my book, Pro SQL Server Administration, available from apress.com, Amazon and most book shops.
You can determine which actions are supported by which facets, by using the below query:
SELECTInstead, I suggested that he use a DDL trigger to ensure that these settings are not changed, and helped him write the below trigger definition.
name ,
'Yes' AS on_demand,
CASE
WHEN (CONVERT(BIT, execution_mode & 4)) = 1
THEN 'Yes'
ELSE 'No'
END AS on_schedule,
CASE
WHEN (CONVERT(BIT, execution_mode & 2)) = 1
THEN 'Yes'
ELSE 'No'
END AS on_change_log,
CASE
WHEN (CONVERT(BIT, execution_mode & 1)) = 1
THEN 'Yes'
ELSE 'No'
END AS on_change_prevent
FROM msdb.dbo.syspolicy_management_facets ;
This trigger has been created on the instance, which means that it will reside in the Master database and is configured to fire when an ALTER DATABASE statement is executed. There is no need for it to fire when a CREATE DATABASE statement is executed, because even when using the GUI to create a database, the auto update and auto create statistics settings are configured with ALTER DATABASE statements, after the initial database creation.CREATE TRIGGER DBA_StatisticsManagementON ALL SERVERAFTER ALTER_DATABASEASBEGINIF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))) LIKE '%AUTO_UPDATE_STATISTICS_ASYNC OFF%'BEGINPRINT 'Auto Update Stats Async MUST be configured as ON'ROLLBACK ;ENDIF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))) LIKE '%AUTO_CREATE_STATISTICS OFF%'BEGINPRINT 'Auto Create Stats MUST be configured as ON'ROLLBACK ;ENDIF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))) LIKE '%AUTO_UPDATE_STATISTICS OFF%'BEGINPRINT 'Auto Update Stats MUST be configured as ON'ROLLBACK ;ENDEND
The trigger pulls the statement executed from the EVENTDATA() function and then uses the LIKE predicate to pattern match the strings that would turn off the auto update and auto create statistics options. The event data is returned as an XML document and the schema can be found at http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd. Therefore, we need to use the value() Xquery method, to extract the data.
After writting this trigger, it played on my mind. Whilst this trigger will always work for changes made through the GUI, there is a chance that it will not work, if the change is made through a script. This is because of the way the LIKE predicate pattern matches the statement. For example, what is a script included an additional space before OFF?
In my next post, I will talk about how I resolved the this, by using a CLR function.
I talk more about DDL triggers and Policy Based Management, in my book, Pro SQL Server Administration, available from apress.com, Amazon and most book shops.
Labels:
Administration,
automation,
Policy Based Management
Tuesday 7 June 2016
How to Calculate Binary Bit Masks
Ok, so I know this post is a bit geeky, but there is nothing wrong with a little geeky fun from time to time.
SQL Server uses bit maps for various thinks, such as CPU affinity and Server Agent Operator's pager availability, but how do you work with bit maps? Well, it is a case of calculating the integer expresentation of the bit map value. This is made more complex because the INT data type is a 32-bit signed integer, meaning that some of the representations will be negative numbers.
Using CPU affinity as an example, the table below shows the processor number, bit mask and signed integer representation for processors 0 - 31.
Processor Number Bit Mask Signed Integer Representation
0 0000 0000 0000 0000 0000 0000 0000 0001 1
1 0000 0000 0000 0000 0000 0000 0000 0010 2
2 0000 0000 0000 0000 0000 0000 0000 0100 4
3 0000 0000 0000 0000 0000 0000 0000 1000 8
4 0000 0000 0000 0000 0000 0000 0001 0000 16
5 0000 0000 0000 0000 0000 0000 0010 0000 32
6 0000 0000 0000 0000 0000 0000 0100 0000 64
7 0000 0000 0000 0000 0000 0000 1000 0000 128
8 0000 0000 0000 0000 0000 0001 0000 0000 256
9 0000 0000 0000 0000 0000 0010 0000 0000 512
10 0000 0000 0000 0000 0000 0100 0000 0000 1024
11 0000 0000 0000 0000 0000 1000 0000 0000 2028
12 0000 0000 0000 0000 0001 0000 0000 0000 4096
13 0000 0000 0000 0000 0010 0000 0000 0000 8192
14 0000 0000 0000 0000 0100 0000 0000 0000 16384
15 0000 0000 0000 0000 1000 0000 0000 0000 32768
16 0000 0000 0000 0001 0000 0000 0000 0000 65536
17 0000 0000 0000 0010 0000 0000 0000 0000 131072
18 0000 0000 0000 0100 0000 0000 0000 0000 262144
19 0000 0000 0000 1000 0000 0000 0000 0000 524288
20 0000 0000 0001 0000 0000 0000 0000 0000 1048576
21 0000 0000 0010 0000 0000 0000 0000 0000 2097152
22 0000 0000 0100 0000 0000 0000 0000 0000 4194304
23 0000 0000 1000 0000 0000 0000 0000 0000 8388608
24 0000 0001 0000 0000 0000 0000 0000 0000 16777216
25 0000 0010 0000 0000 0000 0000 0000 0000 33554432
26 0000 0100 0000 0000 0000 0000 0000 0000 67108864
27 0000 1000 0000 0000 0000 0000 0000 0000 134217728
28 0001 0000 0000 0000 0000 0000 0000 0000 268435456
29 0010 0000 0000 0000 0000 0000 0000 0000 536870912
30 0100 0000 0000 0000 0000 0000 0000 0000 1073741824
31 1000 0000 0000 0000 0000 0000 0000 0000 -2147483648
On a 32-core server, there are 2.631308369336935e+35 possible combinations for processor affinity, but a few examples are included below.
Aligned Processors Bit Mask Signed Integer Representation
0 and 1 0000 0000 0000 0000 0000 0000 0000 0011 3
0, 1, 2, and 3 0000 0000 0000 0000 0000 0000 0000 1111 15
8 and 9 0000 0000 0000 0000 0000 0011 0000 0000 768
8, 9, 10, and 11 0000 0000 0000 0000 0000 1111 0000 0000 3840
30 and 31 1100 0000 0000 0000 0000 0000 0000 0000 -1073741824
28, 29, 30, and 31 1111 0000 0000 0000 0000 0000 0000 0000 -268435456
Because an integer has a a maximum range of 2^32 then the Affinity Mask can only accommodate values for up to 32 CPUs. If your server has between 33 and 64 processors, then SQL Server relies on a 2nd value, called the 64 Bit Affinity Mask, to store the details of the next 32 CPUs.
There are a number of calculators available online, for calculating the signed integer representation of a bit mask. I tend to use www.binaryconvert.com/convert_signed_int.html
SQL Enterprise Edition vs SQL Enterprise Core Based Licensing Edition
There is a common point of confusion, when DBAs decide what version of SQL Server to install. Until SQL Server 2012, if you needed Enterprise Edition, then things were straight forward. When SQL Server 2012 was released, however, things changed, with the release of core-based licensing.
Subsequently, SQL Server Enterprise Edition should not be installed in most cases. It is designed as a "tide-me-over" edition, to see companies through, as they move from CAL based, to core based licenses.
What a lot of people do not realize, is there is actually a technical difference between the two editions. The two products have different SKUs and SQL Server Enterprise Edition will only support a maximum of 20 cores.
If you have a server large enough to test this out, you can see for yourself, by running the following query:
SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
If you are using Enterprise Edition, then the maximum value returned will be either 20, or 40 if the server has hyper-threading turned on.
Check out my books on Apress.com:
Pro SQL Server Administration
SQL Server AlwaysOn Revealed
Expert Scripting and Automation for SQL Server DBAs
Subscribe to:
Posts (Atom)