Skip to content

2016🔗

  • 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)

  • 1 min read

TFS Work-Item Fancy Filtering

If you want to create a TFS query that would identify work items that have changed, but were not changed by the person working it, there is a nifty way to do this.The filtering field can be set to <> another field that is available, but the syntax/setup in Visual Studio is not intuitive. It's in the dropdown list, but I'd never noticed it before!

filter list

AND ' Changed By ' <> [Field] ' Assigned to

Note that you don't include brackets on the assigned to field, and that the <> [Field] is not a placeholder for you to type the field name in, it's actually the literal command for it to parse this correctly.

Filter setup for tfs query

  • 1 min read

Easy way to test log shipping or availability groups setup

Have been working through the fantastic training resources from Brent Ozar's Everything Bundle and on the recommended resources they mention after all the training on log shipping and availability groups that you can use a lab environment from TechNet to actually get going on familiarizing yourself with the technology more.

This is great! Of course, it's not the full deal, but this gives a tangible way to get moving on familiarizing yourself with this complex technology. TechNet Virtual Labs Availability Groups Side note: Fantastic training resource with great roi with Brent Ozar Unlimited "Everything Bundle" if you apply the current podcast review discount of 78%. Great value with immediate return. I also like pluralsight, but find it takes more time investment to get the immediate value. Their courses are short, compact, and full of great material.

Recommend you add to your training budget asap. Podcast Review Discount Combine this with a subscription to Office Hours with the app Pocket Cast, and you'll have an easy way to keep up to date with some great tips.

  • 4 min read

Diff all files

{{< admonition type="info" title="Updated: 2017-07-14" >}} Still find this incredibly awesome! Developer just updated for Visual Studio 2017 after a lot of hard work. Github repo here for any issues. Big thanks to DeadlyDog for this great tool and putting in the effort to update for VS2017. deadlydog/VS.DiffAllFiles: Visual Studio Extension to make comparing files before and after committing them to Git and TFS faster and easier {{< /admonition >}}

{{< admonition type="info" title="Updated: 2016-01-25" >}} I started using the option to compare a single file at a time, since it picks up the syntax highlighting then. The other way (merge files into one) can't handle syntax highlighting with the various files types all mixed into the same window. Diff All Files extension handles this beautifully with proceeding one by one and automatically opening the next file to compare or allowing you to hit next to close and reopen with the next file to compare. I still enjoy using this due to the "context" lines that reduce the noise to just the actual section being changed. {{< /admonition >}}

If you need to do code comparison on a lot of files in Visual Studio, I ran across an extension Diff All Files that is really helpful for merging down the individual file changes into more consumable format.

In the changeset view you can select diff all files and whatever settings you've setup in the Tools > Options > Diff all Files settings will then feed through automatically.

2016-07-06_9-31-56_diff_all_files_setupoptions

You then click on diff all files in the changeset viewer

diff_all_files_animation

All the non-excluded items will then be merged into a single file, if this option was selected, or separate windows. Personally, I've found the merged view really helpful to have one single pane to scroll through.

2016-07-06_9-32-02_diff_all_files_settings

Third Party Comparison Perks

To go a step beyond this you could use a third party comparison tool. My preferred option is Araxis Merge (disclaimer: they provided me with a license for evaluation, which doesn't impact my assessment). I really like the fact it merges down similarities into a concise comparison. This tool gives you the option to provide only the different lines, with a X number of lines before and ahead for context. This could reduce thousands of lines of code to just a few hundred that have variances. Win! Highly recommend you check them out, as if you are doing open source they have a free license for that. If you do a lot of code comparisons, some of the additional functionality in their app might be worth it, otherwise the built in viewer in TFS is pretty good.

In my test comparison, I reduced 3245 lines of code in the comparison window to 25 lines, which was the actual changes + 10 lines above/below for each change to have context. This resulted in only a few hundred lines to scroll through. Made my life easier! The only con for me with Araxis is no dark theme.... but I'll live.

araxis_merge_gui

configuring third party tools

configure_external_diff_tool

I saved this snippet from working through Araxis documentation for setting up their tool with Visual Studio.

I also just ran across another site that had a nice resource list of configuration settings for different comparison tools. Diff All Tools - Visual Studio Extension

Other comparison tools

Third party tools can be great for some extra perks like File versioning, comparing folders, and more. You can get by without them, of course, but if you do a lot of change review consider some of the options. A few others I've reviewed (if pro/paid they provided license for me to evaluate)

  • Delta Walker (pro): Great UI, does image similarities comparison tool. Didn't seem to have a "show only changed lines with context" like Araxis at the time I reviewed. Mac app!
  • Devart Code Compare (pro/free): I love most programs Devart puts out. This is a good comparison app. Wasn't my preferred option, and at times struggled with large files, but overall was good in comparison accuracy.
  • DiffMerge: Brief overview, solid tool, just didn't have specific improvements I needed (focused on changeset/sql comparison)
  • Beyond Compare: didn't dive into this extensively. They have a devoted following with a lot of loyalty, so check them out if you want to evaluate their software.
  • Built in TFS diff - great for a built in tool, I just wanted to have some enhancements in what to ignore and summarize.
  • SQL Compare: my favorite for reviewing changsets that are directly in TFS. I wish they'd add a module for just pulling up the diff view without having to open SQL Compare to setup a comparison though. Sounds like a good user voice feedback item :-)
  • 1 min read

Seeker & Servant: Fantastic Music with incredible dynamics

Just recently discovered this artist after being exposed to an article from Worship Leader magazine. Fantastic dynamics. The dynamics and beautiful harmonies are pretty darn close to what I'd love experimenting with if I had a group of folks playing those instruments. Interestingly, the vocal harmonies are very simple, but I found very beautiful. It's compromised of a tenor and a baritone range, and is a fresh change stuff like Shane and Shane which both have incredibly high ranges. I found it very approachable to enjoy singing with. The power of the musical dynamics and beautiful lyrics was a major win. I'll be following them closely.

Get their latest album for free here: Seeker & Servant Similar Artists/Albums:

  • Fleet Foxes
  • Dustin Kensrue: Carry the Fire

Would Like If:

  1. You like post-rock
  2. Long Vamping Dynamics
  3. Minimalist Arrangements
  4. Tight 2 part harmonies

Here's the album on spotify for those who want to check it out!