SQL Server 2012 SP2 CU4 breaks Data Collection

Last week, one of my clients upgraded their SQL Servers to SQL Server 2012 SP2 CU4.
After the upgrade, the internal DBA notified me that the data collection was not showing any Query Statistics anymore in the Management Data Warehouse report.
I verified the logs of the Query Statistics and saw that the Query Statistics Collection ran for a few minutes and then failed with the following errors:

The external columns for ODS – Get snapshot of dm_exec_requests are out of synchronization with the data source columns. The external column “command” needs to be updated.

Truncation may occur due to retrieving data from database column “command” with a length of 32 to data flow column “command” with a length of 16.

The collection never occurred correctly, so the upload to the MDW never happened. That’s the reason why the report was empty.
But why does the collection package starts to fail? Was the DMV dm_exec_requests updated so that the command column size was 32 characters instead of 16? I double checked with BOL and with an older version of SQL Server but the command field always had a length of 32.

Gianluca Sartori (blog | twitter) suggested on Twitter #sqlhelp to modify the SSIS packages to get this fixed. I downloaded the package from the server, modified the command field length to 32 characters and uploaded it again to the server. The QueryActivityCollect package was now running OK but the QuaryActivityUpload package now started to fail with same error on 3 different components (can’t remember which ones). I modified them too and finally I got everything working. … Well… not… Still no data on my report. I’ve noticed that the upload SSIS package was not loading the queries in the [snapshot].[query_stats] table anymore…

I was getting frustrated and stopped with modifying the packages. There must be a better way to fix this! So I came up with the following workaround.

  • I uninstalled the CU4. Yes, you can install SP’s and CU’s. Just go to Programs and Features in the control panel, select the option “View installed updates”, right click on the CU and select uninstall

     

  • Once the CU was uninstalled, my data collection started to work again. I download again the packages from the server and compared them with the CU4 package version.

    And guess what, the packages are changed!

    For example, when I compared the component ODS – Get snapshot of dm_exec_requests

    I get the following in the CU4 version

    While in the CU3 version you get the following:

    So there has definitely be “some” modifications. I’ve also verified the upload package and it’s the same issue.

     

  • I reinstalled CU4 again
  • Stopped the Query Statistics collection

  • I upload the CU3 SSIS package to the server with dtutil, removed all the cache files from the cache folder and restarted the Query Statistics Collection

Finally, my data collection started to work again…
I’ve opened a connect for this. Please vote and let’s hope Microsoft will fix this in the next CU J

Happy collecting!

Pieter

Create an empty AlwaysOn Availability Group

Last week I had to install a 6 node failover cluster at a customer. 4 nodes were used for high availability solution in site A and 2 nodes were in site B for disaster recovery. Each site was hosting 9 SQL Server instances.

On my last day, I had to configure the AlwaysOn Availability Groups (AVG) as disaster recovery solution.  I have chosen the following settings for my AVG’s

  • Asynchronous commit mode
  • Backups on primary replica
  • Readable secondary

There was just one small “problem”. There were no databases to include in the AVG ‘s because this was a complete new platform. I couldn’t use the AlwaysOn Availability Group Wizard because that expects a database. But I wanted to prepare the AVG’s in advance so that the customer only needed to add the databases to the groups at a later stage.
Since I had to install 9 AVG’s I created a script that I could reuse every time I want to create an empty availability group.
The script will create an Availability Group on the primary replica, will add a group listener and will join one secondary replica all in one execution.

You can find the script below but I must mention a few remarks and restrictions

  • You have to execute the script in the SQLCMD mode in the SQL Server Management Studio. To do this go in the main menu to Query –> SQLCMD Mode
  • Only one secondary replica can be used
  • The settings for the replica’s are the one mentioned above
  • Only one IP address can be linked to the group listener
  • Named instances are supported

Before you can execute the script you only need to specify a few parameters:

Parameter Description
PrimaryReplica The primary replica. Default and Named instances are supported eg. ServerInst01
SecondaryReplica The secondary replica. Default and Named instances are supported eg. ServerInst01
EndPointPort Specify the port for the endpoints
AvailabilityGroup The name of the availability Group
Listener The name of the group listener
IPListener The IP address of the listener
ListenerPort Specify the listener port
ListenerSubnet Specify the subnet of the listerner IP Address

