Find my git remote

I work on a few projects and have a bunch of demos setup, but I don’t work on them all every week. Being only semi-creative like many of you, I end up with some similar projects with similar names. When I wanted to determine which project was linked to which remote repository, I didn’t see an easy way.

I perused help from the command line, but didn’t see anything, so I did a quick Google search. From Stack Overflow, I found this quick command.

git remote –v

When I ran this, I easily could get the address of my remote:

2016-12-03 20_12_43-cmd

Running this in each of the repos showed me the one I needed.

I’m sure plenty of experienced people might know this, or you might always use something like SourceTree or GitKraken, but if you’re working in the command line more (as I often am), you might find this handy.

Hopefully I’ll also remember this now.

Posted in Blog | Tagged , | Leave a comment

Moving Tasks with Copy-SqlJob

More dbatools experimentation for me. This is a great set of Powershell cmdlets that solve simple, handy problems. In this post, I want to look at Copy-SqlJob, which will allow me to easily move jobs from one instance to another.

The Job Subsystem

Jobs in SQL Server are part of the SQL Agent subsystem, which means they’re a part of SQL Server, but not really. Maybe the one part of the jobs that is really complex is getting the code for the job. You either need to use an SMO interface and script out the job, or you can decode the tables in msdb. Neither of which is handy.

We do need to move jobs, though. Perhaps the most common places I need to copy jobs around is for a DR (disaster recovery) situation. Clustering will move jobs from node to node as it covers the instance, but all the other technologies (AGs, Log Shipping, Mirroring), all of these only work with databases. So items like jobs need to be manually moved.

That’s not good.

Over time, we’ll edit, add, change jobs, but I’ve never seen anyone do a great job of actually ensuring the DR systems are up to date with jobs, logins, etc. all the time. There’s too much of a manual process.

That can change with Copy-SqlJob. I can now not only copy particular jobs between an instance, I can use a –Force command to ensure the copy takes place. This means that I can easily ensure the most recent version of my job is copied over. Or I can just skip this, and if the job exists, it’s not copied.

Running the Cmdlet

It’s easy to use this cmdlet. It can use Windows Auth or SQL Auth. It’s up to you. The documentation leaves something to be desired, so you’ll need to use the PowerShell Get-Help to learn what parameters exist.

For a simple copy, I’ll move all jobs from one instance to another. Here are the jobs on my primary SQL Server 2016 instance.

2016-11-22 09_11_09-SQLQuery1.sql - localhost_SQL2016.DBAAdmin (PLATO_Steve (66))_ - Microsoft SQL S

These are administrative, backup jobs and index jobs from Ola Hallengren. I don’t have these on my QA instance, which isn’t good.

2016-11-22 09_11_25-SQLQuery1.sql - localhost_SQL2016.DBAAdmin (PLATO_Steve (66))_ - Microsoft SQL S

Suppose this is a new instance and I want to add them? I can use Copy-SqlJob, but I want to know what will happen. I’ll use the –Source and –Destination parameters and then include a –Whatif. This gives me a list of jobs that will be copied, and those that won’t be.

2016-11-22 09_21_13-powershell

If I use the –Force parameter, you’ll see that all jobs are copied.

2016-11-22 09_22_25-powershell

Well, not copied, but they will be.

I could limit this to specific jobs. For example, let me just copy over these jobs

  • DatabaseBackup – SYSTEM_DATABASES – FULL
  • DatabaseBackup – USER_DATABASES – FULL

When I run the command with the –Jobs parameter, I see just these jobs moved:

2016-11-22 09_45_14-powershell

Let’s actually do that. Once I remove the “”-Whatif” and run the command, I see this:

2016-11-22 10_09_21-SQLQuery1.sql - localhost_SQL2016.DBAAdmin (PLATO_Steve (66))_ - Microsoft SQL S

Note, this doesn’t actually move the dependent objects. Meaning the first job uses this procedure:  [dbo].[DatabaseBackup]

I’d have to make sure this is copied separately for the job to actually run and succeed. Right now it will fail with an error that an object can’t be found. However, if you have jobs that you are using in a DR situation, this ensures the job itself is moved over.

