Voice of the DBA

Tag: sql server

  • Where Do You Run Your R Scripts?

    I know most of you don’t work with the R language. In fact, plenty of you might not know anything about R other than a cursory understand of this as some sort of data analysis language. If you want to know more, here’s what the R Project is.

    Microsoft wants you to use R Services in SQL Server, or the R Server product available as a standalone system. However, I saw someone ask the question why would someone run their R scripts inside SQL Server, because these are expensive CPU cycles to burn on analysis. Someone else noted that Microsoft loves your licensing dollars, so their push to use R Services is perhaps a little self serving.

    Push the intellignce to the data makes sense. Isn’t that what we do with large data warehousing queries or SSAS cubes? We’re trying to get the analysis done at scale without having to move the data elsewhere, especially considering we’ve (usually) already moved the data in some sort of ETL (or ELT) process. Gaining insights from our ever increasing scales of data requires some computational cycles somewhere.

    What’s the alternative? Large queries that pull data to some client? I think that’s fine, and that might be a better alternative since simple queries to pull data don’t burn as many CPU cycles as those that might perform analysis. I certainly understand that the licensed CPU cycles for a SQL Server instance are expensive, and we want to be careful how they are used. Adding complex R scripts might not be the best use of our licensing dollars. On the other hand, if I can perform analysis quicker, that is more useful, than perhaps I can eliminate other random queries analysts want to run on my database?

    Ultimately I think that R Services make some sense in SQL Server, but not as some experiment. I would suggest that the R client is the way to experiment, preferably on a copy of data that allows someone to build scripts and determine if there is insight to be gained from a particular set of data. Build a Proof of Concept (POC), and only deploy it to a SQL Server if you find it provides value.

    And if you do so, continue to experiment. That R script you run today might not be as useful in six months as your application, database, and business evolve. Data analysis isn’t a set-it-and-forget-it, but rather an ongoing, iterative process.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Who’s Touching My Database

    Abstract:

    As databases become more critical to the operation of our organizations, we are being asked to audit and report on access to both data and the configuration of our systems. SQL Server has a number of features that can help you comprehensively audit your system, including SQL Audit, Extended Events and the default trace. Come learn how you can configure and incorporate detailed auditing into your reporting to management.

    Level: 200 – Should have some basic knowledge of SQL Server, database security, and T-SQL.

    Demos:

    • Using Logon Triggers
    • Looking at the default trace
    • Using SQL Audit to track logins
    • Using Extended Events to track logins
    • Checking permissions at the server and database level
    • Watching database activity using DML triggers
    • Watching database activity using SQL Audit
    • Watching database activity using Extended Events

    Downloads:

  • Developer Deployment Frustrations

    This editorial was originally published on Sept 7, 2012. It is being re-published as Steve is at DevConnections.

    Why don’t developers like SQL Server? Probably a few reasons, but I’m sure this is one that really frustrates them. I found a Connect Item that was titled:  Why is Deploying SQL Server 2008 R2 sooooo FRUSTRATING?!!  There really is a question there, asking for guidance on  which versions of SQL Server are available and recommended for developers to include in their applications.

    When SQL Server MSDE was released, it seemed that Microsoft was looking for it to be included in small applications that might then be upsized to a Standard or Enterprise edition of SQL Server. It seems to me that this is really the market for Express (the evolution of MSDE) and that it ought to be simple for a developer to not only deploy this with their application, but also setup basic maintenance easily.

    I sometimes think that the software developers at Microsoft get lost in their own specialty and forget just how frustrating it can be for the rest of us trying to use their product in new ways. They forget that many of us want to deploy simple solutions easily, and not spend a lot of time working out the nuances of software setup.

    I’d like to see Express not only have a very simple setup that works across multiple versions of Visual Studio, but also baic maintenance plans built in that allow full and log backups (if needed), along with index rebuilds with a simple switch set as a part of setup. A few registry keys or XML config changes could set paths or frequencies.

    Making life simpler for developers is a worthwhile investment for the SQL Server team. It makes them more likely to include it in their applications. If you can add a one-switch replication to sync to a Standard or Enterprise SQL Server, they might think Express is required in every application.

    Steve Jones

     

  • T-SQL Tuesday #83–The Same Old Issues

    tsqltuesdayThis month is an interesting T-SQL Tuesday topic, and it’s brought to us by Andy Mallon, with the topic of the same old issues we’re still dealing with. I think that’s an interesting issue, since I do find myself answering the same old questions over and over.

    If you’ve never participated, T-SQL Tuesday is a day when people should publish a post on the specified topic. This is a way to generate some posts and interest, and perhaps learning, about a topic. Share your thoughts, either on the second Tuesday of each month, or catch up later on your blog.

    We Still Don’t Restore

    I’d like to write something about T-SQL, and I could. Certainly SELECT * is an issue, or why we should not use old style joins (that’s almost gone), but there’s a SQL Server topic that I think bears repeating.

    A backup isn’t enough. You must also test restores.

    The reason isn’t complex, but plenty of people still don’t seem to understand why a backup isn’t enough. After all, if I copied a file, or I ran BACKUP DATABASE successfully, isn’t that enough?

    Suppose you had an issue at 3am and needed to restore a database on a new instance. You run this T-SQL:

    USE [master];
RESTORE DATABASE [Finances]
FROM DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\Finances.bak'
WITH FILE = 1,
    NOUNLOAD,
    STATS = 5;

GO

    And you get this error:

    Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint '0xD9B9E685D465E29C11E15346D995DEF59E53B4A3'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

    What do you do? Hopefully you recognize the issue and can fix the issue. Maybe more importantly, you have a backup of the missing certificate.

    Most people don’t deal with encryption, but you never know when your backup job might start failing, perhaps writing to a damaged file that appears to work (if you write as a device) but really isn’t capturing the backup file. Perhaps you don’t know that your backups are being written to a location and deleted a day later, but the process that is supposed to copy them to tape or a remote file share is broken.

    Any number of things can happen. The point is that you want to be sure that you are actually getting useable backup files.

    That means testing restores.

    It’s 2016. I shouldn’t have to remind anyone of this.