Press "Enter" to skip to content

Curated SQL Posts

SQL Server Marketplace Images

Published 2016-04-05 by Kevin Feasel

Melissa Coates debates whether to use a SQL Server marketplace image for an Azure VM:

As more and more customers are interested in moving some portion of their BI/analytics workloads to cloud services, one question that comes up occasionally is whether or not you should start with a marketplace image that has SQL Server already installed. So far I’ve noted a few key considerations for this decision:

  1. Do you want to pay for the SQL Server license as part of the VM pricing?

  2. Do you want to configure SQL Server in a specific way (i.e., following best practices)?

  3. Do you want Azure to handle things like automated patching by default?

My rule of thumb is if it’s Express Edition or just for me to mess around with, I’m typically happy with an image.  But for a production setup, I’d want the fine-grained control at installation time that you just won’t get.

Comments closed

File Processing Pattern

Published 2016-04-05 by Kevin Feasel

Bill Fellows describes a pattern for processing files in an ETL scenario:

All ETL processing will use a common root node/directory. I call it SSISData to make it fairly obvious what is in there but call it as you will. On my dev machine, this is usually sitting right off C:. On servers though, oh hell no! The C drive is reserved for the OS. Instead, I work with the DBAs to determine where this will actually be located. Heck, they could make it a UNC path and my processing won’t care because I ensure that root location is an externally configurable “thing.” Whatever you’re using for ETL, I’m certain it will support the concept of configuration. Make sure the base node is configurable.

A nice thing about anchoring all your file processing to a head location is that if you are at an organization that is judicious with handing out file permissions, you don’t have to create a permission request for each source of data. Get your security team to sign off on the ETL process having full control to a directory structure starting here. The number of SSIS related permissions issues I answer on StackOverflow is silly.

It comes down to consistency and cleanliness.  Plan ahead and you’ll have a much nicer go of things.  Bill also provides a Biml POC, so check that out as well.

Comments closed

Drop Column In SQL Server 2012/2014

Published 2016-04-04 by Kevin Feasel

Aaron Bertrand explains a bug in SQL Server 2012 and 2014 which can crop up after dropping a column:

A fix has been published for SQL Server 2012 (SP2 and SP3), and one is coming for SQL Server 2014 (RTM and SP1) – though in this most recent update, SQL Server 2014 was removed from the list of affected versions (not sure it was accurate to remove those). I will update this space once the next 2014 CUs are released, but for now you should plan to rebuild tables after dropping columns as a matter of course and, more importantly, as part of the same maintenance operation.

This is a good reason to stay up to date on CUs.

Comments closed

Jupyter Notebooks With R

Published 2016-04-04 by Kevin Feasel

Andrie de Vries notes that Azure Machine Learning now supports Jupyter Notebooks with R:

I wrote about Jupyter Notebooks in September 2015 (Using R with Jupyter Notebooks), where I noted some of the great benefits of using notebooks:

  • Jupyter is an easy to use and convenient way of mixing code and text in the same document.

  • Unlike other reporting systems like RMarkdown and LaTex, Jupyter notebooks are interactive – you can run the code snippets directly in the document

  • This makes it easy to share and publish code samples as well as reports.

Jupyter Notebooks is a fine application, but up until now, you could only integrate it with Azure Machine Learning if you were writing Python code.  This move is a big step forward for Azure ML.

Comments closed

Transitioning To Extended Events

Published 2016-04-04 by Kevin Feasel

Dave Mason shares his story of moving from Profiler traces to Extended Events:

Thinking back, my initial reaction was probably something like “I don’t get it.”. XEvents just seemed like a new dog performing an old trick. Even though I concluded most of the SQL pros had already made the transition to Extended Events (XEvents), and that I’d be living in the past if I didn’t do the same, I continued to use Profiler. It’s too bad–my current employer at the time had SQL 2012 across the board. I missed a great opportunity there. Before I left that job, I stumbled upon this Paul Randal post. It helped me trouble-shoot a performance issue with a stored proc. “Ok, so there’s one thing XEvents can do that Profiler can’t do” I thought to myself. (Note: I don’t know if that’s a true sentiment or not–I never investigated further.) I also recall reading at least one of Jonathon Kehayias’ (b|t) posts about XEvents, but I don’t remember which one. Profiler would remain my go-to tool for the remainder of my time with that employer.