Rather than copy the syspolicy_purge_history job, I’ll exclude it. I can do that with the –Exclude parameter. Note that the two jobs I’ve already copied are shown as warnings.

2016-11-22 10_11_39-powershell

All the parts of the job itself are copied. The description, the owner, the comments, the schedule, the steps. Everything is copied, which makes this perfect for ensuring that when you deploy a job, it can be deployed to all of the places you need it deployed by copying it from the source.

Of course, you’ll also want to ensure you use some method to disable the jobs on the DR instances (along with re-enabling them on failover).

Posted in Blog | Tagged , , | Leave a comment

First Class Jobs

I was looking through the forums the other day and noticed that someone wanted to track changes to the Agent jobs on their instance. However, they couldn’t find a DDL trigger to capture the changes to a job. That’s because a job is really data in a table, in dbo.sysjobs, dbo.sysjobsteps, etc. in msdb. To capture changes to a particular job, one would need a DML trigger that captures the insert/update/delete actions on these tables. You could also use XE, Audit, or some other feature that can track data changes in the msdb tables.

I’m not sure that makes sense to me. This week I’m wondering if any of you feel that SQL Agent jobs aren’t well integrated into SQL Server.

Do you think that jobs should be a first class database citizen and managed with CREATE JOB, ALTER JOB, etc. DDL commands?

I think so. In fact, this would be the first step (in my opinion), towards getting jobs as a more useful database work process. Let’s have a job system that handles automated mechanisms inside the database as actual objects we can manage, set permissions on, and control just as we do other objects. Let’s treat these objects for what they are, actual items in SQL Server that should be objects.

Could this be implemented? Sure. In fact, I think this might be one of the best ways to start containing jobs inside of a database. Make them a first class object inside a database. We can use msdb as the container for instance wide jobs. Then any sort of scheduler, including SQL Agent, could use an API to pick up the code from within the database and execute it however they determine is best.

I don’t think this is simple or easy, but it’s possible. It would take some engineering effort from Microsoft, but I’m confident they could build a great ssytem. Making a job a first class citizen could help us control and simplify everything to security to transfer of the work from instance to instance. When the database moves, so does all the work associated with it.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | 2 Comments

Hey Posh, Are My Services Running?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In a previous post, I looked at escaping strings. The reason I needed to do this was that I was trying to do some automatic work with building and starting SQL Server instances. Part of laying the base for this was checking if services are running, and then perhaps taking action, like starting or stopping.

I knew there was a Get-Service command, and ran that. The output from this is much more than I’d like to consume.

2016-11-15 14_46_58-cmd - powershell (Admin)

I’d like to limit this to SQL Server services. I know there is usually an MSSQLServer service, but since I tend to use named instances, this doesn’t work. Plus, I don’t want to search for just a particular service. I want all services for SQL Server.

There is a Where-Object command, that allows me to search. There is also a –Like option for comparisons. I’ll structure a command like this:

Get-Service | Where-Object ($_.Name –Like “SQL*”)

That is less than successful.

2016-11-15 14_49_20-cmd - powershell (Admin)

Why not? Well, PoSh has some syntax requirements and one of them here is that I don’t want parenthesis, I want curly braces. If I change this, things work better.

2016-11-15 14_51_00-cmd - powershell (Admin)

If you’re older like me and don’t necessarily read small print easily, this might be one that catches you for a bit. However, notice that I only have my SQLAgent and system services, not the core database engine. My wildcard needs work.

2016-11-15 14_52_01-cmd - powershell (Admin)

Now I see all my services and I can easily decide if I want to stop, start, restart, etc.


This was a quick post. It took me 10 minutes to relearn a few PoSh things and practice and then about 5 minutes to write this.

I’ll remember it, and it shows how I’m building my administrative skills. You should do that as well.

Posted in Blog | Tagged , , | Leave a comment

Learn to Earn

Could you double or triple your salary by learning more technology skills? Sure, and here’s a great story about someone learning PowerShell and dramatically increasing their earnings. This is a good read, looking at the journey of someone through their career and how they credit learning PowerShell for the changes in salary.

