Press "Enter" to skip to content

Author: Kevin Feasel

Considerations For Azure SQL Database

Published 2016-08-10 by Kevin Feasel

Grant Fritchey discusses whether new database administrators might want to start with Azure SQL Database rather than on-premises SQL Server:

Since you are right at the start of your career, you may as well plan on maximizing the life of the knowledge and skills you’re building. By this, I mean spend your time learning the newest and most advanced software rather than the old approach. Is there still work for people who only know SQL Server 2000? Sure. However, if you’re looking at the future, I strongly advocate for going with online, cloud-based systems. This is because, more and more, you’re going to be working with online, connected, applications. If the app is in the cloud, so should the data be. Azure and the technologies within it are absolutely the cutting edge today. Spending your limited learning time on this technology is an investment in your future.

This answer is a tougher call for me.  Looking at new database developers (or development DBAs or database engineers or whatever…), I think the case is pretty solid:  there’s so much skill overlap that it’s relatively easy to move from Azure SQL Database to on-prem.  With production DBAs, the story’s a little different:  as Grant mentions, this is a Platform as a Service technology, and so the management interface is going to be different.  There are quite a few commonalities (common DMVs, some common functionality), but Grant gives a good example of something which is quite different between the PaaS offering and the on-prem offering:  database backup and restoration.  I think the amount of skills transfer is lower, and so the question becomes whether the marginal value of learning PaaS before IaaS/on-prem is high enough.  Given my (likely biased) discussions of Azure SQL Database implementations at companies, I’d stick with learning on-prem first because you’re much more likely to find a company with an on-prem SQL Server installation than an Azure SQL Database.

Comments closed

Filtering Data

Published 2016-08-10 by Kevin Feasel

Slava Murygin shows various ways to filter data, in particular data from fn_dblog():

Would say we need to extract an information associated with an “UPDATE” for LSNs started at “0000004f:00000087:0001”. You can just specify Starting and Ending LSNs as “fn_dblog” parameters:

[…]

That portion of code would return you ONLY Log records between LSNs “0000004f:00000087:0001″ and “0000004f:00000088:0001″.

Slava’s post uses fn_dblog() as an example but the techniques are applicable across the board, and in practice sum up to “get the fewest number of rows and fewest number of columns you need to solve the problem at hand.”

Comments closed

Identifying Object Names Using fn_dblog()

Published 2016-08-10 by Kevin Feasel

Frank Gill digs into the transaction log to find object names used in CREATE and ALTER statements:

This is the object id of the view that was created.  So, Jes’s question was answered.  But this led me to one of my other favorite SQL Server topics: string manipulation.  The following script will identify all transactions for a particular Transaction Name and return the object name affected.  The comments provide additional information about the functionality.

Click through to check out Frank’s script.

Comments closed

Joins Versus NOT IN Clause

Published 2016-08-10 by Kevin Feasel

Kevin Hill explains a potential performance difference between using NOT IN and using a left join:

Basic stuff, right?   Both will return 951 records (books) that I do not own.  And, very quickly…because the tables are tiny.   Sub-1 second is fast.

The issue here is HOW the rows are compared.

English version now, techy stuff later:

In the first query, this is equivalent to you standing at the bookstore and calling home to have someone check to see if the book in your hand is already in your collection.  EVERY time.  One by one.

In the second, you got really smart and brought a list with you, which you are comparing to the books on the shelf at the store.   You’ve got both “lists” in one place, so it is far more efficient.

Even in the case with a few hundred records, you can see why there’d be a performance difference.

Comments closed

Power BI Embedded

Published 2016-08-09 by Kevin Feasel

Reza Rad looks at Power BI Embedded:

Power BI Embedded is the reporting and analysis solution for mobile and web applications. Power BI Embedded is an Azure service that integrates Power BI solution into mobile and web applications. The report still has to be authored and created in Power BI Desktop. After creating the report it can be published into Power BI workspace in Azure, and using API Keys of Power BI workspace and embedding Power BI report frame into the web/mobile application it will be integrated into the application.

Reza walks through the process step-by-step.  The upshot is that you can take this report you created in Power BI and embed it into your own application, where you can apply your own in-app access controls.  There are limitations, which Reza spells out at the end of the post, so check it out.

Comments closed