You can find my script below. If you should have questions, remarks or suggestions, please do leave a comment so that I can improve it.

Have fun!

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:setvar PrimaryReplica Node1 --Specify the primary replica
:setvar SecondaryReplica Node2 --Specify the secondary replica
:setvar EndPointPort 5022 --Specify the port for the endpoints
:setvar AvailabilityGroup AVG_Group --Specify the name of the availability group
:setvar Listener AGL --Specify the name of the group listener
:setvar IPListener 192.168.0.20 --Specify the IP Address of the group listener
:setvar ListenerPort 1433 -- Specify the listener port
:setvar ListenerSubnet 255.255.240.0 --Specify the subnet of the listeren IP Address

:Connect $(PrimaryReplica)

USE [master]
GO
declare @AVGLogin as sysname
declare @CMD nvarchar(max)

select @AVGLogin=service_account from sys.dm_server_services
where servicename like 'SQL Server (%'

if not exists(select name from sys.syslogins where name=@AVGLogin)
begin
 set @CMD=N'CREATE LOGIN [' + @AVGLogin + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
 print 'Create service account as server login...'
 exec sp_executesql @CMD
end

if not exists (select name from sys.endpoints where name='Hadr_endpoint')
begin
print 'Create HA/DR endpoint...'
CREATE ENDPOINT [Hadr_endpoint] 
 AS TCP (LISTENER_PORT = $(EndPointPort))
 FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
end

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
 ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


set @CMD=N'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [' + @AVGLogin + ']'
print 'GRANT CONNECT ON ENDPOINT...'
exec sp_executesql @CMD

GO

:Connect $(SecondaryReplica)

USE [master]
GO

declare @AVGLogin as sysname
declare @CMD nvarchar(max)

select @AVGLogin=service_account from sys.dm_server_services
where servicename like 'SQL Server (%'

if not exists(select name from syslogins where name=@AVGLogin)
begin
 set @CMD=N'CREATE LOGIN [' + @AVGLogin + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
 print 'Create service account as server login...'
 exec sp_executesql @CMD
end

if not exists (select name from sys.endpoints where name='Hadr_endpoint')
begin
print 'Create HA/DR endpoint...'
CREATE ENDPOINT [Hadr_endpoint] 
 AS TCP (LISTENER_PORT = $(EndPointPort))
 FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
end

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
 ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

set @CMD=N'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [' + @AVGLogin + ']'
print 'GRANT CONNECT ON ENDPOINT...'
exec sp_executesql @CMD

GO

:Connect $(PrimaryReplica)

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect $(SecondaryReplica)

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect $(PrimaryReplica)

USE [master]
GO

declare @SystemAddressPrimary nvarchar(256)
declare @SystemAddressSecondary nvarchar(256)

if charindex('','$(PrimaryReplica)')=0
 set @SystemAddressPrimary='$(PrimaryReplica)'
else
 select @SystemAddressPrimary=substring('$(PrimaryReplica)',0,charindex('','$(PrimaryReplica)'))

if charindex('','$(SecondaryReplica)')=0
 set @SystemAddressSecondary='$(SecondaryReplica)'
else
 select @SystemAddressSecondary=substring('$(SecondaryReplica)',0,charindex('','$(SecondaryReplica)'))