Read this in conjunction with Erin’s request.

Comments closed

Why Do You Avoid Extended Events?

Published 2016-04-04 by Kevin Feasel

Erin Stellato wants to know why you’re still using Profiler over Extended Events:

Extended Events *is* the replacement for Profiler/Trace; it’s not going away.  I really want people to be prepared for the time when Profiler and Trace are removed from the product.  And I want to provide feedback to the SQL Server product team to address limitations that people find in Extended Events.  If the feature is lacking something, we need to work together to create change.

Thanks in advance for your help, and if you haven’t tried XE, or are looking for a refresher, then please attend my webinar next Tuesday, April 5th at 12PM EDT: Kicking and Screaming: Replacing Profiler with Extended Events.  I’d love to see you there and can help get you started with XE!

Leave a comment on Erin’s post and let her know.

Comments closed

SQL Server 2016 RC2

Published 2016-04-04 by Kevin Feasel

The SQL Server Team notes that Release Candidate 2 is now available:

In SQL Server 2016 RC 2, enhancements include:

  • R Services setup – the setup process for R Services is much more integrated into SQL Server setup. There is no longer a need to manually download and install Microsoft R open and R Server if the SQL Server is connected to the Internet; it becomes part of the SQL Server install sequence.

  • SQL Server Management Studio (SSMS) – This release of SSMS features an update to the Visual Studio 2015 shell bringing enhancements such as the quick launch toolbar and improved theming support.

  • Mobile reports – Brand Packages will now be downloaded to the mobile report publisher from a server running RC2 and available for use in report creation.  Basic mobile report content migration between servers is now supported.

These look like wrap-up tasks.  It’s good to see R being integrated a little bit better; that installation series seemed a bit hacky, whereas this sounds a lot more polished.

Comments closed

SQLPS Fixed

Published 2016-04-04 by Kevin Feasel

Chrissy LeMaire and Aaron Nelson have succeeded; Microsoft has fixed the three issues with SQLPS:

In my previous blog post “Can We Get These 3 SQLPS Issues Fixed before SQL Server 2016 RTMs?“, Aaron Nelson and I asked the SQL and PowerShell community to help upvote 3 SQL Connect items. The items addressed three problems with SQL Server’s PowerShell module, SQLPS.

  • It took 3-5 seconds to load
  • It changed the present working directory when loaded
  • It produced approved verb warnings when loaded

Today, Microsoft responded, letting everyone know that the issues were addressed in SQL Server Management Studio March 2016 Refresh.

Given the reputation Connect has in the community, I’m glad to see these issues get fixed.

Comments closed

Power BI Doesn’t Replace Warehouses

Published 2016-04-01 by Kevin Feasel

Jesse Seymour argues that Power BI won’t replace traditional data warehouses:

Pesonally, I am still struggling to see where PowerBI fits in my organization.  I am the only BI professional here, so I have to do every bit of the process from data modeling to building front end applications.  Right now, my organization has a data warehouse with some processes in the warehouse, a Datazen environment and an SSRS environment.  There is no SSAS cubes or any power users using PowerPivot to analyze data.

Data warehouses serve a particular role in an environment:  they answer known business questions and give consistent answers across an organization.  I see Power BI as a tool with a few separate uses depending upon organizational size and maturity.  I think its best use in shops which are not large enough, well-established enough, or with enough non-IT business intelligence expertise is BI developers building beautiful dashboards for business data consumers, feeding from existing systems (including data warehouses).  In that sense, it is a complement to a Kimball-style data warehouse.

Comments closed

Developer Edition Now Free

Published 2016-04-01 by Kevin Feasel

Microsoft is making SQL Server 2014 Developer Edition free for Visual Studio Dev Essentials members:

Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members. 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.

SQL Server 2016 will also be covered under this plan.  Granted, Developer Edition would not break the bank anyhow, but it does lower (ever so slightly) those barriers to entry, and I think it’ll be a driving point for SQL Server on Linux.

Comments closed