SQL Server 2014 Developer Edition is now a free download

As of the 31st of March, SQL Server 2014 Developer Edition is now a free download:
https://myprodscussu1.app.vssubscriptions.visualstudio.com/Downloads?pid=1682

SQL Server 2014 Dev Download VS

Quote from MSDN:

SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.

Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support.

Ohh….and this is also a nice one:

SQL Server 2016 Developer Edition, when released later this year, will also be free.

Read the full post on the SQL Server Blog: https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

 

Evaluation Editions:
Just want to download an evaluation edition?:
Microsoft® SQL Server® 2014 & 2016 Evaluation
Microsoft® SQL Server® 2012 Evaluation

SQL Server 2014 Dev Download

 


 


 


 

Pause SQL Server service before restarting

Did you know that by pausing the SQL Server service before restarting the instance we allow end users to continue their work uninterrupted and we also stop any new connections to the instance? This is a nicer way of telling people to “get out” of the database in order for the server to be rebooted. I wouldn’t leave the server paused for 60 minutes of course, but I would rather use this method than forcibly disconnect users and rollback their transactions.

When a server is paused you will see messages similar to this in the SQL Server error log:

Error: 17142, Severity: 14, State: 0.
SQL Server service has been paused. No new connections will be allowed. To resume the
service, use SQL Computer Manager or the Services application in Control Panel.

Error: 18456, Severity: 14, State: 13.
Login failed for user ''. Reason: SQL Server service is paused.
No new connections can be accepted at this time. [CLIENT: ]

Next time you are worried about rebooting during the day think about the pause button instead. It might be a nice compromise for your end-users.

Thank Thomas Larock for his blog post: http://thomaslarock.com/2016/01/pause-sql-server-service-before-restarting/








SQL Server Window Functions Cheat Sheet (OVER-clause)

It all started with SQL Server 2005 which introduced the OVER-clause. It was (back then) possible to add a row number to a dataset by using something similair to:
ROW_NUMBER() OVER()

You were also able to use the Ranking functions, such as ROW_NUMBER, RANK, DENSE_RANK and NTILE.

Now with SQL Server 2012, Microsoft introduced a few more so called ‘Window functions’ (aggregate and analytic):

SQL Server Window Function OVER clause


Here’s an overview:
sql-server-2012-window-functions-over-clause-cheat-sheet-by-clint-huijbers


Download:
Download my cheat sheet here (in Word format)








FORMAT() function in SQL Server 2012/2014

The FORMAT() function is one to really remember, it saves you a lot of time.
Here are a few examples:


SELECT
FORMAT(12345678,'n') AS n
,FORMAT(12345678,'n0') AS n0
,FORMAT(12345678,'n0','nl-NL') AS n0NL
,FORMAT(0.8856,'p') AS p
,FORMAT(0.8856,'p1') AS p1
,FORMAT(12345678,'c') AS c
,FORMAT(12345678,'c1') AS c1
,FORMAT(12345678,'c1','nl-NL') AS c1NL

DECLARE @d DATETIME = '10/01/2011'

SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';



sql-server-2012-format-function-examples-with-regional-code



For more examples, please visit:
http://www.codeproject.com/Articles/610089/SQL-Servers-FORMAT-function








SSIS: Extract package information (e.g. DataFlowTaskName, TaskDescription, PrecedenceConstraint, etc.)

Found this great blog post from Saravanan:
http://sqlmall.wordpress.com/2014/05/29/extract-ssis-2012-package-data/

He describes a way to search through the XML coding of your SSIS packages, to for example, look for a certain DataFlow which is called ‘RAW_DWH_Main‘.

Step 1) Enable FILESTREAM via SQL Server Configuration Manager

http://sqlserverscribbles.com/2013/07/09/how-to-enable-and-configure-filestream-in-sql-server-2008-2012/
http://msdn.microsoft.com/en-us/library/cc645923.aspx

Step 2) Create the FileTable (and a new database)
NOTE: Make sure to fill in an existing path for the directory.


EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

