#TSQL2SDAY #77 – Favorite SQL Server Feature – RoundUp

This week, I have been looking forward to the time where I got to read through all the contributions to my #TSQL2SDAY invitation: Favorite SQL Server Feature. Very happy to see this many. I have added a short description of each blog post, as well as my own personal key take-away(s).

So, in no particular order, I give you the round-up:

Rob Farley (b|l|t)

Rob writes about SQL Server being backwards compatible and why that is cool. Rob also encourages to upgrade, if you are stuck on older versions – it’s what Microsoft recommends as well.
I also like that fact that with Azure SQL Databases, you don’t need to make the decision – it’s taken care of for you!
Key Take-Away: “…we now recommend ongoing, proactive installation of CU’s as they become available.

Reasons To Upgrade SQL Server

Ben Miller (b)

Ben makes a great point in introducing Server Management Objects (SMO) – I like the fact that the post is kept as simple as possible, but still explains the why’s and how’s. If you are not aware of SMO, please do get going and Ben’s place is a good start!
Key Take-Away: I know SMO already, so no gain here…

T-SQL Tuesday #77- SMO is my favorite feature

John Morisi (b)

John must have balls the size of Texas. His favorite SQL Server feature is Replication! No, kidding aside. John offers a quick intro into what options there are with Replication and makes an effort to debunk some of the bad reputation Replication has. Do you wanna know more?
Key Take-Away: “…Replicate data to any ODBC or OLE DB accessible database including Oracle, AKA heterogeneous replication.

Favorite SQL Server Feature – Replication

James Anderson (b|l|t)

Has written about Partition switching. This feature is as cool as it sounds – and what even more cool, it’s quick! I have used this technique in my ETL, at times. Normally data can be moved quickly enough, but when you need it done ultra fast, this is definitely an option worth investigating.
James also provides a little insight into new features in the upcoming release of SQL Server 2016.
Key Take-Away: “The good news is that the SWITCH command works on regular tables and in any edition.

Partition Switching #TSQL2SDAY #77

Shane O’Neill (b|t)

Seems this is Shanes first blog post ever, so kudos for making the entrance on the #TSQL2DAY stage! Shanes first adventure in blogging hits it out with an excellent run-down of Common Table Expressions, and a recursive one that is!
Key Take-Away: “…you are SELECTing from the CTE while still defining the CTE!!

T-SQL Tuesday #77: Recursive Common Table Expression (CTE)

Andy Mallon (b|t)

Andy’s blog post is a challenge to the topic. A challenge which I think is utterly justified. Andy advocates to take a step back. Look at the bigger picture. Take a moment of thought, before you start hammering away with your new found hammer.
Key Take-Away: “…configuring a multi-subnet WSFC, with an AG that spans two FCIs, then take backups from the readable secondary to use for cross-domain log shipping to a third data center.” (who doesn’t eat that for Breakfast?)

T-SQL Tuesday #77 – My Favorite SQL Server Feature

Andy Yun (b|l|t)

Andy offers up Dynamic Management View/Dynamic Management Functions (DMV/DMF) as his favorite SQL Server feature. The blog post is a quick introduction to DMV’s/DMF’s and makes a great starting point for anyone who haven’t yet been using those. Andy also shows a couple of pointers, to what DMV’s/DMF’s has to offer. The information is practically endless…
Key Take-Away: “…DMVs/DMFs are the gateway into SQL Server!

T-SQL Tuesday #77: My Favorite SQL Server Feature

Jason Brimhall (b|l|t)

Jason has written an awesome post about the almost unmentionable feature. In the end however, he caves in and spills the whole deal. Unintentionally, I think, there are several take-away’s from Jason’s blog post – both the feature (which I will not dare utter), but also Synonyms – If nothing else, Jason demonstrates and example to follow, in the sense that the customer gets a perfectly working solution at minimum cost (even though an upgrade would have been way more cool to work with etc…)
Key Take-Away: “Using a synonym, I can extend this database beyond the 10GB limitation…”

Awesome SQL Server Feature

Kennie Nybo Pontoppidan (b|l|t)

Kennie offers another shot at CTE’s; This one also recursive.
Key Take-Away: “…we continually join the data set to the result set as it exists for each “loop”

T-SQL Tuesday #76 – Parsing code hierachies with CTEs

Edwin M Sarmiento (b|l|t)

Via a Dell Laptop, Edwin steers us through a good number of uses of SQL Server Logging. To anyone who thinks logging is just logging, have a look at the uses in Edwin blog post.
Key Take-Away:”How To Forecast Database Disk Capacity If You Don’t Have A Monitoring Tool

TSQL Tuesday #77 – Favorite SQL Server Feature: Logging

John Sterret (b|l|t)

John talks about Transactional Replication for Azure SQL Databases and offers three main reasons why this is an ûber cool feature. I have to agree, that this is a really cool feature and John does a good job explaining why. Looking forward to the follow-up post containing the How To.
Key Take-Away: “The reporting subscriber database can be scaled up or down as needed based on your needs…

Replication for Azure Databases – #TSQL2SDAY #77

Derik Hammer (b|t)

With the End of Support for SQL Server 2005, Derik finds it appropriate to pay his respects by picking Database Mirroring as his favorite feature. The feature will be replaced by Basic Availability Groups in SQL Server 2016.
Key Take-Away: “…mirroring fails over even faster than Availability Groups.

Favorite SQL Server Feature – #TSQL2SDAY #77

Kenneth Fisher (b|l|t)

