A Fine Slice Of SQL Server
James Serra enumerates high availability options for SQL Server Analysis Services:
If you are looking at providing high availability (HA) for SSAS, here are 3 options:
Install SSAS on a Windows Server Failover Cluster (WSFC)Here’s a good article. The main issue with this option is that SSAS isn’t cluster-aware, so if windows is “OK” but SSAS (the service) is hung, it won’t failover
Read on for the other options.
Comments closedAndy Levy has a T-SQL programming breakthrough:
Finally, this week I had a breakthrough. I was working on updating a bunch of data but it was breaking on a small subset of that data. In this case, I was attempting to
JOINtwo tables on fields that should have beenINTs, but in a very small number of cases one side was using a comma-delimited string. The user told me that someone else had done these updates in the past and didn’t encounter the problem I was having (so I knew that it was something i was doing “wrong”), but given that it was only a handful of broken updates she was OK with manually doing the updates (we were scripting it because we were updating potentially tens of thousands of records).I am not OK with manually fixing this in the future. I wanted to know how the other DBA had done it before. I dug into some history and found
CROSS APPLY. My nemesis. I was determined to figure out how to use it this time.
The APPLY operator is extremely powerful in the right set of circumstances. Andy shows the “classic” use case, but there are a number of other uses for the operator.
Comments closedStandard Edition
You wants 1×6 (one socket, 6 cores) because standard edition will only use the first 4 sockets in a server (up to 16 cores combined). There’s no getting around that.
From a NUMA perspective as long a vNUMA at the Hypervisor is disabled then it doesn’t matter as SQL Server standard edition isn’t NUMA aware (NUMA awareness is an Enterprise Edition feature).
Read on for a more nuanced answer when it comes to Enterprise Edition.
Comments closedDavid Alcock looks at a new Python-based command line tool, mssql-scripter:
Earlier today two new command line tools were announced for SQL Server, one an experimental Linux tools DBFS which enables access to live DMVs without using a UI like SSMS and secondly a tool that enables script generation of objects within SQL rather like the Generate SQL Scripts option in SSMS.
In this post I’m going to run through the installation of the script generator tool and provide a very quick demo. The reason I’m going through this is because in order to install the tool we need to use something called PIP. PIP is a package management system that enables us install and use packages written in Python. Yeah, Python again!
I’m pretty interested in DBFS, as it seems well-placed to make crusty Linux sysadmins happier with SQL Server, and that’s a big positive in my book.
Comments closedI prefer to passively gather performance metrics – even if it’s a little convoluted and more work (for me). I don’t often need these metrics immediately available, so I execute queries similar to the query below and store the performance metrics in a table. I can (and do) build dashboards to track SSIS performance (perhaps I should blog about them…) using this passively-collected performance metrics.
As with Lookup Transformation messages, OLE DB Destinations in Data Flow Tasks record specific messages using the same format every time. Because of this (hard-coded) consistency, you and I can passively collect the number of rows written while executing packages in the SSIS Catalog using the (default) Basic logging level. We can use the following Transact-SQL query to collect this execution metadata post-execution:
Click through for the script.
Comments closedKendra Little has a video showing how to use plan guides:
If you need to add, remove, or replace hints from ad-hoc queries where you can’t change the code, plan guides can help. See a demo of removing a query hint from parameterized TSQL run from an application, and get tips on how to make your plan guides work in SQL Server.
The code from the demo is here. Links for more info are below the video. Have fun!
Click through to watch the video, or you can catch the podcast version.
Comments closedSomehow I have become the R DBA at my job which I don’t mind, I plan on taking Microsoft’s Professional Program on Data Science to be familiar with it. But recently I’ve had to upload files to our R servers which the first time wasn’t too bad. Copy these files to six different servers but come the second time around it became apparent that the Predictive Analytics Manger was going to be asking me to do this more frequently than I wanted to to it manually. So I wrote a quick PowerShell function to take care of this added to our module we use in house. It unzips the file provided to the correct location. It does assume you have administrative rights to your server i.e. you can use the admin shares (c$) for example on the server. You will need to get the function Get-CMSHost from my Running SQL Scripts Against Multiple Servers Using PowerShell post to run the code below.
Click through for the script. This is particularly useful for deploying in-house packages and you don’t want to set up a miniCRAN.
Comments closedAdam Saxton has a video showing how to install and configure Power BI Report Server:
In this video, I look at how to install and configure the May 2017 Preview of Power BI Report Server. Power BI Report Server has a new standalone install experience and this product allows for Power BI reports to be rendered in the web portal along with paginated reports.
This will get you started with the new version.
I was really excited about this preview until I realized that, for now, it only works for Analysis Services data sources.
Comments closedArun Sirpal shows how to set up Azure SQL Database to use Azure Active Directory accounts:
I think it is important to highlight a couple of points, more specifically around the requirement of ADALSQL.DLL and proper setup of AD which I will highlight below and reference some links, please do this as it lays the foundation for you.
ADALSQL.DLL
You need ADALSQL.DLL which is part of the latest SQL Server Management Studio (SSMS) to test access. This stands for Active Directory Authentication Library for SQL Server.
This goes through some of the issues Arun had setting everything up and provides workarounds and explanations.
Comments closedJohn Morehouse talks about a mistake he made:
The other day I had to update some records, in Production. I’m a firm believer of using explicit transactions and double checking things before committing a transaction. This helps ensure things go as expected. This also allows me a way to rollback the changes if they don’t. It happens.
However, this means that I have to COMMIT said explicit transaction. And not go to lunch without doing so.
Can you see my mistake? I bet you can.
Fortunately, it sounds like it wasn’t a critical problem. If you want to check for open transactions, Jack Vamvas has a couple methods.
Comments closed