CREATE DATABASE PackageDefinition
ON PRIMARY
(Name = PackageDefinition,
FILENAME = 'E:\FileTableDB\PackageDefinition.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = Package,
FILENAME='E:\FileTableDB\PackageDefinition')
LOG ON (Name = PackageDefinitionLog,
FILENAME = 'E:\FileTableDB\PackageDefinitionLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'PackageDefinition');
GO

USE PackageDefinition;
CREATE SCHEMA FT
GO

CREATE TABLE FT.PackageDefinition AS FileTable
WITH
(FileTable_Directory = 'PackageDirectory');
GO

Here’s your new FileTable:

Search-in-ssis-packages-with-sql-filetable-structure


Explore the directory and copy/paste your SSIS packages here

Search-in-ssis-packages-with-sql-filetable-explore-directory


Now copy (or perhaps move/relocate) your TFS to this directory.

Almost done…run the query!

;WITH XMLNAMESPACES
(
'www.microsoft.com/SqlServer/Dts' AS DTS
),

PackageDefinition
AS
( /*Query the file content([file_stream]) from filetable*/
SELECT *
,FileTableRootPath() AS RootPath
,file_stream.GetFileNamespacePath() as FilePath
,path_locator.GetLevel() AS PathLevel
,PackageData = CAST(CAST([file_stream] AS VARBINARY(MAX)) AS XML )
FROM [FT].[PackageDefinition]
WHERE file_type = 'dtsx' --SSIS Packages
--AND Name LIKE '%RAW_DWH_Main%' --FileName
AND file_stream.GetFileNamespacePath() LIKE '\PackageDirectory\DWH.SSIS\%' --Project folder
AND path_locator.GetLevel() = 2

)

SELECT r.*
FROM
(
SELECT
RootPath
,FilePath
,PathLevel
,PackageName = Exe.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,CreationDate = Exe.pkg.value ('@DTS:CreationDate' ,'DATETIME2(0)')
,CreatorName = Exe.pkg.value ('@DTS:CreatorName' ,'VARCHAR(250)')
,DataFlowTaskName = Task.pkg.value ('@DTS:Description' ,'VARCHAR(250)')
,TaskDescription = Task.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,PrecedenceConstraint = '['+REPLACE((pc.pkg.value ('@DTS:From','VARCHAR(250)')),'Package\','')+'] ' +'---->' + '['+REPLACE((pc.pkg.value ('@DTS:To' ,'VARCHAR(250)')),'Package\','') +']'
,refId = Con.pkg.value ('@DTS:refId' ,'VARCHAR(250)')
,ConnectionProvider = Con.pkg.value ('@DTS:CreationName' ,'VARCHAR(250)')
,ConnectionManagerName = Con.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,ConnectionString = Par.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,ParameterName = CM.pkg.value ('@DTS:ConnectionString' ,'VARCHAR(250)')
,ParameterValue = Par.pkg.value ('.' ,'VARCHAR(250)')
,VariableName = Var.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,VariableValue = Var.pkg.value ('.' ,'VARCHAR(250)')
,PackageData
FROM PackageDefinition
OUTER APPLY PackageData.nodes('/DTS:Executable') AS Exe(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:Executables/DTS:Executable') AS Task(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:PackageParameters/DTS:PackageParameter') AS Par(pkg) --Will only return data while using the 'Package Deployment'-model I guess.
OUTER APPLY Exe.pkg.nodes('DTS:Variables/DTS:Variable') AS Var(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:ConnectionManagers/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager') AS CM(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:ConnectionManagers/DTS:ConnectionManager') AS Con(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:PrecedenceConstraints/DTS:PrecedenceConstraint') AS pc(pkg)
) AS r
WHERE r.DataFlowTaskName LIKE '%MyDataFlow%'
ORDER BY FilePath



The results:

Search-in-ssis-packages-with-sql-filetable-xml-query-results


Important note:
The query doesn’t return any nested tasks, you may need to use a LIKE-operator for that:
WHERE CAST(PackageData AS NVARCHAR(MAX)) LIKE '%%'

If you have a solution for this, please let me know or post the solution in the comments. Thanks!


More info about FileTables:
http://blog.tallan.com/2012/01/01/its-a-file-system-its-a-database-table-its-sql-server-denali-filetable/

How-filetables-work-more-info-sql-server








How To: Build a (SQL) Server Inventory

Thanks to Brent Ozar for this one.

He describes how you could easily populate a list of (SQL) Servers by using one or a combination of these tools:

How to Survey Your Network for Servers
Put a row in the spreadsheet for every server you have – whether you’re in charge of it or not. We want to start with a good inventory of what we have, and there’s two good free tools to do it.

Microsoft Assessment and Planning Toolkit – it’s actually designed for licensing compliance, but it works great for building server inventories. It scans your network looking for whatever programs you pick, but just confine it to SQL Servers only.

Dell Discovery Wizard for SQL Server – it’s a GUI tool that pings all the servers in your network and tries to figure out if they’ve got SQL Server installed. If you’re in a small shop where your account has admin privileges in the domain, you might find a lot more servers than you expected.

SQL Power Doc – open source PowerShell scripts to discover, document, and diagnose your SQL Servers. I’m not gonna lie: this one is not for the faint of heart, but if you’re willing to get your hands dirty and deal with some manual work, the end result is a nice set of Excel spreadsheets.








SQL Server alerts and email operator notifications

Today I wanted to setup a few necessary alerts on my SQL Server Instance and found this step-by-step walkthrough:
http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

Next thing I found was a script by the team of Brent Ozar:
http://www.brentozar.com/blitz/configure-sql-server-alerts/

Nice script of course, but I renamed the Alerts to make more sense (to me at least):
sql-server-alerts-create-script

Here’s how it looks:
sql-server-alerts

Download the full script here:
sql-server-alerts-create-script



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group