declare @CMD nvarchar(max)
set @CMD='
CREATE AVAILABILITY GROUP [$(AvailabilityGroup)]
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR 
REPLICA ON N''$(PrimaryReplica)'' WITH (ENDPOINT_URL = N''TCP://' + @SystemAddressPrimary + ':$(EndPointPort)'', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)),
 N''$(SecondaryReplica)'' WITH (ENDPOINT_URL = N''TCP://' + @SystemAddressSecondary + ':$(EndPointPort)'', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));'

print 'Create the availability group...'
exec sp_executesql @CMD

print 'Create the availability group listener...'
ALTER AVAILABILITY GROUP [$(AvailabilityGroup)]
ADD LISTENER N'$(Listener)' (
WITH IP
((N'$(IPListener)', N'$(ListenerSubnet)')
)
, PORT=$(ListenerPort));

GO

:Connect $(SecondaryReplica)

ALTER AVAILABILITY GROUP [$(AvailabilityGroup)] JOIN;

GO

Careful with autogrowth!

A few months ago I was invited by a customer to do an audit of their SQL Servers.
One of the issues that I found was the “implementation” of the autogrowth settings, like growth with 1MB or growth in %.
This week, they invited me back to perform the corrective actions on the issues that I have found.
I was manually modifying the autogrowth setting of one of the databases and I made a mistake… Instead of changing the value of MB I accidently changed the % value into 1024. To my surprise the SSMS accepted this value.
I expected that the percentage value couldn’t go above 100%. Wrong guess … J.
My mistake triggered me to perform some testing on the autogrowth setting.

Let’s see if it’s actually grows with 1024%.
I just created a simple DB with an initial data size of 10 MB and change the autogrowth to 1024%. (Don’t mind the autogrowth setting of the log file.)
I’ve done my tests on a SQL 2012 and on a SQL 2014 instance.

After loading some data into my database, my file actually has grown with 1024%!

(10 MB x 1024%) + 10 MB (initial size) = 112,4 MB !

Let’s see if we can push it a little bit further. What can be the maximum value of the percent growth?
Well, it turns out if you enter a large number like 9999999999 in the GUI, it will change it back to the following value 2147483647, which is the maximum value of an integer.
I’ve tried the same with T-SQL statement

ALTER DATABASE [testgrowth] MODIFY FILE ( NAME = N'testgrowth', FILEGROWTH = 9999999999%)

and I’ve got the error
Incorrect syntax near ‘9999999999’.

When I changed the 9999999999 into 2147483647%, my query ended successfully! As soon as you enter one digit higher than 2147483647, independent if you’re using KB, MB, GB, %,… after the number, the SSMS sees it as an “Incorrect syntax”.

So now we know what the limits are for % but are these the same when you choose, autogrowth in MB?
Let’s give it a try. I entered 9999999999 into the MB autogrowth field and the SSMS automatically changed it into 1048576 MB. It looks like the autogrowth in MB is limited to 1 TB and not 2147483647 like the percentage growth. It’s a completely different value.
If I try a T-SQL statement

ALTER DATABASE [testgrowth] MODIFY FILE (NAME = N'testgrowth', FILEGROWTH = 2147483647MB)

(a larger number will give me the incorrect syntax) I got the following error message:

Msg 1842, Level 16, State 1, Line 1

The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage.

Let’s see what happens when I do a T-SQL statement that is larger than 1048576MB (GUI limit) and smaller than 2147483647 (T-SQL limit).

ALTER DATABASE [testgrowth] MODIFY FILE (NAME = N'testgrowth', FILEGROWTH = 2097152 MB)

completes successfully.
Hey! Wasn’t that limited to 1TB by the GUI?

Let’s open the GUI again to see how SSMS deals with it. Well… not quite good… J

Conclusion here is that the GUI and the T-SQL statement are using different limitations, which should be corrected.

I’ve opened a connect on https://connect.microsoft.com/SQLServer/feedback/details/812486/gui-and-the-t-sql-statement-are-using-different-limitations-for-autogrowth-in-mb

Be careful when you change the autogrowth settings and when you make mistakes. The results can be quite strange.

Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core’

I was migrating SSIS packages from a SQL Server 2008 R2 server to a new SQL Server 2012.
When I loaded a package in the SQL Server Data Tools, I got the following error:

I was only getting this error when I was trying to upgrade a package with a Script Component in it.
When I tried to edit the script I also got the following error message:

Strangely enough, when I opened the same package on my computer, it worked fine.

After searching a while I found a solution to fix this issue.
The key is in the message “Cannot show Visual Studio 2010 Tools for Applications editor

When I looked at the “Program and Features” of my server I saw that the following components were installed

When I compared it to my computer “Program and Features”, I saw that an extra component was installed Microsoft Visual Studio Tools for Applications x86 Runtime 3.0

After my investigation, I found out that the problem is caused by the order in which you install SQL Server Integration Services 2012 and SQL Server Data tools.
So I played around and tested 3 different scenario”s:

  1. First Integration Services installation and secondly run the setup again to install SQL Server Data Tools

Actually, this was my original situation. I only installed Integration Services, but the customer also wanted the SQL Data Tools installed, so I added it afterwards.
When Integration Services was installed I saw that only one component was installed

Now, let”s install the SQL Server Data Tools. According to the installation wizard the Microsoft Visual Studio Tools for Applications 3.0 still needs to be installed (see picture below)

However, after the installation is finished, the Microsoft Visual Studio Tools for Applications Design-Time 3.0 have been added but the Microsoft Visual Studio Tools for Applications x86 Runtime 3.0 is still missing…

  1. First SQL Server Data Tools installation and secondly run the setup again to install Integration Services

I started again from a clean situation and installed first the SQL Server Data Tools. The Microsoft Visual Studio Tools for Applications Design-Time 3.0 and Microsoft Visual Studio Tools for Applications x86 Runtime 3.0 components have been installed.
As you might notice,the x64 Runtime is now missing…

No problem,let”s install Integration Services now. I expect that the Microsoft Visual Studio Tools for Applications x64 Runtime 3.0 will be installed since it was installed in my first test.
Well, suprisingly enough, it didn”t install it… The SQL Server Installation wizard saw that the Microsoft Visual Studio Tools For Applications 3.0 was already installed (see picture below)

And when I verified the “Program and Features”, the Microsoft Visual Studio Tools for Applications x64 Runtime 3.0 was still missing.

  1. Installation of SQL Server Data Tools and Integration Services at the same time

I started again from a clean situation and installed SQL Server Data Tools and Integration Services at the same time.
The SQL Server Installation Wizard detects that it still has to install the Microsoft Visual Studio Tools for Applications 3.0, which is a good thing.

Finally, both x64 and x86 Tools have been installed!

Now, let”s see if my package will upgrade now without any error?

YES, IT DID!!!

 

If you would like to have this issue fixed, please vote on the following link

https://connect.microsoft.com/SQLServer/feedback/details/776248/could-not-load-file-or-assembly-microsoft-visualstudio-tools-applications-core

How easy is it to deploy your database to the cloud?

With the current hype in cloud computing, DBA’s want to have an easy way to deploy their database to SQL Azure. In previous versions, migrating a database to the cloud was a significant undertaking. You could download the SQL Azure Migration Wizard which actually created some scripts to move your database. Microsoft also provided following solution Migrating Databases to SQL Azure.  Other possibilities are BCP, building SSIS packages, “Generate script wizard”,…

I was wondering why it has to be that “difficult” and “complex”.  People are still using these solutions, which are certainly not bad, believe me, but…Now. with SQL Server Management Tools in SQL 2012  you can use the Deploy Database to SQL Azure feature, which under the hood exports a bacpac and then imports it to SQL Azure. It makes the life of the DBA much easier.  Let’s see how this works.

Open the Object Explorer and go to your database that you want to deploy. Right click on it and select Tasks – Deploy Database to SQL Azure

image

You should now see an introduction screen of the deploy wizard.  Just click next…

2

Now you need to specify the deployment settings.  First of all, you need to connect to your SQL Azure Server. Click the connect button.

image

Enter your SQL Azure credentials and click the connect button. Please note that the connection to a SQL Azure server in some companies can fail.  Probably because of firewall settings. …this company is not ready yet for the cloud… Denkende emoticon.

image

Once you are connected, specify the correct database name.
Select the Edition (Web or Business) of SQL Azure and the maximum database size that you need.  For more information on SQL Azure and Pricing check out this link

image

image

When you click next,you will get a summary of the specified settings

image

Click Finish to start the deployment to SQL Azure. The wizard will first evaluate your database if it meets all the requirements of SQL Azure. If not,it will fail, as you can see below Knipogende emoticon.

image

If you click on the “error link” to verify what is wrong.  In my case, I have a table with no clustered index.  That’s not allowed in SQL Azure.  Check the following page if you want an overview of the Guidelines and Limitations (SQL Azure Database)

image

You need to restart the wizard all over again.  Microsoft didn’t provide a retry button here…
I have corrected my database and retried the deploy again. Successful! As you can see, the wizard is first exporting the database, the schema and the data itself. Secondly a database on the SQL Azure is created, schema and data are imported.

image

Now let’s verify if my database is indeed deployed correctly.
When I check the Windows Azure Platform, I find indeed my new deployed database

image

Conclusion: The other deployment tools were very useful in previous versions but when you have installed SQL Server 2012, I really should recommend this new feature.

How to Audit SQL Audit

While I was preparing my session for my next TechNet Live Meeting – Who did what and when on my database?, I was curious how you could audit the SQL audit. Actually, my goal was to be notified if my audit object layout was changed, disabled or dropped.

First thing that you can do is configuring the action group AUDIT_CHANGE_GROUP in the server and database audit specification.  This action group will log an event whenever and audit is created, modified or deleted.  OK, fine, nice feature, however… If somebody changes or disables the audit, I will not be notified about it.  It will be in just the audit log… Unless I verify the audit logs, I will never be aware about the changes that have been made.

So I was thinking of a way how I could be alerted in a fast way if somebody or something modifies the server or database audits. I know that there are other tools that are made for monitoring and alerting. For example, Microsoft System Center.  A possible solution would be writing the audit events to the application log or security log of the SQL Server.  The System Center Operations Manager is dedicated to monitoring, reporting and alerting on Windows event logs. So, problem solved! However, not every small company has those tools available…

I was looking for a solution within SQL Server to send me an alert if my audit object has been changed.

” … to send me an alert…”. SQL Server Agent can handle alerts!

Let’s see how we can configure this.  I have the following configuration

Server audit with name “Server Audit”
Server audit specification with name “Server audit specification”
Database audit specification with name “Database audit specification”

I’ve created 2 alerts, one that will capture all the modifications on my server audit objects and one that will capture the changes on my database audit object. I have used the event classes Audit Database Object Management Event Class and Audit Server Object Management Event Class for my WMI query.  Be aware when you use the where_condition that you can only use the = operand together with DatabaseName, SchemaName and ObjectName.  Other expressions cannot be used with these event properties.

image
Audit Server Alert

image
Audit Database Alert

The response of each alert is the execution of a job.  The job is going to gather some information about the event as will send an E-mail.  You can find the script below to create the job.

USE [msdb]
GO

/****** Object:  Job [Send Alert]    Script Date: 28/03/2012 14:56:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 28/03/2012 14:56:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Send Alert’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Mail]    Script Date: 28/03/2012 14:56:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Mail’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’declare @Msg varchar(8000)
declare @sub varchar(128)
declare @State smallint
declare @Statename varchar(128)
declare @StartTime varchar(30)
declare @Description varchar(512)

SELECT @Sub = ”Server Audit State of $(ESCAPE_NONE(WMI(ServerName))) changed!”
set @StartTime=SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,0,5) + ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,5,2)
+ ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,7,2)
+ ” ” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,9,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,11,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,13,2)
SELECT @Msg = ”ObjectName: $(ESCAPE_NONE(WMI(ObjectName)))”
+ CHAR(13)
+”Hostname: $(ESCAPE_NONE(WMI(Hostname)))”
+ CHAR(13)
+”Loginname: $(ESCAPE_NONE(WMI(Loginname)))”
+ CHAR(13)
+”NTDomainname: $(ESCAPE_NONE(WMI(NTDomainname)))”
+ CHAR(13)
+”NTUsername: $(ESCAPE_NONE(WMI(NTUsername)))”
+ CHAR(13)
+”StartTime: ” + @StartTime
+ CHAR(13)
+ ”Executed Statement:”
+ CHAR(13)
+ ”$(ESCAPE_NONE(WMI(TextData)))”
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”Pieter”,
@recipients = ”pieter.vanhove@kohera.be”,
@body = @Msg,
@subject = @Sub’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Remark: Make sure that Database mail is configured correctly and that the setting “Replace tokens for all job responses to alerts” is checked.  You can find this setting by selecting the properties of the SQL Server Agent (SSMS –> Right click on SQL Server Agent –> Properties) and go to the Alert system tab.

image

I know that there are still some gaps in this solution, like “What will happen if somebody disables the alert or stops the SQL Server Agent”, but at least this blog can be considered as a start…

Happy auditing!

Snippets and Template Browser in SSMS

While I was working one my presentation for  12 hours of SQL Server 2012 I noticed a new feature of SQL 2012 called Snippets.  These code snippets are small templates that can be used as a starting point when building your queries. They can also be pretty handy for junior DBA’s that are not pretty sure about the syntax of certain statements.

Let me give an example.  I want to create a table Person with following structure:

Field Type
PersonID int
Last Name varchar(50)
First Name varchar(50)
Age smallint

Right click in your query windows and select “Insert Snippet” or just use the short keys CTRL+K, CTRL+X

image

The snippet window opens.  Just double click the type of snippet that you want to insert, in my case Table

image

image

As result you get the following code. As you can see, the snippet has some replacement points which are marked in yellow

image

Now, go through the template with “tab” and change the replacement points into the appropriate values that you want.  The replacement points remain active until you “close” the snippet. Just press “Enter” to close it.

My example,

CREATE TABLE dbo.Person
(
PersonID int NOT NULL,
Lastname Varchar(50),
Firstname varchar(50),
Age smallint
);

 

But… Didn’t we have this feature in older versions? I thought templates were actually providing the same. But in fact, there are some differences.  Let’s follow my example again, and create the same table by using the templates.

In the menu click on View – Template Browser

image

In the template browser, select Table and double click on Create Table

image

You’ll get the following result in your query window

— =========================================
— Create table template
— =========================================
USE <database,sysname,AdventureWorks>
GO

IF OBJECT_ID(”<schema_name, sysname, dbo>.<table_name, sysname, sample_table>”, ”U”) IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

You can use the Replace Template Parameters dialog box to specify values any time a parameter definition is used in code.  Or just change the code manually.

image

image

As you can see the sample code that is provide by default is slightly different.  In the template code, they added the IF clause to determine if the table already exists or not.  The template also provides more detailed information on the create table statement like identity, data types, constraints and primary keys,…

A snippet is really something you can use as a starting point when building your queries, I call it a quick insert of a SQL statement, while a template provides a more detailed pre-written SQL statement. Templates are highly useful queries to jumpstart some of the “not-so-familiar tasks”. They also come in handy when writing deployment scripts. Templates are also a place to store and organize your own parameterized queries. I personally, used templates a lot when I was working as a junior DBA.  Once you have used them a lot, you will know them by hart Knipogende emoticon

Restore Database Enhancements in SQL 2012

In SQL 2012, Microsoft has introduced some nice restore database enhancements.
The major enhancements are:

  • Point-in-time restore has now a visual timeline that allows you to quickly select the target time and perform your restore.
  • Page Restore worked already in SQL 2008 (R2) and SQL 2005 but it has now a nice user interface. It allows you to check your database for corrupt pages and restore them from a good backup file.

In this blog, I’ll give you an overview how to use these 2 new features.

Point-in-time restore

In the Object Browser of your SSMS, right click on Databases and select “Restore Database”
In this example, I will perform a restore of the Adventureworks2008R2 database.  I selected Device to get my backup files.  Just press the […] button

Restore1

In the locate backup file window, I select all the backup files (Full backups and Transaction Logs) that have been made.  To create the backups, I just created a simple Maintenance Plan.
Click on OK.

Restore2

Now all the backup sets are in the list (this is not new…).  As you can see, there is a new button called “Timeline”.  Click on it to open the timeline interface.

Restore3

Now, you can choose to restore to the last backup taken or choose a specific date and time.  With the timeline, you can scroll to the restore time that you want. On the timeline you can also see what types of backups will be used to perform the restore. Once you selected the correct time, just press the OK button.

Restore4

Now press OK again, to start your restore. A restore plan is automatically generated and your database is restored till the requested time.

Restore5

Restore6

How cool is that!

Page Restore

To perform a page restore,I first need to have a corrupt database Knipogende emoticon and you also need to have a GOOD backup file,which means, without the corrupt page.
As you can see below, I did a DBCC checkdb and my database is indeed corrupt.

RestorePage1

Let’s fix this database!
Right click on your DB, select Tasks – Restore – Page

RestorePage2

In the Restore Page window, the database is selected and the Pages grid is automatically showing the  damaged pages. You can also run DBCC CHECKDB, by clicking on the button “Check Database Pages”, to find out if there are more damaged pages in the database. You also need to set the location for the Tail-Log backup file. The Backup sets grid shows you all the backups that can be used to fix your pages.

RestorePage4

RestorePage3

Just click on the OK button to start the page restore

RestorePage5

When I check my database again with DBCC CHECKDB I see that the damaged page has been fixed Emoticon met brede lach

RestorePage6

I think those 2 new features will make the life of the DBA just a little bit easier.

Have fun!

How to configure Availability Groups in 10 steps

Last week I installed SQL Server 2012 RC0 which was released a few weeks ago.

I was playing around with the AlwaysOn high availability groups and noticed some small, but nice, improvements in the Availability Group Wizard.

It has become very easy now to configure an availability group in just 10 steps.

In this post you can find the steps I followed, I will also describe the enhancements in the wizard that were not in the CTP3 version.

Step 1: Startup the wizard

  1. In Object Explorer, connect to the server instance that hosts the primary availability replica, and click the server name.
  2. Expand the AlwaysOn High Availability node.
  3. Right-click the Availability Groups node, and select the New Availability Group

Now here we have already the first difference compared to CTP3. In the old version you had to go to “Management” à Availability Groups. With RC0, Microsoft created a separated node called “AlwaysOn High Availability”

Step2: Specify Availability Group Name

Just specify the name for the availability group.

Step 3: Select Databases

Select the databases that you want to be part of the availability group.
If you notice that you can”t select one of your database, check the status column to find out the reason, for example your recovery model is set to simple or no full backup of the databases is available.
More information on Checklist: Requirements Availability Databases

 

Step 4: Specify replica

In this step, Microsoft changed a lot compared to CTP3.
For each replica, you can now choose if it should use Automatic Failover of not and if it should use Synchronous Commit mode or not.
In CTP3,you only had the option Replica Mode where you could choose the options Automatic Failover,High Performance and High Safety.

Automatic Failover à Synchronous Commit mode will be selected to because you cannot combine asynchronous commit mode with Automatic Failover
High Performance à Don”t select Automatic Failover and don”t select Synchronous Commit
High Safety à Select Synchronous Commit. Don”t select Automatic Failover. Only manual failover will be possible. If you should select Automatic Failover, you”re back to the first mode.

The readable Secondary column has the same functionality but Microsoft just put other “names”
In CTP3 you had Disallow Connection, Allow all connections and allow only read-intent connections

CTP3

RC0

Disallow Connection No
Allow all connections Yes
Allow only read-intent connections Read-intent only

 

 

Step 5: Specify Endpoints

In this section you have to specify the endpoint that you want to use for your mirroring session.
This is actually the same like configuring endpoints for database mirroring.
Nothing changed much here compared to CTP3


Step 6: Backup Preferences

The backup preferences tab is a new tab that has been added in RC0.
It allows you to specify where your backups have to occur.
You can choose between:

  • Prefer Secondary
  • Secondary Only
  • Primary
  • Any Replica

The only “issue” is that the DBA has to implement the logic himself. You can use the sys.availability_groups table to find the backup preference. Also check the sys.availability_replicas to check the replica backup priority. By querying these views and tables, you can determine which replica should run a given backup job by running a simple script.

I”m currently working on that script, which will be posted later on.

More information can be found on Backup on Secondary Replicas (AlwaysOn Availability Groups)

Step 7: Listener

Your application should use the listener to connect to the databases. The listener is owned by the primary server, so for the application it’2014-03-28 19:35:30’s transparent which server is the active on because it”s connecting to the listener.
The big improvement in this wizard of RC0 is that you can specify a static IP address. In CTP3, you only had the option to specify DHCP. If you wanted to use a static IP, you had to skip it in the wizard and configure the Listener afterwards.

Just specify the DNS name, port and network mode.

Step 8: Select Initial Data Synchronization

In CTP3 you only had the option Full (formally known as “Perform initial data synchronization”) and Skip initial data synchronization.

In this new version, Microsoft added an extra option Join only. If you want to use this option, the database must already be restored on the replica.
This option will then just join the selected database to the availability group.


Step 9: Validation

Now your complete setup will be verified. Everything should be successful before you can continue with the next step.

Step 10: Summary and Results

The wizard creates a summary for the user. The script button will generate a script with all the settings that you have selected.
Might be handy if you want to test different configuration and you don”t want to go through this wizard every time.
When you click finish, the availability group is created and finally you should have a successful result