When I read this, I don’t view this as PowerShell being the reason that Mr. Duffney increased his salary. Instead, I think the takeaway is that professional learning and regular skill development, focus across time, and producing results for an employer as being the primary reasons for raises. I think that the right part of the salary curve in the post, which has a steeper slope, is also likely due as much to speaking and giving back as it is to solving problems. The other lesson in this piece is that Mr. Duffney is constantly gaining skills, and looking to move forward in his career.

The world of technology is ever changing, with new platforms, additional features and changing paradigms constantly appearing. I’m sure many of you have experience with management that becomes excited by the latest buzzword or hot topic in the media, expecting that each of us can quickly build a proof of concept. Perhaps you’ve dealt with a boss that assumes we could buy a product or build a tool that easily solves some problem because they read about some other company in a publication having success.

Tackling a new project or technology is a challenge, but we can learn to ease the way for ourselves. We should be constantly learning something, anything that exercises our minds. One of the best talks on the topic I’ve seen is from Andy Warren, and I’d encourage you to check it out. Andy talks about directing your learning in an area that can help you. That may sound daunting, but I think that building the habit of regularly learning something is important in this business. We never know where our career may take us, and being accustomed to the idea of picking up some new technology and using it for a task is a skill you should practice and develop. The more often you try to improve your skills, the more comfortable you will be with the idea of tackling some new technology. In the first piece linked, we see that Mr. Duffney had a plan to become a CCNA, but over time that plan morphed into something else, as did the focus of his learning.

I certainly have been able to raise my salary by learning more about databases. This usually comes about not just from learning, but from applying that learning to my job, showing my employer (and potential future employers) that I provide lots of value for my salary. Using new skills in a valuable way is the method by which I’ve most often been able to translate learning into a raise.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

What’s the little popup window in #SQLPrompt?

Awhile back I was working in SSMS and saw this window.

2016-11-21 16_25_51-SQLQuery1.sql - localhost_SQL2016.sandbox (PLATO_Steve (66)) - Microsoft SQL Ser

It threw me off since I was trying to write some code and hadn’t expected it. I clicked Escape, Enter, a few things and was getting frustrated when it disappeared.

I ignored it until I saw the window again and then investigated. I’m glad I did because I was able to answer a question from someone else recently that didn’t know how to get rid of it.

Tl;Dr CTRL will make it appear or disappear.

When I am working with SQL Prompt, it’s in the background. I usually just depend on it to pop up some code or give me information. This means when I have a cursor, there’s no sign of SQL Prompt. Notice this below.

2016-11-21 16_27_38-SQLQuery1.sql - localhost_SQL2016.sandbox (PLATO_Steve (66))_ - Microsoft SQL Se

As soon as I select an area, as little as one space, I get a small SQL Prompt window in the left sidebar. As you can see in the image below, this has a down arrow on it.

2016-11-21 16_27_44-SQLQuery1.sql - localhost_SQL2016.sandbox (PLATO_Steve (66))_ - Microsoft SQL Se

I can click on this, but being a keyboard person whenever possible, I accidently discovered that CTRL will expand this, as shown below.

2016-11-21 16_29_34-SQLQuery1.sql - localhost_SQL2016.sandbox (PLATO_Steve (66))_ - Microsoft SQL Se

What threw me initially is that not all my snippets are in this list. Only those that have the $SELECTEDTEXT$ token inside them. These are handy snippets that I want to use to encapsulate text.

For example, let me surround a simple query.

2016-11-21 16_32_00-SQLQuery1.sql - localhost_SQL2016.sandbox (PLATO_Steve (66))_ - Microsoft SQL Se

I see the SQL Prompt icon and can click CTRL to open the list. If I type “cv”, I get the Create View snippet.

2016-11-21 16_32_11-SQLQuery1.sql - localhost_SQL2016.sandbox (PLATO_Steve (66))_ - Microsoft SQL Se

Once I then hit tab, I get the snippet with my query inside.

