You may have heard that R and the big-data RevoScaleR package have been integrated with with SQL Server 2016 as SQL Server R Services. If you’ve been wanting to try out R with SQL Server but haven’t been sure where to start, a new MSDN tutorial will take you through all the steps of creating a predictive model: from obtaining data for analysis, to building a statistical model, to creating a stored prodedure to make predictions from the model. To work through the tutorial, you’ll need a suitable Windows server on which to install the SQL Server 2016 Community Technology Preview, and make sure you have SQL Server R Services installed. You’ll also need a separate Windows machine (say a desktop or laptop) where you’ll install Revolution R Open and Revolution R Enterprise. Most of the computations will be happening in SQL Server, though, so this “data science client machine” doesn’t need to be as powerful.
The tutorial is made up of five lessons, which together should take you about 90 minutes to run though. If you run into problems, each lesson includes troubleshooting tips at the end.
SQL Server R Services has the potential to be a great tool. The standard V1 warning obviously applies, but I’m excited.
I’m not going to say whether I think Auto Update Statistics should be on or off. Instead, I’m going to argue that there are definitely scenarios when you want to have this on AND there are scenarios where you want it turned off. Can you really have this both ways? Absolutely.
I’m going to say yes to auto-update unless you know the answer is no for an object. But it’s nice to know that the fine-grained option is available.
Our financials are the logical first place to start. And our financials are in the hands of our accounting firm. Specifically, they are stored in Quickbooks.
This, of course, poses a problem. Because like ALL accounting and ERP systems, Quickbooks is primarily focused on being a great accounting system. A system that collects, stores, organizes, and routes data. Quickbooks is NOT an analytics tool.
And being an analytics (or BI or reporting, whatever you call it) tool is a full-time job. ANY system whose job it is to collect/organize/route data will NEVER be sufficient for reporting and analysis. NEVER. I’m not kidding. We should never expect different, and that’s not a “knock” on these vendors. It’s just too many missions for any one company to execute.
This is a nice walkthrough of how you can apply visualization and analytics concepts, especially in a small business scenario.
Now that we have our PS1 file that will send the email alert, but we need to have the job run when the service fails. To set this open up services and right click on the SQLServerAgent service you wish to add the failure alert to and select properties. Navigate to the Recovery tab and set one of the failure actions to “Run a Program” I tend to choose it as the first failure action as I like to look at why my service failed before I just restart it. You can attempt to restart first and then run the program on if it fails again by setting the second failure to run a program. The choice is yours!
Despite the development of AlwaysOn in recent releases of SQL Server, log shipping is still a great way to set up a copy of databases to be used for reporting. One of the main reasons it is great is because, unlike AlwaysOn, it is available in less expensive editions like Standard and Web from SQL Server 2008 onwards. Sure, in 2016 AlwaysOn will be available in Standard, but in a greatly deprecated form, and you cannot read from the secondary. So it will be good for DR, but not for reporting (as an aside it still might be easier to set up log shipping for DR than AlwaysOn Basic because you need to setup a failover cluster. Read through the “how to set up Standard Edition Availability Groups” here.) However you do need to be careful though when setting up log shipping across different editions of SQL Server: whilst you can log ship between Enterprise to Standard/Web, if the database uses any Enterprise features then you’ll need to log ship to an Enterprise edition of SQL Server. And because you’ll be using the database for reporting, you’ll need to get it licensed.
Log shipping is a venerable disaster recovery technique and it behooves database administrators to know of its existence.
The random sample that TABLESAMPLE provides is based on the number of data pages, not the number of records. If you want the number of rows to be specifically limited you will need to use Top(n) as well. I’ve written all my samples based upon AdventureWorksDW so you can run them for yourself later. I’ve listed the variety of ways to call TABLESAMPLE and shown the number of records returned.
TABLESAMPLE is useful for spelunking, but is somewhat limited otherwise.
Before we jump right into enabling either row or page compression, we can actually estimate the savings of each to determine which will provide us with the most savings on storage. Since page compression includes row compression, we will start with row compression and the estimated savings.
In practice, data compression is extremely valuable and in most circumstances, the benefits outweigh the costs. In certain workloads, you might even see CPU usage go down.
The error was thrown by the ROLLBACK statement. As such, the transaction is still open, the locks are held and the transaction log space can’t be reused. Unless the application that called this was checking for open transactions, that transaction could potentially be left open for quite some amount of time, causing blocking and/or the transaction log to grow.
It’s not just that someone in the future might call the code from another stored proc within a transaction, it’s also that it might be that the code is called from an application which started a transaction. Or called from SSIS which started a transaction. It’s very hard to ensure that code is never called from within an existing transaction
Read the whole thing.
How do I get it?
i have now pushed it to the visual studio gallery and will use this for all future updates so feel free to grab it from:
Also check out T-SQL Clippy.
Looking back at the previous blog post, changing the the startup parameters through the SMO is pretty easy with the ManagedComputer class. In some ways, it is too easy. As Shawn calls out, you could easily overwrite the full string and remove the startup locations for your master database (and breaking your instance). This is where tool building can be such an aid, because by wrapping the change code in a function, we can build some safety mechanisms to protect us (or others) from doing harm when trying to make this sort of change. The function I wrote is not terribly long, but I’ll spare you the whole thing by letting you view it on GitHub. We’ll use our time better by going over how I constructed it while focusing on some of my tool building principles.
Thanks to Mike for making that available to the community.