Skip to content

posts🔗

  • 2 min read

The Mysterious Black Box of R - For the SQL Server Guy

Took a class from Jamey Johnston @ SQLSaturday #516 in Houston. Lots of great information covered. Follow him for a much more detailed perspective on R. Jamey Johnston on Twitter @StatCowboy. Did a basic walkthrough of running an R query, and figured I'd share it as it had been a mysterious black box before this. Thanks to Jamey for inspiring me to look at the mysterious magic that is R....

Setup to Run Query

Simple-Talk: Making Data Analytics Simpler SQL Server and R This provided the core code I needed to start the process with R, recommend reading the walkthrough for details. To get started in connecting in RStudio to SQL Server run this command in the RStudio console.

install.packages("RODBC")
Verify the library is installed by running from the console

library()

Running Select from View

This was run against StackOverflow database

library(RODBC)
startTime1
<- Sys.time() cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=localhost;database=StackOverflow;trusted_connection=yes;") dataComment <- sqlFetch(cn, 'vw_testcomments', colnames=FALSE,rows_at_time=1000) View(dataComment) endTime1 <- Sys.time() odbcClose(cn) timeRun <- difftime(endTime1,startTime1,units="secs") print(timeRun)

I created a simple view to select from the large 15GB comments table with top(1000)

USE [StackOverflow]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    CREATE view [dbo].[vw_testcomments] as
    select top(10000) * from dbo.Comments as C
GO

Running Select from View

viewing the results of basic query in r studio

viewing the results of basic query in r studio

running R script in PowerBi

running R script in PowerBi

execute r script

execute r script

results preview

results preview

Visualized in Power Bi

Visualized in Power Bi

  • 2 min read

Red Gate Dependency Tracker - Making Databases Into Moving Art

data structures are art

If anyone thinks that working with complex data structures is boring... I don't know what world they live in. The problem is often that sql tables and data structures are just script files and lists of tables in an object explorer.

However, once you crack open the visual aspect of database diagramming and data architecture, you can see some interesting patterns emerge.

red gate dependency viewer tracker

I've long enjoyed playing around with Red Gate Dependency Tracker. I've found it a great tool for interacting with dependencies and visually working through their relationships. I figured I'd share with others a more artistic side of database architecture, as I've never seen it done, and I found it uniquely beautiful.

visual review of data structures

This is the architecture of the application I work on everyday. I inherited care of a system that shows the attention and design that was put into it. The order of the key relationships is obvious. I've worked on some systems that this diagram would be completely different story without the order and structure. The interesting part about the visual dependency view is you can often see orphaned objects that aren't correctly linked to their parent objects, so it can serve as a useful tool to help improve existing designs. My previous article about tools I've use for this is here...Documenting Your Database with Diagrams Enjoy!

circular

balloontree

smartorganic

hierarchic

  • 4 min read

Automating SSMS 2016 Updates & Install

{{< admonition type="info" title="update 2016-04-27 11:20 - Red Gate SQL Toolkit" >}} This also is a great help for folks using Red Gate SQL Toolkits. It can help ensure all items are up to date. When a new bundle installer is identified, it would download the new one and you could then trigger the updates of each of the apps you desire, without having to keep run the download later through Red Gate's tool. {{< /admonition >}}

Figured I'd share a way to automate the SSMS 2016 updates until it gets it's own fancy self updater. I love staying up to date, but with Power BI, SSMS, and others updating monthly or more and not having any automation for keeping up to date, this is something I find a waste of time that I'd rather automate.

ssms 2016 install

I think this gets a win, as it's by default in a dark theme. If contains the future possibility of dark theme just like Visual Studio, it gets my stamp of hearty approval. According to some social media posts I've read, it's not yet implemented, but bringing the theming and extension capabilities to SSMS is a goal, and some of it should be here soon.

ssms 2016 install

currently using 2015 shell

currently using 2015 shell

Updates applied seperately from sql service packs

Of course, the main benefit to having the SSMS install as it's own installer/update is we can get regular updates and improvements without it having to align with sql server service packs. This should allow Management Studio to have more rapidly developed and improved product with more frequent releases.

Updates applied seperately from sql service packs

changelog

Finally have a changelog to easily review Sql Management Studio updates. As I recall, previously you had to sort through all the changes with sql bug fixes to find what was updated. SQL Management Studio - Changelog (SSMS)

changelog

Ketarin to the rescue