2016-11-21 16_32_22-SQLQuery1.sql - localhost_SQL2016.sandbox (PLATO_Steve (66))_ - Microsoft SQL Se

This is especially handy with things like TRY..CATCH, where I can write the TRY part and then quickly surround it with the structure.

Once you get used to this, and learn not to habitually tap the CTRL key (as I do), you’ll find this list of snippets handy. And if you don’t like them, just tap CTRL and get rid of the list.

Posted in Blog | Tagged , , | Leave a comment

The Pressure to Compromise Ethics

There have always been those that seek to defraud, deceive or mislead their customers. In order to do this, someone inside an organization has always been willing to pressure employees to compromise their ethics and morals. Construction companies may use substandard materials or ignore standards for construction. Financial companies falsify performance records of products. Medical professionals may order unnecessary tests to charge more. Perhaps one of the common areas many people feel cheated is with used vehicles, where there are numerous stories of deceit from companies and individuals. I’ve had my own experiences with cars to support this.

It may be no surprise to you this is also happening in the digital world, which seem fundamentally more disturbing to me. After all, software is constantly changing in a way that many other industries don’t. We can fundamentally rewrite the rules under which systems work by deploying new code, something many of us do on a regular basis. What’s more, we have rules and regulations that fundamentally prevent us from disclosing how the systems work, limitations that don’t allow for anyone to easily audit or evaluate what the programming might be doing.

This is different than a car, where we can examine the components and test them, or have a third party perform this for us. It’s different than health, where we can solicit second opinions. Even construction has inspections from independent groups that can verify some specification is met. However, none of that exists for software. In fact, our industry resists remotely attempting to implement any sort of ethics by limiting access to source code.

There’s an article that lists some of the issues developers have encountered. It also talks about teaching ethics as a way to reduce the impact, which is a good idea. But without any sort of accountability or transparency into the actual code, I’m not sure ethics will help much. There are always people that need a job, and may be willing to write code that continues to provide a paycheck because they aren’t sure they can get another job. I know most of us would like to think we wouldn’t succumb to pressure, but it can be hard to be sure what you’d do until you are confronted with a particular situation.

I do think this is a problem that will grow, and become more pervasive in the digital world, precisely because no one really knows what software is doing under the covers, and it’s hard to even determine what might be happening if you could access the source code. After all, can you be sure that code you get is actually what is running? You can, but not easily.

What I’d like to see is some sort of framework that allows us to specify the behaviors of our software, along with the data movement and handling so that end users would have an understanding of how the software should work. Maybe some automated way of producing a “contract” based on the code. Then we could resolve disputes without ever needing to examine the source.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | 2 Comments

Finally, Create or Alter

There are lots of reasons to upgrade to SQL Server 2016, but this is the one for me. We finally get a CREATE OR ALTER statement in T-SQL. This not only makes lots of code easier to write, it means that the ways in which you might script and schedule your future deployments will be cleaner. This is an exciting change for implementing a simpler and easier Continuous Integration/Continuous Deployment system in your organization.

It’s not perfect news for a few reasons. First, this is a SQL Server 2016 addition to T-SQL only. That means until you have most of your applications have moved to SQL Server 2016 SP1+, you won’t be able to use this construct. That’s OK, because it will mean that at some point most of our instances will be on SQL Server 2016 SP1 or later, and much of our code will be cleaner. We won’t resort to including IF statements in our deployment scripts. We won’t need to create stubs of procedures and functions so our code is embedded in an ALTER script. In essence, you won’t need to maintain two separate code constructs to make a change.

This isn’t perfect, nor is it complete. We still don’t have CREATE OR ALTER for tables. That’s the place where I’d really like to get a consistent way of coding items. What I really want is a complete view of the table each time I change it. By this I mean that if I create a table like this:

    studentname VARCHAR( 200),
    status TINYINT

Then I want to be able to add a column like this:

    studentname VARCHAR( 200),
    status TINYINT,

Or alter a column like this:

    studentname VARCHAR( 200),
    status BIT,

Or better yet, have a CREATE OR ALTER for tables.

