2019-03

Fix For Using Azure Active Directory to Connect to SQL

Hello! The other day someone was kind enough to get in touch with me to thank me for some post I wrote back last year. For the sake of brevity, there’s all sorts of nastiness in trying to authenticate using Azure Active Directory when connecting to an Azure SQL Instance/Managed Instance. And the function what I wrote aimed to automate uninstalling and installing the “correct” version of the adalsql.dll required. Thing was I admitted it was a hack in the original post, and the worst thing about a hack is that they tend to be a bit….

2019-03-12

2018-06

PoshSSDTBuildDeploy 2.0.271.0 - Now With Better Exception Handling!

Hello!

Another quick bulletin about the changes I made to PoshSSDTBuildDeploy today -

2018-06-22

PoshSSDTBuildDeploy 2.0.268.0 - Now With Issue Management!

Hello!

Concluding this weeks work on PoshSSDTBuildDeploy is adding one of the features I spoke about yesterday, which was joining the OperationSummary and Alerts objects to produce a new table that will make it easier to figure out which Operation is creating which Issue.

Let’s revisit the xml file that I running my tests with -

2018-06-15

PoshSSDTBuildDeploy 2.0.262.0 - Now With Warnings!

Hello!

Continuing on with my efforts to make the SSDT Deployment Report more useful, I’m pleased to say that I’ve added reporting on any alerts that appear in the Deployment Report. Let’s take a look at a report that has warnings in it -

2018-06-14

More Changes to PoshSSDTBuildDeploy - DeployReport Information

Hello!

This is going to be a short one because I’ve been working hard all evening on this, and frankly I’m a little tired. But I’ve made a change to PoshSSDTBuildDeploy which is going to be the start of more changes. Intrigued? Well, read on…

2018-06-12

2018-05

Birthday Musings and Deploy Using PoshSSDTBuildDeploy

Hello!

It is my birthday: another year older, another year not necessarily wiser. I’m planning on spending the evening watching the BBC and eating pizza. There are worse ways to spend a birthday…

2018-05-31

Fix For Using Azure Active Directory and DacFX

Update:

Refer to this post instead

Hello!

One of the things I feel strongly about is that a build/release pipeline needs to be as complete as possible - that is all configuration required to complete the build/deploy process needs to be automated in some way or another. And as we will see from todays post, this tends to create further complexity, and unfortunately sometimes we have to get inventive for a process to work…

2018-05-25

PoshSSDTBuildDeploy Updated - Now With Pester Tests

Hello! Some weeks ago I got the tests working on the CI build for PoshSSDTBuildDeploy. The tests were a bit of a mish mash, however they sort of did the trick. Thankfully Eugene Niemand came along and very kindly offered to update the tests into Pester tests, and also just generallytidy up the mess. It’s always great to have a second pair of eyes on any code, as it helps it from not being quite so auteured…

2018-05-23

PoshSSDTBuildDeploy Updated - Now With Install .NET!

Hello! I’m currently working on a project that requires building and deploying SSDT-based projects. So it’s been a great opportunity of making use of PoshSSDTBuildDeploy and work through any issues. And there have been a few minor updates to the module over the past fortnight which I’ll go through now! As I type the latest version is 2.196.0. Return Path of MSBuildDataTools - Just so much easier to have location returned so that when you execute Invoke-MSBuildSSDT and Publish-DatabaseDeployment you can just pass the outputted variable in.

2018-05-01

2016-10

SQL Server Compression 101

The data compression feature in SQL Server can help reduce the size of the database as well as improve the performance of intensive I/O intensive workloads, especially on data warehouses. This performance boost in I/O is offset against the extra CPU resource that is required to compress/decompress the data whilst data is exchanged with the application. With data warehouses, the guideline is to compress all objects as there is typically CPU capacity whilst the data storage and memory capacity is at a premium, so Microsoft’s recommendation is to compress all objects to the highest level. However, if there are no compressed objects at all, it is better to take the cautious approach and to evaluate each database object individually and the effects on the workload, particularly if the CPU headroom is limited.