Compression Delay

Published 2016-08-09 by Kevin Feasel

Rob Farley digs into Compression Delay as part of real-time operational analytics:

The thing with Operational Analytics is that the analytical data, reporting data, warehouse-style data, is essentially the same data as the transactional data. Now, it doesn’t look quite the same, because it’s not been turned into a star-schema, or have slowly changing dimension considerations, but for the purposes of seeing what’s going on, it’s data that’s capable of handling aggregations over large amounts of data. It’s columnstore.

Now, columnstore data isn’t particularly suited to transactional data. Finding an individual row within columnstore data can be tricky, and it’s much more suited to rowstore. So when data is being manipulated quite a lot, it’s not necessarily that good to be using columnstore. Rowstore is simply better for this.

But with SQL 2016, we get updateable non-clustered columnstore indexes. Data which is a copy of the underlying table (non-clustered data is a copy – clustered data or heap data is the underlying table). This alone presents a useful opportunity, as we can be maintaining a columnstore copy of the data for analytics, while handling individual row updates in the rowstore.

Read the whole thing.

Comments closed

Pitfalls Of DIY Hadoop

Published 2016-08-09 by Kevin Feasel

Ben Davis discusses considerations when rolling your own Hadoop cluster:

5. Security hardening
I find it is easier to deploy Hadoop in a fairly low security configuration. This is because there are a range of ports that Hadoop talks on and having an incorrectly configured firewall can cause you problems. So after deployment, set aside time to identify how to customise your firewalls, user and group settings, Kerberos and ssl settings.

I think the article makes some good points.  DIY is great for a proof of concept or for playing around with a technology, but if you don’t already have a good amount of experience with a technology, you’ll probably make costly mistakes in development and administration.  This is not Hadoop-specific:  I’ve seen companies do terrible things to SQL Server because they didn’t know the correct way to do it but needed to get work done.  As part of a proof of concept, do all the terrible things you’d like; they’re how you’ll learn.  But if this is going to production, it’s a good idea to have people who know what they’re doing involved.

Comments closed

Credentials In Azure Automation

Published 2016-08-09 by Kevin Feasel

Mark Vaillancourt explains Azure automation credentials:

With that String parameters, it is easy to just type what I want in the textbox. But, for the PSCredential parameter at the bottom, I was unsure what to do. I figured, since this is just a textbox, I couldn’t just magically pass in a Credential Asset. So, I tried passing in an expression that used the Get-AzureRmAutomationCredential cmdlet that returns the Credential Asset specified. That didn’t work very well. I then started digging through documentation, figuring I would see references and examples of passing a Credential Asset in the Test Pane. It took me a bit, but I finally landed on an Azure.com blog post titled Azure Automation: Runbook Input, Output, and Nested Runbooks. While this post didn’t match all that closely with the search criteria around the Test Pane, it did contain my answer in a section on starting Runbooks:

The answer turns out to be pretty simple.

Comments closed

Quality Graphics With R

Published 2016-08-09 by Kevin Feasel

David Smith discusses building high-quality visuals with R:

Note the use of an attractive colour pallette, style-compatible fonts, and even the official Olympic icons for the sports. I just took a screenshot here, but if you click through to the actual site you’ll notice that these graphics are also scale-independent (you can zoom in on your browser and they’ll look better, not worse) and even interactive (pop-ups appear with country-specific data when you hover over a bar).

Duc-Quang has been generous enough to provide the R code behind these charts if you’d like to try your hand at something similar. The data themselves were scraped from the official Rio 2016 site. The bar charts were created using a standard geom_bar plot using ggplot2, with a custom theme to set the font to OpenSans Condensed. The interactive elements were added using the ggiraph package and the geom_bar_interactive function. The chart titles (including the icons) were created as HTML headers directly, which was then exported along with the interactive charts using the save_html function.

I’m impressed that this all comes from R.  There’s a good bit of work involved in getting this going, but you can get professional-grade graphics quality with R, and that’s pretty cool.

Comments closed

Issues With SSISDB In An Availability Group

Published 2016-08-09 by Kevin Feasel

Andrea Allred has some lessons learned from a troublesome service pack upgrade:

Here are a few of the fun errors that we saw.

“Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 942, state 4, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.”

There are some good lessons here.

Comments closed