I know this might be asking for a lot, but I really think that we ought to get a consistent way of coding databases so that we can reduce the mistakes and make our systems easier to understand. I’m sure this may require substantial engineering, not to mention a great deal of understanding of how this would actually affect our systems when run, but it would certainly make our code cleaner.

I doubt we’ll see these kinds of changes, at least not until we have an ANSI standard that encompasses them, but I would hope that as an industry we would mature and improve the way we work with databases, not remain bound by tradition and history.

Steve Jones


Posted in Editorial | Tagged , | 2 Comments

Rate a Session for GroupBy

One of the things that I struggle with is understanding whether a session at a conference like a SQL Saturday is worth watching. I also struggle writing abstracts and attracting people to my own sessions, so I think the idea of GroupBy allowing rating and reviewing of abstracts in advance is fantastic.


First, let me encourage many of you to go take a minute today and rate an abstract. If you see something that you like, leave a note. If you aren’t sure of something, or don’t like something, or even have a question about the content, leave that note as well.

I’ve been fortunate to attend lots of events. I speak at many, but I try to view a session or two at each as well. I’ve seen some great ones, and some poor ones. I do try to provide constructive criticism, and I do so privately. If you’d like feedback from me at any event, please ask.

One of the things I’ve seen is that the abstract often doesn’t quite match the talk, or the abstract doesn’t really help me understand what will be covered. It doesn’t matter if someone has tried to write a cute description or a plain boring one, the writing doesn’t always match the talk well.

I know I make mistakes in my abstracts. I know sometimes I write something that I realize later isn’t quite what I think will work in the talk. I’d like the chance to edit and correct small items. More importantly, I’d like to be sure that if my abstract topic (and talk) could slightly be tweaked in a way that more people like, I want to do it.

Take a minute and give some feedback. Be honest, rate what you want, don’t take up too much of your day, and help improve the conference schedule.

I’d love to see PASS implement this as well for the Summit. I realize this can be hard, but I would prefer to see some give and take in advance to help build the best set of sessions that people want to attend.

Posted in Blog | Tagged , , | 2 Comments

Validating a Set of Database Scripts using DLM Automation

The basis of all the DLM Automation from Redgate is a series of PowerShell cmdlets. They might look intimidating or confusing, but they aren’t. This is part of a series of posts that examine how you use each one.

Previously I looked at New-DatabaseConnection. In this post, I’ll go through Invoke-DlmDatabaseSchemaValidation. This is the cmdlet that one uses to check if your set of scripts will actually produce a database. This is equivalent to the “build” plugin that exists for a few platforms.

The way this works is that the location of the database scripts is passed to this object through a pipe. This will then validate the scripts on LocalDB with a build of the database and the static data scripts. If this works, then an output object is returned.

A Quick Build

Let’s see how this works. I have a valid database folder on my computer. This has all my object code in subfolders, including static data in the data folder. I want to validate this folder.

2016-11-22 13_56_30-ScriptFolder

I can do that with this code. I’ll pass the location of the scripts into the cmdlet.

$output = “e:\Documents\GitHub\SimpleTalk_Devlopment\ScriptFolder” | Invoke-DlmDatabaseSchemaValidation

When I do this, a LocalDB instance is created and the code validated. I get a message to that effect. The output variable has the confirmation message.

2016-11-22 14_06_39-powershell

This means the code is valid. However, does this really work? Let’s edit some code and see. I’ll change the code for a procedure. Here’s the original GetCountryCodes.sql.

2016-11-22 14_08_29-dbo.GetCountryCodes.sql - Notepad

Let’s change this to top 100 and add an ALTER, but I’ll get an extra comma in there. This is no longer valid SQL.

2016-11-22 14_10_45-dbo.GetCountryCodes.sql - Notepad

Let’s re-run the build. We now see this has failed with an error, and the file is the one I edited:

2016-11-22 14_11_41-powershell

This is a quick look at builds, but there is more that can be done. You can specify the server and database to be used, combining this with the New-DlmDatabaseConnection I previously wrote about.

I urge you to experiment with this cmdlet if you want to perform your own builds.

Posted in Blog | Tagged , , , , | Leave a comment