Ketarin is one of my favorite tools for automating setup and maintenance of some tedious software products. It takes a little practice to get the hang of it, but it's pretty awesome. It's sort of like a power user version of Ninite. You can automate setup and install of almost anything. The learning curve is not too bad, but to fully leverage you want to benefit from the regex parsing of the webpage to get the download link that changes with version, such as what we might deal with on version changes with SSMS.

Download latest SSMS Version

MSDN Installer Location Hopefully, they'll improve the process soon by trimming the size and allowing ssms to autoupdate. Just like Power BI, you have to download the installer for the new version and run the installer to upgrade. As a solution in the meantime, you could leverage the power of Ketarin** ** I created a installer package for running the update automatically, so you could have this setup to check upon startup, and then when a download is detected, download the update, and run silent install. Perhaps this will help you if you want to stay up to date.

Download latest SSMS Version

Ketarin passive install

The version parsing I added into this means you shouldn't need to download the installer unless it detects a new version applied.

Ketarin passive install

Update ready to download and apply

This is what you'd see on computer startup with a fresh update ready and waiting for you.

Update ready to download and apply

Last setup note

If you setup Ketarin, to make the app portable, copy the jobs.db from appdata folder, into the application folder and restart. This will make it portable so you can actually put this on a USB, clouddrive, or however you want to make it easily usable on other machines.

red gate sql toolbelt updated automatically with Ketarin

  • 2 min read

Google Search Only Results from the last year

Tech changes quick. Reading google postings from something in 2009 is not my first choice.I found an option after digging through some google discussion posts on how to setup the default search in chrome (also applies to other browsers) to automatically apply the advanced filter option for "results in last year".

  1. Go to chrome settings menu Chrome settings menu

  2. Navigate to manage search engines Manage search engines dialogue

  3. Enter the new search engine option Enter new search option. Here is the snippet that sets the new default.

  1. Set the entry as default Set new entry as default

  2. Review the great results of your hard work :-) Results of search are now automatically filtered

  • 4 min read

Cool Tools: Powershell ISE-Steroids

Disclaimer: I have been provided with a free license because I am reviewing. This doesn't impact my assessment of the tool. I have a passion for finding tools that help developers, and even more specifically SQL DBA/Developers improve their workflow and development. I don't recommend tools without actually using them and seeing if I'd end up adding to my roster of essentials!

Cool Tool: ISE Steroids

Expanding Powershell ISE

Powershell ISE is simple. Not much fluff, but it gets the job done. You already know you have it on most of your machines, so expanding ISE seems like a logical step if buying something like Sapien's powershell studio or installing Idera's/Dells/etc studios are not something you want to do. I ran across Powershell ISE-Steroids as a recommended "must have" and decided to investigate further.

Intelligent Code Parsing

For those of us that don't live daily in powershell, having some explanations on best practice and some guidance is a welcome change.

Intelligent Code Parsing

Variable Explorer

This is a great implementation of a variable explorer that can dramatically help when debugging scripts.

Variable Explorer

Diving into variable explorer

I was having trouble finding the right property and objects with Amazon Powershell SDK for AWS. So I broke up a query to get instances into several steps and then explorered the objects. This made things much easier to explore.

Diving into variable explorer

Intelligent Error Checking

Helpful description on error when I clicked on the warning icon

Intelligent Error Checking

Help

I need powershell help a lot. I don't work enough it in it to have it all memorized, so having a helpful syntax reference guide is a great plus. I'm a big fan of more help...that is easily accessible. I'll take as much "powershell for dummies" guidance as possible. I know you can find help with built in powershell functionality, but again, it's the combination of all the little shortcuts and pieces together that help so much. Small benefit is context menu selection of help. With regular ISE, you can run UPDATE-HELP and then press f1 over a cmdlet to get details, this is just a shortcut on the context menu. However, it's a helpful reminder for those newer to working with Powershell ISE.

Help

More help

Help as you've seen it is triggered on executing help against specific cmdlets. However, ISE-Steroids has a context sensitive help that's pretty cool.

More help

example of contextual help

ISESteroidsContextualHelper-compressed

Refactoring Utility

Lots of great best practice refactoring can be automatically applied.

Refactoring Utility

refactoring

This is a very small example of what it would do. A better case would be a more complex powershell query.

refactoring

Versioning of powershell scripts

Instead of having to constantly comment and uncomment for fear of losing work, you can keep things nice and clean with the versioning built in. This is great, I wish more editors had it. In this case there is a shortcut to pull up a compare and you can open up the code comparison quickly to see what variations in the script have occurred. Additionally, commenting is possible.

Versioning of powershell scripts

thoughts

If you are working with powershell then something like ISE Steroids can provide some smart help, ensuring better script quality, and hopefully saving some time. It's a recommended tool for my #cooltools list.

  • 2 min read