2016-10-28

2016-08

Turbo LogShip 1.0 Released

Earlier this year, I wrote a long post about log shipping. One of the key points I mentioned was how slow restoring can be for a read only log shipped database. If going and reading the whole thing is too much effort for you now, I’ll save you the effort (you child of the internet you) and tell you it’s because the database needs to be kept transactionally consistent in between restores when making it available for read-only. It creates a .tuf file (transaction undo file) to keep the progress of all the pages that have uncommitted transactions saved to them. The checking, creation, applying and re-applying of this can take some time where there is a significant amount of uncommitted transactions within a log backup.

2016-08-30

2016-02

SQL Server Database File Size and Free Space Revisited

I recently had a comment posted to one of my first articles I wrote way back in 2012 concerning adding a column that detailed the used space as a %age. So without any further ado, here is an updated version of a script originally blogged about here. select sf.FILEID AS [File ID], [File Size in MB] = convert(decimal(12,2),round(sf.size/128.000,2)), [Max Size in MB] = convert(decimal(12,2),round(sf.maxsize/128.000,2)), [Space Used in MB] = convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)), [Free Space in MB] = convert(decimal(12,2),round((sf.

2016-02-06

2015-12

Log Shipping: It's Better Than Bad It's Good!

2015-12-15

2015-04

Finding Locks on Tables

Recently I needed to find a stored procedure that was creating a lock on a table and updating the table regularly. So my starting point was the table name, but I needed to find where the commad was coming from that was locking the table, and what stored procedure was doing the update, if any. As I could not be sure that it was a sproc, I wanted to make sure that I also got the program name.

2015-04-30

2014-10

EXEC sp_executeSQL

Today I am going to talk about the stored proc sp_executesql and the benefits it has over EXEC @SQL. Microsoft actually recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. This is because of something called parameter substitution, which makes Sp_executesql a far more efficient method of executing dynamic SQL.

2014-10-27

2014-08

How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table

Another pithily titled blog post, but this one revisits the feature of SQL Server 2014 that piqued my interest the most: Clustered Columnstore Indexes. I’ve been working with Clustered Columnstore Indexes for the past few months and recently a colleague had to split a non empty partition, only for the error message below to appear.

Msg 35346, Level 15, State 1, Line 390 SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Ok, so it looks like we cannot split non empty partitions with a Clustered Columnstore Index on the table, or at least an enabled clustered columnstore index. So the topic for todays post is to find out how can we split a non empty partition when a clustered columnstore index exists on the table. We can create a test database/tables/Clustered Columnstore Indexes and disable the indexes before we split to see if that is the solution, as suggested in the error that it might be. Let’s start at the beginning using the database I created for my previous post on SQL Server 2014 Clustered Columnstore Indexes and Partitioning. The script below will create the database and the objects necessary. I’m going to state the obvious, but read through the scripts first before you run them; things like file locations on boxes are always different:

2014-08-18

2014-06

Microsoft's Lousy Response to a Connect Issue

I’ve been going through the process of upgrading from SQL Server 2012 to SQL Server 2014 and I’ve hit a bug when running snapshots. Any snapshot that was in place from SQL Server 2012 cannot be dropped in SQL Server 2014. There’s a Connect article already open.

2014-06-18

2014-04

SQL Server Partitioning 101

I’ve worked with partitioning for quite some time now, and though it has been around for quite some time and lots has been written about the concept I recently presented an example of partitioning to a few people within a team and felt it was worth sharing on this blog. In today’s post I am going to demonstrate how to create partitions for a table, what happens when you insert data, how to split data out, how to switch partitions and finally merging partitions.

2014-04-23

2013-12

Moving Tables that use Partitioning and Compression Across File Groups

2013-12-10

2013-11

Analysis Services Server Activity Monitor 2014 0.0.0.1 Alpha Released

This morning I am happy to announce the release of an alpha version of the SSAS Activity Viewer for SQL Server 2014 CTP2. The name has had a slight change and is now called Analysis Services Server Activity Monitor (ASSAM) 2014.

2013-11-11

UK SQL Relay 2013 R2 Plus Travel Info for London and Southampton

Throughout November, SQL Relay R2 2013 is taking place in the UK. This is the 2nd series of events this year. SQL Relay is a series of 1 day events that are run throughout the UK. Each event is a single track (or more in the case of London) conference, with between 50 and 200 attendees and 6-8 hour long talks by SQL Server professionals, MVPs, authors, technical experts etc.

2013-11-04

2013-10

SQL Server 2014 CTP2 Available to Download

At the PASS Summit keynote today Quentin Clark announced that SQL Server CTP2 (which is public and the last public review) and is available right now to download here. It is also available as a VM on Azure. According to a tweet from Glenn Berry, the version number in the demo is 12.0.1524.

Before what you download and install, here’s what you ought to know;

2013-10-16

SQL Server Analysis Services Activity Viewer 2012 1.1 Stable Released

This morning I am happy to announce the release of a stable version of SSAS Activity Viewer 2012. The build number is 1.1.0.0. The new features have been highlighted below in the release notes section.

2013-10-10

SQL Server Analysis Services Activity Viewer 2012 1.0.0.1 Beta Released

(Worth noting that there is a more recent version available here!)

Hi all, yesterday morning I created a project based on an old blog post Upgrade Analysis Services Activity Viewer 2008 to SQL Server 2012. Over the course of the day I made a few changes that I wanted to see myself and this afternoon I have released a beta version, SQL Server Analysis Services Activity Viewer 2012 1.0.0.1, or SSASAV 2012 1.0.0.1 for short.

2013-10-04

Get The Backup History for Databases

Below is a script that I use to find the backup history for all the databases on a single instance. There is an inline ‘WHERE’ clause that is currently commented out that you can add in to filter by database name.

2013-10-03

2013-09

SQL Server Hardware and Storage Links

Being a DBA is more than just knowing about SQL: it’s about the technology which SQL runs on, which includes servers and storage. I’m naturally interested in hardware, so I enjoy keeping up-to-date. Becoming acquainted and staying current with new technologies will not take up too much time, plus it will make you a better DBA. Below is a few resources that I have used to keep up-to-date with hardware and storage.

2013-09-24

Extract DacPac Using Command Line

When extracting Dacpacs it’s quite straightforward to go through the UI and extract, however what if you want to do this for more than one database, or automate the process for a build? The sqlpackage.exe enables us to extract a dacpac in silent mode. Below is the command, and I will go through each parameter: "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Extract /OverwriteFiles:False /tf:"C:\Users\richard.lee\Documents\dacpacs\dbname_Baseline.dacpac" /SourceConnectionString:"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=SQLA" Some you need to be wary of:

2013-09-19

Cumulative Updates - SQL Server 2012 SP1 CU6

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #6.To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 23 fixes and is pertinent for versions 11.0.3000 – 11.0.3373.0 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999). There seem to be quite a few SSAS updates in this release.

2013-09-18

Unknown Unknowns: Analysis Services Failing to Start

2013-09-09

Cumulative Updates - SQL Server 2012 RTM CU9

A 9th Cumulative Update is available for those users still on the RTM branch of SQL Server 2012. This is in keeping with Microsoft’s Incremental Service Modelling. http://support.microsoft.com/kb/2867319You may have to restart your machine before the update is applied. Please check that the hotfixes which are included in this cumulative update package are relevant to you, though some of these seem fairly important, such as poor performance when you run a query that uses the LIKE operator in the WHERE clause.

2013-09-06

2013-07

Cumulative Updates - SQL Server 2012 SP1 CU5

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #5.To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 30 fixes and is pertinent for versions 11.0.3000 – 11.0.3368 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)

2013-07-16

2013-06

SQL Server 2014 CTP1 Released

This week has been the week of the release: We’ve had the Visual Studio 2013 Preview and now the SQL Server 2014 CTP1. And that’s just the ones I’m focusing on! There’s the Windows 8.1 Preview as well as Windows Server 2012 R2. What with all the keynotes from Build and both TechEd’s this month, it is clear that Microsoft have certainly accelerated the release cycle and embraced a cloud-first development model. For me this is most noticeable in Team Foundation Services, the Azure based Source Control solution. Features were turned on regularly before they were released in the three updates we’ve had since the release of TFS 2012 back in August. This is a big change from the Microsoft’s previous strategy of developing for its products hosted on the premises first and then being pushed to them to the cloud afterwards.

At a later date I’ll dig more into features of SQL 2014, as this week my focus has been on Visual Studio 2013, which is out sometime this year, as well as doing my job in real life. The duties of blogging…

2013-06-28

SQL Server 2014 Announced

Introduction

A week late and more than a few dollars short, this blog post is focusing on a few of the features announced for SQL Sever 2014. I’ve watched the Keynote and a few of the videos from the sessions at TechEd North America 2013, and read the whitepapers and datasheets. Information is still limited though, like which versions the features will be added to, so it’s certainly not a deep dive into the features. Nor is it an exhaustive list of the features; moreover, it’s a look at those ones that immediately piqued my interest in the Mission Critical Performance category of improvements.

2013-06-10

Cumulative Updates - SQL Server 2012 SP1 CU4

As part of theirIncremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #4.http://support.microsoft.com/kb/2833645 To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 39 fixes and is pertinent for versions 11.0.3000 – 11.0.3367 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)The build number of the cumulative update package is 11.

2013-06-08

2013-05

SSIS 2012 Deployment Fail Part Two: SSIS Server Maintenance Job

Recently I post about a timeout issue I was getting when deploying SSIS projects. It was mainly to do with too much log and previous versions being kept in the SSISDB on our test environments. My solution was to run the SSIS Server Maintenance Job prior to every deployment to mitigate the timeout. By and large this has been super effective. However that job can take a considerable amount of time to run.

2013-05-30

SSIS 2012 Deployment Fail

(Update: Part 2 focuses on clearing out the SSISDB by creating new stored proc based on the one used by the maintenance job. If your Maintenance Job is taking hours to run, you need to check this out.)

No WiX Wednesday this week, owing to commitments in real life. Instead, here is something regarding SSIS 2012 Deployment. Enjoy!

As part of our CI and Test Builds we have automated the deployment of two SSIS Projects. One is fairly large and the other one contains only two dtsx packages. Recently we have been getting timeout issues with the deployment of the solutions.

2013-05-22

2013-04

Choosing the Right Processor for your SQL Server Workload

Last year, we planned to move our data warehouse from a 2 socket server to a 4 socket server that was left in the data centre from an abandoned project to virtualize our desktops.

2013-04-26

Find Largest Tables To Compress

In this post I’m gong to show some T-SQL that will show you the compression state of tables and how to quickly find objects that are worth compressing.

Last week brentozar.com ran one of their Technology Triage Tuesdays on Compression in SQL Server. The video does not appear to be up yet, however it provided good insight, certainly better than the Technet pages on the same subject. As we have a multi-terabyte data warehouse at work, which is on an Enterprise licensed instance of SQL Server 2012, I’m familiar with the subject of data compression in SQL. Until recently however, another one of our other SQL Servers was on a Standard license. Recently this instance was upgraded to Enterprise, and so I was able to compress the database. Although not as large as our data warehouse, it was well worth considering compressing some of the larger tables.

2013-04-16

Cumulative Updates - SQL Server 2012 RTM CU7

A 7th Cumulative Update is available for those users still on the RTM branch of SQL Server 2012. This is in keeping with Microsoft’s Incremental Service Modelling. http://support.microsoft.com/kb/2823247You may have to restart your machine before the update is applied. Please check that the hotfixes which are included in this cumulative update package are relevant to you, though some of these seem fairly important. This CU has 17 fixes, and the version number will now be 11.

2013-04-16

Unknown Unknowns: SSAS Version Number and Default Admins

With Service Packs and Cumulative Updates rapidly released for SQL, it can be hard to keep up with what version you have installed where. It’s easy enough to find out which version of SQL Server you have installed just running the following T-SQL and you can find out all the info you need. But what about SSAS?

2013-04-09

2013-03

Cumulative Updates - SQL Server 2012 SP1 CU3

As part of theirIncremental Service Modelling , Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #3.http://support.microsoft.com/kb/2812412 This CU contains 38 fixes and is pertinent for versions 11.0.3000 – 11.0.3348 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)Please check that the hotfixes which are included in this cumulative update package are relevant to you, unlike the last CU2,which fixed a major bug , it is generally recommended that you only install a CU if one of the fixes is pertinent to you.

2013-03-19

2013-01

Important Update for SQL Server 2012 SP1

One of the challenges we faced this week was a high CPU usage on one of our SSAS Cluster boxes when there was very little activity, plus some errors in the event viewer being logged repeatedly: We escalated it to the documentation team* and found a KB article on Microsoft. Be warned though, we did have to restart the server in order for this hotfix to apply. But it fixed our problem.

2013-01-21

SQL Bits XI Registration is Now Open

Hi all, registration to attend SQL Bits is now open. http://www.regonline.com/Register/Checkin.aspx?EventID=1163518 There’s a 600 capacity limit and they anticipate it to fill rather quickly. More details can be found here: http://sqlbits.com/ Hope to see you there!

2013-01-11

Reports on Disk Space and Space in Files

Previously I have blogged about how to check for free space in a database at a file level; a file for a database can be very large, but the amount of data stored within the file can be very little. When initializing a file for SQL, it is generally best practice to create it to the size you think it is going to be so that auto-grow is not initialized as this can cause a loss in performance.

2013-01-05

List of Disks and Free Space

Recently I have been supporting our move from TFS from 2010 to TFS 2012. The database instances is stored on a server that I do not have log on rights to. During the upgrade, it became clear that one of the tables was growing at a rapid rate. This is because rows were being moved from one table to another, and there were a lot of rows to move: I came back from my Christmas break on the 31st and apparently it had been running from the 28th!

2013-01-03

2012-12

SQLBits XI Dates Announced

SQLBits XI has been announced for the 2-4th of May 2013. Having taken place in London this year, it is moving back North to the Midlands at the East Midlands Conference Centre. Apparently there are great transport links, and being smack in the centre of England I guess it makes it convenient for everyone to get to. If you work with SQL Server in any way, and you’ve never been to SQLBits I’d highly recommend it, because

2012-12-31

Introduction to Hekaton Part One: MVCC

Hello folks, welcome to a post focusing on one of the future features of SQL Server. SQL Server 2012 is barely a year old, yet there is already talk on a feature that is to ship in the next major release of SQL Server. The current name of this feature is Hekaton, which is Greek for 100. I believe the reason for this name is because of the performance boost that one online gaming company experienced when working with Microsoft in implementing the technology preview of Hekaton.

2012-12-30

SQL Server Database File Size and Free Space

(There is a more up to date version of this script here.

Hey folks, and welcome to my first proper blog post. One of the things that I like to monitor through my daily checks in the file size and the free space of each file within the databases. During a busy loading period, some databases can grow massively, so it’s important to keep an eye on the growth. Through the UI, SSMS only gives us the size of the files and what the max size is that they can grow to, which is clearly not very useful. Fortunately, a quick query on the dbo.sysfiles of each databases that we want to monitor gives us some info, but this isn’t entirely too useful. For starters the size is in KB. This makes sense as databases store data in 8kb sized files. Whilst it may be OK for a small database like this one, our data warehouses are far to big for us to find sizes in KB useful. Also, we can infer the remaining space, but again it’s not too helpful by having to figure this out for ourselves.

2012-12-24