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!
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
Use SQL 2016 Query Store to Remove Ad-hoc Plans
Ad-hoc query plans consume memory and
can be of limited use. It is a good idea to remove ad-doc query plans if they
are not being recused. The query below demonstrates how to use Query Store
metadata to identify and remove unwanted ad-hoc query plans from the cache.
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =
(
SELECT
'EXEC sp_query_store_remove_query '
+ CAST(qsq.query_id AS NVARCHAR(6)) + ';'
AS [data()]
FROM
sys.query_store_query_text AS qsqt
JOIN
sys.query_store_query AS qsq
ON
qsq.query_text_id = qsqt.query_text_id
JOIN
sys.query_store_plan AS qsp
ON
qsp.query_id = qsq.query_id
JOIN
sys.query_store_runtime_stats AS qsrs
ON
qsrs.plan_id = qsp.plan_id
GROUP BY
qsq.query_id
HAVING
SUM(qsrs.count_executions) = 1
AND
MAX(qsrs.last_execution_time) < DATEADD (HH, -24, GETUTCDATE())
ORDER BY
qsq.query_id
FOR XML
PATH('')
) ;
EXEC(@SQL) ;
You can find out more about the Query Store in SQL Server 2016, in my new book, Expert Scripting and Automation for SQL Server DBAs, available from Apress at www.apress.com/9781484219423?gtmf=s or at Amazon at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1
Labels:
Administration,
Optimizer,
Performance,
SQL Server 2016
Navigating a SQL Server Instance with PowerShell
As well as navigating a folder
structure with commands such as get-childitem, and set-location, PowerShell can also be used to navigate the SQL Server object
hierarchy of an instance. You can connect PowerShell to the SQL Server database
engine provider by using set-location to navigate to SQLSERVER:\SQL The information returned by get-childitem is dependent on the current location of the object hierarchy. The table below shows what information is returned from each level of the hierarchy.
|
Location
|
Information Returned
|
|
SQLSERVER:\SQL
|
The name of the local machine
|
|
SQLSERVER:\SQL\ComputerName
|
The names of the database engine
instances installed on the local machine
|
|
SQLSERVER:\SQL\ComputerName\InstanceName
|
Instance level object types
|
|
Lower levels
|
Object types or objects contained
within the current location
|
Once you have navigated to the level of the hierarchy, in which you are interested, you are able to use PowerShell to perform basic
operations against objects at that level. For example, the script below will navigate to the "tables" namespace within the AdventureWorks2016
database and rename the dbo.DatabaseLog table, to dbo.DatabaseLogPS. The dir commands will display the original name and new name of the table.
sl SQLSERVER:\SQL\ESASSMgmt1\MASTERSERVER\DATABASES\ADVENTUREWORKS2016\TABLES
dir | where{$_.name -like
"*DatabaseLog*"}
rename-item -LiteralPath dbo.DatabaseLog
-NewName DatabaseLogPS
dir | where{$_.name -like
"*DatabaseLog*"}
More complex tasks that do not have a specific cmdlet, associated with them can
be performed by using the invoke-sqlcmd
cmdlet.
You can also start PowerShell
from within SSMS. This is done by selecting "Start
PowerShell" from the context menu of an object folder, within Object Explorer.
This will cause the PowerShell CLI to be invoked, with the initial location
being set to the object folder that you used to invoke the CLI.
My new book, Expert Scripting and Automation for SQL Server DBAs contains extensive details and examples of how to use PowerShell to administer SQL Server. The book is available from Apress at www.apress.com/9781484219423?gtmf=s or from Amazon, at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1
Monday, 6 June 2016
Running Administrative Queries Across Multiple Servers
As a DBA, sometimes we are asked questions, such as "What is total size of databases used by Application A?" or "Does Application A have any databases with a mismatched collation?"
If Application A has a single database server, then these questions are straight forward to answer. If Application A has databases dispersed across multiple instances, however, then the question becomes more time consuming to answer. The temptation is to connect to each instance individually, and copy/pasting the query.
It is possible to make use of Central Management Servers, to simplify answering this kind of question, however. For example, if you are supporting a large, dispersed data-tier application, you can create a server group, containing each of the instances that support the application.
For example, in the image below, you can see that I have created two folders; one for the EMEA region and another for the US.
If Application A has a single database server, then these questions are straight forward to answer. If Application A has databases dispersed across multiple instances, however, then the question becomes more time consuming to answer. The temptation is to connect to each instance individually, and copy/pasting the query.
It is possible to make use of Central Management Servers, to simplify answering this kind of question, however. For example, if you are supporting a large, dispersed data-tier application, you can create a server group, containing each of the instances that support the application.
For example, in the image below, you can see that I have created two folders; one for the EMEA region and another for the US.
We can now register our servers\instances under the relevant group. Below, you can see that I use the New Server Registration dialog box, to add servers. In this example, I will add two servers, nammed ESPROD1 and ESPROD2, to the Production | NTAM server group.
Imagine a scenario where you have a
number of SQL Server instances, where the database files are hosted on a SAN
(Storage Area Network). You come into work one morning, to find that there has
been an issue with the production SAN and you need to check all databases, on
all instances, for corruption. This can be easily achieved, using central
management servers.
Because you have a separate SAN for each
environment, in each data center, you will only want to check the servers in
the NTAM server group, as this is where the SAN issue
occurred. Therefore, select New Query, from the context menu of the NTAM server group. This will cause a new query
window to be displayed and we will use DBCC CHECKDB to check the consistency of all databases on
both instances. This is demonstrated below:
EXEC
sp_MSforeachDB 'DBCC CHECKDB (?)' ;
This command executes the undocumented
sp_MSForeachDB system stored procedure.
We pass the command that we want to run against each database, into this
procedure and use ? as a placeholder for the database name. This is automatically
populated by sp_MSForeachDB, for
each database in turn.
Below is a partial
output. You can easily see which results relate to each server, along with the
execution context. You may also notice the query status bar is pink instead of
yellow and displays the name of the server group that we can the command
against.
Because you have a separate SAN for each
environment, in each data center, you will only want to check the servers in
the NTAM server group, as this is where the SAN issue
occurred. Therefore, select New Query, from the context menu of the NTAM server group. This will cause a new query
window to be displayed and we will use DBCC CHECKDB to check the consistency of all databases on
both instances. This is demonstrated below:
EXEC sp_MSforeachDB 'DBCC CHECKDB (?)' ;
This command executes the undocumented
sp_MSForeachDB system stored procedure.
We pass the command that we want to run against each database, into this
procedure and use ? as a placeholder for the database name. This is automatically
populated by sp_MSForeachDB, for
each database in turn.
Below is a partial
output. You can easily see which results relate to each server, along with the
execution context. You may also notice the query status bar is pink instead of
yellow and displays the name of the server group that we can the command
against.
To learn much more about Central Management Servers, including further examples of useful queries to run across instances, full details of how to configure them and a demonstration of how to evaluate Policies against Central Management Servers or Server Groups, please refer to my new book, "Expert Scripting and Automation for SQL Server DBAs"
Apress link is www.apress.com/9781484219423?gtmf=s
Expert Scripting and Automation for SQL Server DBAs
My new book "Expert Scripting and Automation for SQL Server DBAs" is now available to pre-order on Amazon and Apress.com
The link for Amazon.com is www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422/ref=sr_1_1?ie=UTF8&qid=1465205714&sr=8-1&keywords=expert+scripting+and+automation
The link for Amazon.co.uk is www.amazon.co.uk/Expert-Scripting-Automation-Server-DBAs/dp/1484219422/280-0264565-3359429?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465205714&ref_=sr_1_1&sr=8-1
The link for Apress.com is www.apress.com/9781484219423?gtmf=s
It is worth noting that the expected release date on Amazon is not correct. Publication is actually expected in August.
Labels:
Administration,
Books,
Powershell,
Server Agent,
SQL Server 2016,
SSIS
Monday, 18 January 2016
SSIS Toolbox in SSDT for Visual Studio 2015
I am currently writing my latest book - Expert Scripting and Automation for DBAs, which Apress will be publishing later this year. The book is based around SQL Server 2016 and I am currently writing a chapter that is based around SSIS.
I needed to use SSDT for Visual Studio 2015, so that I could use the pre-release version of the Azure Feature Pack, for SSIS 2016. When creating a project, however, I was rather disturbed to find that I had no controls in my Toolbox!
Apparently, there are two Toolbox windows, and SSDT defaults to the wrong one. You should navigate to View | Other Windows and open the SSIS Toolbox window. Normality will at once be restored.
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
I needed to use SSDT for Visual Studio 2015, so that I could use the pre-release version of the Azure Feature Pack, for SSIS 2016. When creating a project, however, I was rather disturbed to find that I had no controls in my Toolbox!
Apparently, there are two Toolbox windows, and SSDT defaults to the wrong one. You should navigate to View | Other Windows and open the SSIS Toolbox window. Normality will at once be restored.
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Sunday, 6 December 2015
Pro SQL Server Administration
I am very excited that my first book has now been published by Apress. Pro SQL Server Administration is available in paperback and e-book formats, pretty much everywhere, including Apress, Amazon and Waterstones
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Monday, 11 July 2011
Finding Object Dependencies In SQL Server
In a sprawling database application, it can sometime be a nightmare if you need to alter the definition of a table, for fear that you will break an object that references it, that you were unaware of. (Although this will all be detailed in your documentation of course! ;-) )
But recently I found a great DMF, which is documented on MSDN, but I had never come across, that given the name of a table, will list all of the Procedures, Functions, Views and Triggers that reference it, and you can join it back to other system object, such as sys.sql_modules to pull back other useful information, such as is the object schema_bound (which will potentially stop you altering the table, and throw an error when you run the ALTER script.)
The DMF is called sys. dm_sql_referencing_entities and the example below will list the details of all objects that depend on a table called mySchema.myTable, along with if the object is schema bound, and even the object’s definition (providing it is not encrypted).
SELECT referencing_schema_name, referencing_entity_name, is_schema_bound, [definition]FROM sys.dm_sql_referencing_entities ('mySchema.myTable', 'OBJECT') rINNER JOIN sys.sql_modules mON r.referencing_id = m.object_id
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
SSIS - Execute Process Task Hangs When Run From Server Agent
I was faced with an interesting problem recently. I had a SSIS Package that was stored in the Package Store on an ETL Server, and run from a Server Agent job on a Clustered SQL Instance.
If the package was run in Debug mode, it worked perfectly. If it was run from Windows Scheduled Tasks on the Cluster, it also ran perfectly, but when run from Server Agent on the Cluster, it failed.
Or to be more accurate, it didn’t fail! In fact it didn’t do anything! It reached an execute process task that was calling a decompression utility, and sat there, not working, not failing, not doing anything!
Well, I tried the usual things, such as:
Adding SSIS Server to Local Intranet Zone
Turning on Allow Unsafe Files
Using "God Account" to run job
Using "God Account" to Run As on the execute process
Making the owner of the .exe the same account as the execution context of the process
Disable attachment manager
I knew the package and executable worked, so I was sure that it was a OS level setting, but what? Well, I struggled with this, with the aid of an infrastructure team and also MS Technical Support, but the answer finally came from the MSDN Forums, who suggested creating an Environment Variable called SEE_MASK_NOZONECHECKS and setting the Value to 1.
This worked perfectly, but it was an interesting situation, and wanted to share the resolution with you all.
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Sunday, 24 April 2011
Including Current Date In a Flat File Name - SSIS
There was a thread on MSDN Forums, which paraphrased, asked "How can I include the current Year and Month in a file name when I create a Flat File in SSIS"
Although this is a relatively straight forward task, because SSIS is so visual, it can be difficult to explain how to do things in Forum threads, so I decided to produce this short walk through video. Hope you enjoy, and find it useful...
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Although this is a relatively straight forward task, because SSIS is so visual, it can be difficult to explain how to do things in Forum threads, so I decided to produce this short walk through video. Hope you enjoy, and find it useful...
America
United Kingdom
Find a User's Effective Permissions
There was a recent post on the MSDN forums, that paraphrased, asked, how do I find out what permissions a User has to all Objects, at all levels, including those granted by their role memberships, etc.
Now, I am assuming that this is a fairly common security auditing task, so I scanned Google to dig out a script that would do it, but to my amazement, I could not find one, so I decided to write one. It is a pretty simple script, using the fn_my_permissions function and a couple of well known system tables.
In order to run it, you will need to be able to impersonate the login that you are running it for, and of course, you will also need the appropriate permissions to the system tables.
Feel free to modify the script, but please give due credit. This will work with 2005 and 2008, but I actually wrote it in Denali, so it is defiantly forward-compatible. Enjoy...
EXECUTE AS LOGIN = 'INSERT LOGIN NAME'
SELECT t.name, a.subentity_name, a.permission_name
FROM sys.objects t
CROSS APPLY fn_my_permissions(QUOTENAME(t.name), 'OBJECT') a
UNION ALL
SELECT d.name, a.subentity_name, a.permission_name
FROM sys.databases d
CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a
UNION ALL
SELECT @@SERVERNAME, a.subentity_name, a.permission_name
FROM fn_my_permissions(NULL, 'SERVER') a
ORDER BY t.name
REVERT
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Now, I am assuming that this is a fairly common security auditing task, so I scanned Google to dig out a script that would do it, but to my amazement, I could not find one, so I decided to write one. It is a pretty simple script, using the fn_my_permissions function and a couple of well known system tables.
In order to run it, you will need to be able to impersonate the login that you are running it for, and of course, you will also need the appropriate permissions to the system tables.
Feel free to modify the script, but please give due credit. This will work with 2005 and 2008, but I actually wrote it in Denali, so it is defiantly forward-compatible. Enjoy...
EXECUTE AS LOGIN = 'INSERT LOGIN NAME'
SELECT t.name, a.subentity_name, a.permission_name
FROM sys.objects t
CROSS APPLY fn_my_permissions(QUOTENAME(t.name), 'OBJECT') a
UNION ALL
SELECT d.name, a.subentity_name, a.permission_name
FROM sys.databases d
CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a
UNION ALL
SELECT @@SERVERNAME, a.subentity_name, a.permission_name
FROM fn_my_permissions(NULL, 'SERVER') a
ORDER BY t.name
REVERT
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Saturday, 23 April 2011
Head In The Clouds
In Paul Randal's recent SQLSkills Insider e-mail, he talked briefly about the SQL Azure, and how he had not seen his Clients express a large appetite for it "yet", and that inspired me to write this post, (which I have been meaning to write for a couple of weeks), to share my thoughts on Azure.
So firstly, I just want to state my position on Cloud Computing in general, and that for the most part is very positive. The opportunity to access your applications from any part of the world, with consistent performance and a massively reduced Total Cost Of Ownership has to be exciting, and there are many applications, such as Office and even Exchange, that I feel are a perfect fit for the Cloud.
SQL on the other hand is a different matter. Does it really fit in the Cloud, and if it does, what place does it hold? Will it ever be able to host Enterprise class systems, or will in fall into the category of Virtulization, which is great in the most part, but does not fit well for SQL, especially for Enterprise Solutions?
Well, because of these concerns, and because of the current immaturity of Azure, until recently, I have shyed away from playing around with this app, and kept my focus on traditional SQL, until...
I recently spent several months working on a project to plan the up-scaling of an established 2TB local system to global 32TB Enterprise class system. The obvious choice for this system was a FastTrack Data warehouse Appliance, and I had a fantastic opportunity to do some POC work at Microsoft HQ, (Which I will blog about soon). The POC was successful, and I thought it was a done-deal, but then an instruction came from a very senior level, that we should be investigating putting the application in the Cloud.
Now my gut reaction here was wow! An opportunity to build an Enterprise class database in the Cloud, but within seconds (and luckily before speaking) my professional head suppressed my geek head and I articulated the answer that it would not be practical because of the 50GB limit in Azure. This led to the suggestion that we could shard the data across multiple instances, which is true, but seemed like trying to plug a square hole with a round peg. I knew at that point that the idea was unlikely to go further, and we would probably stay with FastTrack Solution, but I felt it prudent to subscribe to Azure and have a play with it.
So when playing with Azure, the thing that surprised me was how limited the product currently is. A lot of the limitations surround Administration, and make perfect sense, for example, when your database is in the Cloud, why would you need to worry about High Availability? There is already a 4 9s SLA with MS, just by signing up. Some of the other limitations, however, are just a bit strange. For example, you can not create Heap Tables, only Tables with Clustered Indexes. Don't get me wrong, I'm all in favour of having a CI on all tables, but it just felt like a strange limitation. I also found that quite a few useful tools, such as Service Broker are not supported, and even my favourite %%PhysLoc%% query would not compile!
According to a contact of mine, there are currently only about 20 people in the Azure product team, and as I already mentioned, the offering is still fairly immature, so I fully expect that a lot of functionality will appear over the next few months and years, so from a technical perspective, I don't feel that we are quite ready to put many systems in the Cloud "yet", but we may be soon. I also suspect that the size restrictions will be relaxed as bandwidth improves.
My main question mark around Azure (thinking Medium-Term) is more political. If you are storing client data, especially if it is from a sensitive sector, such as public service data, I can not imagine those Clients, when asking the regular question "Where exactly are you storing my data", liking the answer "Oh, it's in the Cloud..."
Now I know that this stance is silly because I would be willing to bet large amounts of money that data is much more secure in Microsoft's Cloud, than it is in the vast majority of Data Centres, but what I'm not sure, is how long it will take, or how to even bring-about that cultural change that would be required, especially as the people who will need to be convinced are likely to be non-technical.
My other question mark, is a rather self-centred one, and revolves around (if I'm honest with myself), humans not liking change, because from a corporate point of view, it is actually a positive...
According to my contact, Microsoft's medium-term SQL Server policy will be to release features first to the Cloud, because they can easily be rolled back if problematic. (This is a concern in itself for the Cloud because it will always be the most up-to-date but possibly also the most unstable platform), then to release to Appliances. (FastTrack is already on v3, PDW is out and OLTP and BI appliances are on the way), and then finally to release to the traditional market.
Now this leads me to assume, that within the medium-term, large SQL projects will either be hosted in the Cloud, or on Appliances, and both of these environments are largely pre-configured and pre-optimized, so where does that leave skilled SQL professionals, who make a living out of performing those complex optimizations that will no longer be required?
Again, this is probably a silly stance, because there are bound to be a whole different set of issues that will require highly skilled professionals. For example, just because Windows 7 is a damn site easier to work with and configure than DOS 6.1, does not mean that there is no market for Desktop Support Technicians, or Network Engineers, but it does mean that more people are able to support it, which means that the market rates are dramatically lower than they were 15 years ago.
Maybe I'm an eternal pessimist, but it is certainly food for thought? If you have any thoughts on the subject, please feel free to leave comments...
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
So firstly, I just want to state my position on Cloud Computing in general, and that for the most part is very positive. The opportunity to access your applications from any part of the world, with consistent performance and a massively reduced Total Cost Of Ownership has to be exciting, and there are many applications, such as Office and even Exchange, that I feel are a perfect fit for the Cloud.
SQL on the other hand is a different matter. Does it really fit in the Cloud, and if it does, what place does it hold? Will it ever be able to host Enterprise class systems, or will in fall into the category of Virtulization, which is great in the most part, but does not fit well for SQL, especially for Enterprise Solutions?
Well, because of these concerns, and because of the current immaturity of Azure, until recently, I have shyed away from playing around with this app, and kept my focus on traditional SQL, until...
I recently spent several months working on a project to plan the up-scaling of an established 2TB local system to global 32TB Enterprise class system. The obvious choice for this system was a FastTrack Data warehouse Appliance, and I had a fantastic opportunity to do some POC work at Microsoft HQ, (Which I will blog about soon). The POC was successful, and I thought it was a done-deal, but then an instruction came from a very senior level, that we should be investigating putting the application in the Cloud.
Now my gut reaction here was wow! An opportunity to build an Enterprise class database in the Cloud, but within seconds (and luckily before speaking) my professional head suppressed my geek head and I articulated the answer that it would not be practical because of the 50GB limit in Azure. This led to the suggestion that we could shard the data across multiple instances, which is true, but seemed like trying to plug a square hole with a round peg. I knew at that point that the idea was unlikely to go further, and we would probably stay with FastTrack Solution, but I felt it prudent to subscribe to Azure and have a play with it.
So when playing with Azure, the thing that surprised me was how limited the product currently is. A lot of the limitations surround Administration, and make perfect sense, for example, when your database is in the Cloud, why would you need to worry about High Availability? There is already a 4 9s SLA with MS, just by signing up. Some of the other limitations, however, are just a bit strange. For example, you can not create Heap Tables, only Tables with Clustered Indexes. Don't get me wrong, I'm all in favour of having a CI on all tables, but it just felt like a strange limitation. I also found that quite a few useful tools, such as Service Broker are not supported, and even my favourite %%PhysLoc%% query would not compile!
According to a contact of mine, there are currently only about 20 people in the Azure product team, and as I already mentioned, the offering is still fairly immature, so I fully expect that a lot of functionality will appear over the next few months and years, so from a technical perspective, I don't feel that we are quite ready to put many systems in the Cloud "yet", but we may be soon. I also suspect that the size restrictions will be relaxed as bandwidth improves.
My main question mark around Azure (thinking Medium-Term) is more political. If you are storing client data, especially if it is from a sensitive sector, such as public service data, I can not imagine those Clients, when asking the regular question "Where exactly are you storing my data", liking the answer "Oh, it's in the Cloud..."
Now I know that this stance is silly because I would be willing to bet large amounts of money that data is much more secure in Microsoft's Cloud, than it is in the vast majority of Data Centres, but what I'm not sure, is how long it will take, or how to even bring-about that cultural change that would be required, especially as the people who will need to be convinced are likely to be non-technical.
My other question mark, is a rather self-centred one, and revolves around (if I'm honest with myself), humans not liking change, because from a corporate point of view, it is actually a positive...
According to my contact, Microsoft's medium-term SQL Server policy will be to release features first to the Cloud, because they can easily be rolled back if problematic. (This is a concern in itself for the Cloud because it will always be the most up-to-date but possibly also the most unstable platform), then to release to Appliances. (FastTrack is already on v3, PDW is out and OLTP and BI appliances are on the way), and then finally to release to the traditional market.
Now this leads me to assume, that within the medium-term, large SQL projects will either be hosted in the Cloud, or on Appliances, and both of these environments are largely pre-configured and pre-optimized, so where does that leave skilled SQL professionals, who make a living out of performing those complex optimizations that will no longer be required?
Again, this is probably a silly stance, because there are bound to be a whole different set of issues that will require highly skilled professionals. For example, just because Windows 7 is a damn site easier to work with and configure than DOS 6.1, does not mean that there is no market for Desktop Support Technicians, or Network Engineers, but it does mean that more people are able to support it, which means that the market rates are dramatically lower than they were 15 years ago.
Maybe I'm an eternal pessimist, but it is certainly food for thought? If you have any thoughts on the subject, please feel free to leave comments...
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Wednesday, 13 April 2011
SQL Server Southampton User Group 13/04/2011
I was speaking at the Southampton SQL UG tonight, and as promised, here are links to the code samples that I used, plus some more in depth articles on the subjects I spoke about. Thanks to everybody for a good night...!
Shrinking Databases
Why not to shrink a database here
The world's worst maintenance plan? here
Using %%physloc%%
How to find what page your data is stored on here
Logical Order NOT Physical Order here
Allocation Units
What Are Allocation Units here
Querying Allocation Units here
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Shrinking Databases
Why not to shrink a database here
The world's worst maintenance plan? here
Using %%physloc%%
How to find what page your data is stored on here
Logical Order NOT Physical Order here
Allocation Units
What Are Allocation Units here
Querying Allocation Units here
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Wednesday, 6 April 2011
The Curious Case Of The Phantom Last Log Backup!
I was extremely "busy" at work the other day. So much so, I was sat reading Paul Randal's Blog, when I came across an interesting article on how to tell if your database is really in Full Recovery, see here.
The general premise is that when you switch from Simple to Full recovery model, the model does not change until the log backup chain has been established, with a backup. This is called pseudo-simple mode, and can be recognised by a database that is in Full recovery model showing a NULL value for last_log_backup_lsn in sys.database_recovery_staus
I was a little board, so I though that I would have a look and make sure that our OLTP databases were really in Full Recovery Model, and they were, but much to my surprise I found that some of our VLDs that were in Simple Recovery Model were showing a non-NULL value in the last_log_backup_lsn column in sys.database_recovery_status catalogue view.
This confused me and I ask Paul Randal if there was a pseudo-full mode, but there is not, so why was there a last_log_backup_lsn? Well, intrigued, I kept an eye on the transaction logs of the databases involved, buy using SELECT * FROM sys.fn_dblog(NULL,NULL) which shows all log records in the active VLFs.
Day 1 - 7436 entries, day 2 - 762 entries.
This was good news! It meant that the log had been truncated, so the databases were behaving as if the were in Simple recovery model. This was a relief, as my initial fear was that they were behaving as though they were in Full, and of course, we were not taking Transaction Log backups, as we thought they were in Simple!
So the next test - Was this record of a last LSN only in the sys.database_recovery_status catalogue view or was it embedded more internally? To check this, I ran DBCC PAGE on the boot page of the database. The database ID happened to be 8, so the syntax was
DBCC TRACEON(3604)
DBCC PAGE(8,1,9,3)
Here, I turned on trace flag 3604, so that the DBCC output would be to the messages window, rather than the event log. The parameters for DBCC Page represent the Database ID, The file number, the page number and then the level of detail. The database boot page is always at page 9 of file 1 see here for more info on system pages.
The results showed that the boot page also contained the lsn and time stamp of the last log backup, so the mystery continued. Where did it come from? Unfortunately, it was a production system that I was looking at, so I was not able to play around with it too much, but after a few hours of trying different things, that I new had occurred on the production system, I managed to reproduce the symptom, with the following script:
CREATE DATABASE [lsn_test] ON PRIMARY
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY FULL WITH NO_WAIT
GO
The results were as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL NULL
So now...
BACKUP DATABASE [lsn_test]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER=MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT, NAME = N'lsn_test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Again, results as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL 25000000006200064
Next, was to switch the Database to Simple...
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Once more, predictable results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So now lets backup the database again...
BACKUP DATABASE [lsn_test] TO DISK = N'C:\Program Files\Microsoft SQL=erver\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT,
NAME = N'lsn_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, ST=TS = 10
GO
Once more, the correct results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So finally, what happens if we restore the database...?
RESTORE DATABASE [lsn_test] FROM DISK = N'C:\Program Files\Microsoft =QL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH FILE = 2, NOUNLOAD, STATS = 10
GO
Ah, this doesn't look so straight forward...!
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE 25000000011900001
So basically, SQL has picked up the last log backup LSN from the restore, but it is meaningless, and essentially a bug. So in the end, it turned out not to be a major issue, but it was certainly interesting, tracking it down, and a big thanks to Paul Randal for helping me get my head around it!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
The general premise is that when you switch from Simple to Full recovery model, the model does not change until the log backup chain has been established, with a backup. This is called pseudo-simple mode, and can be recognised by a database that is in Full recovery model showing a NULL value for last_log_backup_lsn in sys.database_recovery_staus
I was a little board, so I though that I would have a look and make sure that our OLTP databases were really in Full Recovery Model, and they were, but much to my surprise I found that some of our VLDs that were in Simple Recovery Model were showing a non-NULL value in the last_log_backup_lsn column in sys.database_recovery_status catalogue view.
This confused me and I ask Paul Randal if there was a pseudo-full mode, but there is not, so why was there a last_log_backup_lsn? Well, intrigued, I kept an eye on the transaction logs of the databases involved, buy using SELECT * FROM sys.fn_dblog(NULL,NULL) which shows all log records in the active VLFs.
Day 1 - 7436 entries, day 2 - 762 entries.
This was good news! It meant that the log had been truncated, so the databases were behaving as if the were in Simple recovery model. This was a relief, as my initial fear was that they were behaving as though they were in Full, and of course, we were not taking Transaction Log backups, as we thought they were in Simple!
So the next test - Was this record of a last LSN only in the sys.database_recovery_status catalogue view or was it embedded more internally? To check this, I ran DBCC PAGE on the boot page of the database. The database ID happened to be 8, so the syntax was
DBCC TRACEON(3604)
DBCC PAGE(8,1,9,3)
Here, I turned on trace flag 3604, so that the DBCC output would be to the messages window, rather than the event log. The parameters for DBCC Page represent the Database ID, The file number, the page number and then the level of detail. The database boot page is always at page 9 of file 1 see here for more info on system pages.
The results showed that the boot page also contained the lsn and time stamp of the last log backup, so the mystery continued. Where did it come from? Unfortunately, it was a production system that I was looking at, so I was not able to play around with it too much, but after a few hours of trying different things, that I new had occurred on the production system, I managed to reproduce the symptom, with the following script:
CREATE DATABASE [lsn_test] ON PRIMARY
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY FULL WITH NO_WAIT
GO
The results were as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL NULL
So now...
BACKUP DATABASE [lsn_test]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER=MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT, NAME = N'lsn_test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Again, results as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL 25000000006200064
Next, was to switch the Database to Simple...
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Once more, predictable results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So now lets backup the database again...
BACKUP DATABASE [lsn_test] TO DISK = N'C:\Program Files\Microsoft SQL=erver\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT,
NAME = N'lsn_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, ST=TS = 10
GO
Once more, the correct results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So finally, what happens if we restore the database...?
RESTORE DATABASE [lsn_test] FROM DISK = N'C:\Program Files\Microsoft =QL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH FILE = 2, NOUNLOAD, STATS = 10
GO
Ah, this doesn't look so straight forward...!
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE 25000000011900001
So basically, SQL has picked up the last log backup LSN from the restore, but it is meaningless, and essentially a bug. So in the end, it turned out not to be a major issue, but it was certainly interesting, tracking it down, and a big thanks to Paul Randal for helping me get my head around it!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Subscribe to:
Posts (Atom)