Failed to Initialize SQL Agent Log... not worthy

Moving system databases in SQL Server takes a bit of practice. I got that again, along with a dose of SQL humility (so tasty!), today after messing up some cleanup with sql agent server log files.

Failed to initialize SQL Agent log (reason: Access is denied).

I was creating a sql template when this came about. SQL Server Agent wouldn't start back up despite all the system databases having very little issues with my somewhat brilliant sql commands. I had moved all my databases to the new drive location, and changed the advanced startup parameters for sql server and SQL Agent... or so I thought.

Logging location not the same

I apparently missed the order of operations with SQL Server Agent, and so it was unable to start. MSDN actually says to go into the SQL agent in SSMS to change this, and I thought I was smarter than msdn....

MSDN

  • Change the SQL Server Agent Log Path From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.
  • Right-click Error Logs and click Configure.
  • In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file.
  • The default location is C:\Program Files\Microsoft SQL Server\MSSQL . \MSSQL\Log. Found the registry entry and changed here... all fixed!

Fixing in the registry I also updated the WorkDirectoryEntry to ensure it matched new paths.

Thanks to this article I was saved some headache. I also learned to read directions more carefully :-)

  • 1 min read

Calculating Some Max Mirror Stats

This turned out to be quite a challenge. I couldn't find anything that made this very clean and straight forward to calculate, and in my case I was trying to gauge how many mirroring databases I could run on a server.In my scenario, I wasn't running Expensive Edition (@BrentO coined this wonderful phrase), so was looking for the best way to assess numbers by doing mirroring on a large number of databases, in my case > 300 eventually. The documentation was... well.... a bit confusing. I felt like my notes were from the movie "A Beautiful Mind" as I tried to calculate just how many mirrors were too many! This is my code snippet for calculating some basic numbers as I walked through the process. Seems much easier after I finished breaking down the steps. And yes, Expensive Edition had additional thread impact due to multi-threading after I asked about this. Feedback is welcome if you notice a logical error. Note that this is "theoretical". As I've discovered, thread count gets reduced with increase activity so the number mirrored database that can be mirrored with serious performance issues gets decreased with more activity on the server.

  • 1 min read

Failover all databases to other server

Quick snippet I threw together to help with failing over synchronized databases to the other server in bulk. No way I want to click that darn Fail-over button repeatedly. This scripts the statements to print (i commented out the exec portion) so that you can preview the results and run manually.Note that it's also useful to have a way to do this as leaving databases running on the mirror server for an indefinite period can violate licensing terms on the secondary server when it's a fail-over server and not meant to be the primary.

  • 1 min read

Previewing the new SSRS 2016 portal

Ran into an issue with the "Preview New Reporting Portal" link on a fresh install of 2016 giving me a not found error.

SNAG-0031

Changing the virtual directory in the Report URL tab for SSRS configuration fixed this invalid link. In my case, I changed /Report to /Reporting. Thanks to Adam on Stack Overflow for providing the solution and saving me a lot of time!

  • 2 min read

SQL 2012 SP3 and entity framework conflict

the problem

An issue with SQL Server 2012 SP3 was identified that impacted EF4/5 due to additional datatypes in the dll.

System.EntryPointNotFoundException: Unable to find an entry point named 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'

diagnosing

To easily identify the available dll versions of sql server, I ran a quick adhoc bat file.

The output returns a simple text file like this:

A post in technet mentioned that the DLL shipped with SP3 could cause these conflicts and if the uninstall didn't clean up the GAC correctly, problems could occur with Entity Framework calls.

Can confirm in my case it was due to dll shipped in SQL Server SP3. I had to uninstall the patch but the newer dll was still in the gac so I had to overwrite with the older version using gacutil. ( Edited by snowcow Thursday, January 14, 2016 12:41 PM )

The Fix

In my case, I still needed the current SP3 version, but we wanted to make sure that the app was pointing to the older version to avoid this error. I apparently needed to point backwards to: C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\10.0.0.0__89845dcd8080cc91 Stack Overflow, the golden mecca of programming knowledge, saved the day with a solid answer

EF Cannot Update Database This forces the EntityFramework to use the version 10 of the SqlServer.Types.dll, which doesn't have the Geometry type apparently. - KdBoer When the fix was applied to map the application config to the older version of the Microsoft.SqlServer.Types.dll (in this case 10). Apparently the 2012 SP3 provided some additional functionality in the dll and this had a conflict with Entity Framework 4 for my situation (and according to online posts EF5 also had some issues)