In this post Kenneth walks us through the different applications of Delayed Durability in SQL Server. Advice on Simple and Full Recovery Mode as well as HA scenarios are covered. Delayed Durability is a new feature to SQL Server 2014 and applies to Azure SQL Database as well as the upcoming SQL Server 2016 release.
Key Take-Aways: “The fact that this should be utilized for massive Data Warehouse loads, where durability is not of concern.”

The Risk of Delayed Durability

I was really impressed by the hard work put into this round of #TSQL2SDAY, so thank you, to all the contributors. Great job all!

Posted in Community, Programming | Tagged , , | Leave a comment

Do You Speak Azurian?

A Day in the Life of a BI Dude:

SqlNexus Azure from Jens Vestergaard on Vimeo.

Posted in Programming | Tagged , , | Leave a comment

Invitation to #TSQL2SDAY #77 – Favorite SQL Server Feature

tsql2sdayThe Why

If you’re not familiar, T-SQL Tuesday is a blogging party hosted by a different person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for ages now! Basically the host selects a topic, defines the rules (those are almost always the same), and then everyone else blogs about it. Once the deadline is reached, the host summarize each of the submitted posts on their site/blog.

The What

This month I get to pick the topic, and I am going to go with:

My favorite SQL Server Feature

This can be anything from Reporting Services as a report creating tool, down to the Columnstore Indexes. Anything goes!
I selected this topic precisely to illustrate the breadth and depth of what SQL Server has evolved into over the last decade+. Starting out as a RDBMS (w/ DTS), adding Analysis Services, then Reporting Services, following a myriad of auxiliary services and tools. So please, share your story about a specific SQL Server feature close to your heart. So please, share precisely what you think makes SQL Server such an awesome tool.

The How

There’s only a few rules for T-SQL Tuesday:

  • Your post must be published between 00:00 GMT Tuesday April 12th 2016 and 00:00 GMT Wednesday April 13th 2016.
  • Your post must contain the T-SQL Tuesday logo (see above) at the top and the image must link back to this blog post.
  • Trackbacks should work, but if they don’t, please put a link to your post in the comments section so I (and everyone else) can see your contribution!
  • Tweet about your post using the #tsql2sday hashtag

There’s also a few optional you can do that might help:

  • Include “T-SQL Tuesday #77” in your blog post’s title.
  • Contact Adam Machanic (b|l|t) and tell him you’d like to host a T-SQL Tuesday from your blog
Posted in Community, Programming | Tagged , | 18 Comments

New EU Legislation potentially cripples #SQLServer2016

Today the Microsoft SQL Server Team took a huge blow, due to new EU legislation passed in the EU Commision. The kick-off of all this, is to be found in new features implemented in the not yet released version of SQL Server – Microsoft expects to release later this year.

At the #DataDriven event, on March 10th, Scott Guthrie (b|l|t) announced that SQL Server 2016 has “Everything built in”

This bold statement was apparently too much for an undisclosed competitor, so they filed a complaint with the EU Commision.

The claim to the EU Commision, is that Microsoft SQL Server is simply too hard to compete with – a leading spokesman from the claimant is to be quoted:

We simply cannot keep up with Microsoft anymore. It’s just not fair, that they have all the cool stuff. Plus, Reporting Services now looks like something build in this century.

The formal complaint filed to the EU Commision can be seen here:

On the basis of information received from end-users, SMEs active in the IT (information technology) sector and competitors of Microsoft, the Competition Directorate General of the European Commission has formally requested Microsoft to provide information about the new technical features of SQL Server 2016 in the context of EC competition law. This information should allow the Commission to verify allegations that Microsoft has designed SQL Server 2016 in a way which will dominate the market for Relational Database Systems and related offerings.

reference: here

Furthermore, the claimant has suggested the following restrictions on SQL Server, in order to level the playing field.

And finally the one that will really hamper SQL Server

The final one means that any query fired against SQL Server MUST be returned in less than 21 seconds. If not, the operation will time out and the results also. Outrageous, don’t you think!?

Posted in Programming | Leave a comment

SSAS Compare by Red-Gate

Som years back Red-Gate put out a free tool to compare SQL Server Analysis Services (SSAS) Databases. The tool is no longer updated, but still has some very useful features.

The Tool

The official download is available on this page at the Red-Gate site.

Quick Tour

With SSAS Compare, you can select SSAS databases to compare differences between cubes, dimensions, measures and other objects. You can select to compare XMLA, Project File or a live Database.

Running the application will automatically display the pop up New Comparison – if not, click the button New Comparison – Top Left Corner.

SSAS Compare New Comparison

Example – Compare Database to Database:

SSAS Compare Database to DatabaseExample – Compare Project File to Database

SSAS Compare File to Database

Example – Compare Project File to Project File

SSAS Compare File to File

Running the comparison will result in an update in the initial application – the pop up will close, and below you can see an example of a comparison of a Project File with a Live Database.

SSAS Compare File to Database OutputI have highlighted a Dimension, which the tool is displaying in bold to indicate a difference. As we can see, clearly the Attributes Comparison Weeks Order, Comparison Months Order, Weeks Order and Months Order are missing in the Live cube.

By clicking Deploy and checking the Include Processing, you can then merge the changes from source to destination – that being Project File, XMLA or Live Database.

The Downside

Obviously the biggest downside is that Red-Gate no longer updates the tool. This also means that some of the short comings will never be available, like you cannot use the application to generate a new Live Database from an existing one. The initial deploy needs to be done outside the tool. Likewise you cannot grab a Live Database and convert it to a new Project File or XMLA. For the latter, see my blog post on how to do that using PowerShell.

Being aware of the limited functionality the tool is still very useful in many scenarios – it’s part of my tool-pack for SSAS.

The complete documentation can be found here.

Posted in Community, Entry Level, Programming | Tagged , , , | Leave a comment