Using PowerShell to Execute SQL Maintenance

It’s an odd truth that laziness leads to better systems administration. That is, so long as it spurs you to automate and thoroughly document a repetitive or tedious task. For instance, I was recently tasked with reducing the excessive size of some system tables in our Microsoft Dynamics CRM environment. To start with, I accomplished this the way you would any one-off task. I RDP’d to each of the app nodes, disabled the service that interacts with the tables we’re performing maintenance on, RDP’d to my utility box, used SSMS to disable each of the SQL Agent jobs that might interfere (on two different nodes), opened the script (provided by Microsoft), tweaked it to my liking, and executed it. The next morning I did all of this in reverse, starting with cancelling the script. For one evening this isn’t really a big deal. However, we soon realized that in order to get the record count down to where we wanted it that several iterations of this maintenance would have to occur over the course of multiple weekends. Reviewing all the steps I’d just performed, my thought was “ain’t nobody got time for that”.

Confronted with performing multiple GUI-based steps during each of these maintenance windows I did what any good/lazy Sys Admin does, I scripted it. Below you’ll find an example of what I used. I run it from PowerShell ISE, executing whichever block is applicable to what I want to do at the moment. This allowed me to go from starting up the maintenance in fifteen minutes to under one minute. (I know, 14 minutes isn’t a big deal. But when you’re tired and it’s late every minute counts.) As I mentioned before, my particular case is CRM maintenance. So basically I disable services on the app nodes, disable SQL Agent Jobs that might interfere (my database is in an Availability Group, so I disable them on both nodes), start the SQL Agent Job containing the Microsoft script referenced above, and then do it all in reverse the next morning at the end of the maintenance window. I included service status checks at the bottom because I’m paranoid and want to confirm the services are actually stopped before starting the SQL script. Also, I did not script the stopping of the job. I always hope (in vain) that the job will have finished, signaling the end of this particular maintenance need. Since both SSMS and the script run from my utilities box I check it in SSMS every morning and simply alt-tab over to ISE after stopping the job to start everything back up.

It’s unlikely that you’ll have the exact situation as me, but hopefully this can give you some ideas for how to incorporate these methods into your own work. In any case I hope this helps, and feel free to contact me with any questions.

##################################################################
#Import SQL module.
import-module sqlps

##################################################################
#Stop and disable CRM services on the app nodes.

invoke-command -computername CRMSERVER1 -scriptblock {Set-Service MSCRMAsyncService -startupType Disabled}
invoke-command -computername CRMSERVER2 -scriptblock {Set-Service MSCRMAsyncService -startupType Disabled}

invoke-command -computername CRMSERVER1 -scriptblock {Stop-Service MSCRMAsyncService}
invoke-command -computername CRMSERVER2 -scriptblock {Stop-Service MSCRMAsyncService}


##################################################################
#Disable SQL Agent Jobs on both members of the Availability Group.

Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 0;"

##################################################################
#Kick off SQL Script against the Availability Group name.

Invoke-Sqlcmd -ServerInstance "SQLAGCRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_start_job N'CRM_SysTableCleanup';"

##################################################################
#Enable SQL Agent Jobs on both members of the Availability Group.

Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 1;"

##################################################################
#Start and enable CRM services  on the app nodes.

invoke-command -computername CRMSERVER1 -scriptblock {Set-Service MSCRMAsyncService -startupType Automatic}
invoke-command -computername CRMSERVER2 -scriptblock {Set-Service MSCRMAsyncService -startupType Automatic}

invoke-command -computername CRMSERVER1 -scriptblock {Start-Service MSCRMAsyncService}
invoke-command -computername CRMSERVER2 -scriptblock {Start-Service MSCRMAsyncService}

##################################################################
#Check CRM Services

invoke-command -computername CRMSERVER1 -scriptblock {Get-Service MSCRMAsyncService}
invoke-command -computername CRMSERVER2 -scriptblock {Get-Service MSCRMAsyncService}

 

The Faith of Atheism

The extent to which Christians are criticized for their faith always surprises me. In current American culture, and especially in science-based fields like IT, one is viewed as unintelligent or at least ignorant if they believe in any kind of religion. It is automatically assumed that if you find faith to be a credible notion then you must lack in basic deductive reasoning or logic. The only way to arrive at answers is by science, and to think that science could lead to God is laughable.

Take a moment to examine the foundations of this world view, though. In order to follow it you place a great deal of weight on two pillars, people and science. You must assume in the first place that it is at all possible for humans to fully understand the mysteries of the universe. Secondly, you must assume that science is a capable vehicle for arriving at those answers. I won’t claim to know what the full intellectual potential of humanity is, but it’s safe to say that at this point we do not know everything. I think any reasonable scientist would agree with that. As to the method, I’m as much a fan of science as anyone. In fact, I thoroughly believe that as we uncover more that science will lead us right back to God. However, science can only provide answers based on the answers it already has. Science is constantly disproving its own findings from decades before based on new information that has recently been acquired. It’s just the nature of how it works, and that’s okay. The trouble comes when people think, with a great amount of hubris, that people (who don’t understand everything) can use science (that hasn’t uncovered everything) to make definite declarations about the universe, its origins, and all it holds. Also, there’s the issue of scientists being bought or swayed to produce skewed results in support of a particular idea. They are only human, and it does happen.

So, to bring it back around to faith, people such as myself believe that God created the universe and all that’s in it. Take a moment to stand up, walk outside of the man-made rectangle you’re sitting in (where it’s so easy to feel in control), and literally think outside of the box. Look around at nature with all of its complexity and intricate detail. Take in the hundreds of types of life just within your yard at home or the grounds at your work. Then think about how, to date, not one other life-supporting planet so perfect as this has been found in all the known universe. Think about the vastness of space with all of the planets and stars it contains, most of which we’ve not viewed yet. Then tell me how you’re not living a life of faith by depending on people, who are equally as fallible and weak as you are, to not only understand all of that fully but to also rule out the existence of something they don’t understand in all the areas we haven’t yet observed.

Let me give you another example. Let’s say that based on today’s knowledge and the research of the world’s top minds you come to the conclusion that there is no God (what other reasonable conclusion is there, right?). Going on that information you live a life with no regard for God and die at an old age, having spent your years pursuing the things of this world. It’s possible you achieved fame, wealth, and enjoyed a long list of pleasures. Eighty years after your death science has progressed enough to explore all of the universe, to examine the basic building blocks of life, and finds conclusive evidence of God. You and all those who confidently followed the minds and science of your day will have missed God completely. There is no second chance to seek Him out. By contrast I will have lived a peaceful life of faith, crafting my actions on the advice of a God I believe to be infinitely wise, and then end my days without regret. Each of us will carry the same things from this life past the grave, nothing. But I have the hope of eternity with a loving creator after having spent what is, in retrospect, a very short time on this earth. If things go the other way and I’m the one that’s wrong then I’ve still lived that same peaceful life following wise teaching and, hopefully, doing good to those around me. I’ve lost nothing because we both end in nothing, and I’ve made a much smaller gamble.

In the end the atheist lives their life based on faith just as much as the religious among us. They like to think it is an intellectually superior position based on concrete evidence. However, in truth it is built on imperfect systems that were created by even more imperfect people. I choose to place my faith instead in a mighty God capable of creating all these things, and to view science as a way to find Him and the glories of His creation rather than as a way to dismiss Him. He is revealed through creation, through history, and through His work in the hearts of men (including my own). His name is Jesus Christ, He sacrificed Himself to set you free, and I’d love to tell you about Him sometime.

Scripting Failovers for SQL Server Maintenance

As much as we’d all like to set up our SQL instances and never again let anyone touch them, that’s just not possible in real life. We live in a world where entities, for good or for evil, are constantly exposing vulnerabilities both in the SQL software and Windows itself. For that reason it is the responsibility of good DBA’s to keep their systems updated, and that means failovers.

It is typical in many environments to have a separation of duties, often keeping DBA’s from having control over the Windows Failover Cluster itself. In my particular case I actually have access to the cluster. However, our OCC (Operations Command Center) is tasked with carrying out Windows Updates on a regular basis. Letting them fail instances over means the servers get updated regularly (and also that I have to get up in the middle of the night for one less thing). I needed a way to allow their team members, regardless of experience level, to carry out Windows Updates on our cluster. Enter PowerShell scripting.

The code below is a modified version of what our OCC uses to carry out Windows Updates on our Failover Cluster. (There are options for Availability Groups too, but that’s a post for another day.) In the simplified example below there are six instances (SQL Instance 1-6) balanced across six nodes (sqlnode1-6). To keep things simple, a few assumptions are made in the script:

1. All instances are named with a particular convention. In this case that is “SQL Instance #”.
2. The instance numbers have their permanent homes on nodes of the same number (ie SQL Instance 1 belongs on sqlnode1).
3. The script is run on one of the nodes of the cluster.
4. The script is run in PowerShell ISE section by section, not all at once.

With that in mind I present the code to you below. It is written in a manner such that a Systems Administrator would be reading it as an instruction sheet and carrying out the tasks on your behalf. I am sure there are more elegant and efficient ways to accomplish this task, but this has been successful for us and I hope it can help you in some way. If you have any questions please feel free to comment or contact me on Twitter. Enjoy!

#### 1. Import Cluster Module ####
Import-Module FailoverClusters

#### 2. Fail all instances over to nodes 04-06. ####
Move-ClusterGroup "SQL Instance 1" -Node sqlnode4
Move-ClusterGroup "SQL Instance 2" -Node sqlnode5
Move-ClusterGroup "SQL Instance 3" -Node sqlnode6
Move-ClusterGroup "SQL Instance 4" -Node sqlnode4
Move-ClusterGroup "SQL Instance 5" -Node sqlnode5
Move-ClusterGroup "SQL Instance 6" -Node sqlnode6


#### 3. Remove nodes 1-3 as possible owners and add 4-6.. ####
$ClusterGroups = Get-ClusterGroup

foreach ($Service in $ClusterGroups) 
    {  
      
      If ($Service -like "SQL Instance*")
      {
        Set-ClusterOwnerNode -Group  $Service.Name -Owners sqlnode4,sqlnode5,sqlnode6
        Get-ClusterOwnerNode -Group $Service.Name
      }  
      
    }

#### 4. Install updates on nodes 1-3. ####

#### 5. Remove nodes 4-6 as possible owners and add 1-3. ####
$ClusterGroups = Get-ClusterGroup

foreach ($Service in $ClusterGroups) 
    {  
      
      If ($Service -like "SQL Instance*")
      {
        Set-ClusterOwnerNode -Group  $Service.Name -Owners sqlnode1,sqlnode2,sqlnode3
        Get-ClusterOwnerNode -Group $Service.Name
      }  
      
    }

#### 6. Fail all instances over to nodes 1-3. ####
Move-ClusterGroup "SQL Instance 1" -Node sqlnode1
Move-ClusterGroup "SQL Instance 2" -Node sqlnode2
Move-ClusterGroup "SQL Instance 3" -Node sqlnode3
Move-ClusterGroup "SQL Instance 4" -Node sqlnode1
Move-ClusterGroup "SQL Instance 5" -Node sqlnode2
Move-ClusterGroup "SQL Instance 6" -Node sqlnode3


#### 7. Install updates on 04-06. ####

#### 8. Add all nodes as possible owners. ####
#### Note: This list is mostly random. It only has two goals.
#### Goal 1 is to set the permanent home at the first of the list. ####
#### Goal 2 is to make sure each of the cluster nodes is listed and that they are somewhat randomized. ####
Set-ClusterOwnerNode -Group "SQL Instance 1"  -Owners sqlnode1,sqlnode5,sqlnode2,sqlnode4,sqlnode3,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 2"  -Owners sqlnode2,sqlnode4,sqlnode5,sqlnode3,sqlnode1,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 3"  -Owners sqlnode3,sqlnode6,sqlnode1,sqlnode2,sqlnode5,sqlnode4
Set-ClusterOwnerNode -Group "SQL Instance 4"  -Owners sqlnode4,sqlnode3,sqlnode6,sqlnode5,sqlnode1,sqlnode2
Set-ClusterOwnerNode -Group "SQL Instance 5"  -Owners sqlnode5,sqlnode2,sqlnode4,sqlnode1,sqlnode3,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 6"  -Owners sqlnode6,sqlnode1,sqlnode3,sqlnode2,sqlnode4,sqlnode5

#### 9. Fail displaced instances back over to their permanent nodes. ####


Move-ClusterGroup "SQL Instance 4" -Node sqlnode4
Move-ClusterGroup "SQL Instance 5" -Node sqlnode5
Move-ClusterGroup "SQL Instance 6" -Node sqlnode6

Giving Yourself Permission To Lose

One of the most profound turning points in my life was the moment that I gave myself the permission to lose. Please do not confuse this with apathy or a lack of ambition. It’s more of an upfront decision to roll with the punches, coupled with a focused vision on what is worth pursuing. Let me explain.

By nature (or maybe nurture) I am a very task-oriented person. I’m also a bit of a perfectionist. I want to be the best in every area of my life be it work, ministry, family, or simply recreation. I can’t simply play a video game, I have to be able to play it on a competitive level. I can’t just work in my area of expertise, I feel the need to develop a comprehensive understanding of all technology. I also want to be able to play instruments, fluently speak other languages, read a long list of books, etc, etc, etc. Being task-oriented, I cannot start one of these and just let it go. I have to finish it or it weighs on my mind. I also cannot stop everything else in life for that one task, so I spin up tasks in multiple areas. All of this combined, over time, becomes a huge drain on my mental and physical energy. Not only that, I begin to feel like a failure in each of these areas when I fall short of expertise in all of them. At this point you’re probably feeling stressed just from reading this. Imagine waking up and feeling the burden to accomplish all these tasks while still feeling the sting of not accomplishing them the day before. Living that way just isn’t sustainable.

Then one day I was praying during my devotional time and the thought occurred to me, what if I choose to lose? I’m not a quitter, so the idea of giving up on anything was repulsive. However, as I thought about it I began to realize that by pursuing this laundry list of interests I was already leading myself to failure in those things that matter most to me. Just as our parents always told us, “you can’t have your cake and eat it too”. We are all dealt a finite amount of time and energy. By choosing to involve myself in everything I’d taken away the option to truly invest myself in anything. It occurred to me that in order to win the right battles, sometimes you have to lose the wrong ones.

From that point I began to ask myself what I was really after in life, and I started to set aside those things that didn’t contribute toward those goals. This has lead to a laser focus in my life that I did not have before. At work I focused on strengthening my skill in database technology. To do that I had to let myself lose in the areas of Linux, programming, web development, and others. I don’t need to be a leading expert in every technical field to do my job well and have a fulfilling career. I also decided to lay off on video games and learning the guitar so that I could focus on learning Spanish. Ministry to the Hispanic community is a driving passion in my life and it became clear to me that these other hobbies were eating up the time and mental energy that I needed to devote to Spanish and Bible study.

I want to be careful not to portray the wrong idea. I’m all for challenging yourself in new areas and having diverse interests. It’s all too easy to get into a rut, which leads to other issues. There will be a day when I pick up the guitar again or study programming in C#, but today is not that day. If I master Spanish and therefore complete a critical piece of a primary goal, then I’ll move on to music. The point is to be aware of what you’re going after in life and to make sure you’re not sabotaging those goals by splitting yourself in too many directions at once.

There is another facet of giving yourself permission to lose that has more to do with humility. Sometimes we become overly aggressive/competitive in our desire to be the best. Or, even worse, we don’t even jump in because we’re too afraid that we’ll do badly. Giving yourself permission to lose up front frees you to both enter the activity and enjoy honing your skill in it, regardless of what the people around you do. Decide up front that even if you look like an idiot you’re going to do it anyway. And once you move past there and begin to develop some skill (and you will), do it in humility. Make your goal to be the best you can, not better than others. And help develop others along the way, even if that means they become better than you.

Get out there in life, stay focused on what is important, and enjoy giving it 100%. Set yourself up to win in what’s important by giving yourself permission to lose.

Unlocking Liberty

This week a US judge asked Apple Inc. to help them unlock the iPhone that belonged to one of the San Bernardino shooters. The CEO of Apple, Tim Cook, has resisted this order citing risk to his customers and implications that extend past the case itself. Since then, notable people have stood up in defense of each side. On the one hand some say that not unlocking the phone is helping terrorists. The other side says that this sets a dangerous precedent, and that the FBI is just using one case to open the door for unlocking any device they want in the future. I don’t claim to be an expert on security, government, or terrorism. However, there are a few things I’d like ‘we the people’ to keep in mind as this discussion unfolds.

  1. The government does not have a good track record of using their abilities in a limited scope. Recall if you will the Patriot Act. Many people were okay with it initially because of its promise to aide in stopping the terrorist threat. However, years later we know that it’s been used to access innocent citizens’ information on disturbing levels and has been active past the time it was intended for. I want to hope that our government would not use device access methods maliciously, but I don’t think any of us are naïve enough to rule out the possibility.
  2. It’s unnecessary to force Apple’s hand. If any organization in the world should be able to crack an iPhone it’s the FBI. They have some of the best tech minds in the country, one of the most technically developed countries in the world, at their disposal. Aside from that, there are independent hackers available for hire. John McAfee , creator of McAfee antivirus, recently volunteered to do this for free. That would allow us to access the data without setting a legal precedent that allows the government to force companies into unlocking customers’ devices.
  3. We’re slipping into a dangerous mindset where anything is acceptable if there is a remote possibility of catching terrorists. Not that we shouldn’t be vigilant about tracking terrorists and securing the country, but at what point are we doing ourselves more harm than good? If we continue down the trail of sacrificing liberty for security then we’ve already accomplished the terrorists’ goals for them. They will have defeated the American spirit and caused us to relinquish ourselves to servitude. Not servitude to an oppressive regime they placed on us, but one we created ourselves, driven by our fear of them. Also, on a more conspiratorial note, we can’t assume that ill-intentioned individuals or organizations wouldn’t use that fear as a carrot to drive us farther and farther down a path that releases liberty and gives them power. The use of terrorism seems increasingly like the wars of George Orwell’s ‘1984’. Is it Eurasia or Eastasia that we’re fighting now? I can never keep track.

In summary, there will be innumerable situations through the years where the question of security vs liberty will be raised. Don’t be quick to assume that everything marketed as anti-terrorism is pro-American, and when in doubt err on the side of liberty.

The Price of Free

When looking at which tech toys to use it’s easy to differentiate by the upfront cost. An iPad, for example, can be twice the price of some of its competitors. This can certainly be prohibitive, no matter the quality of the device. I very much enjoy my Macbook Pro, but I would not be using one if work had not provided it for me. For many people it’s just not feasible to pay such a large cash amount upfront, and understandably so.

But is the monetary cost of the device itself the only consideration? Maybe we should be asking how or why other companies offer theirs for less. For the purpose of this article I’m going to pick on Google simply because they are the largest company utilizing a competing model. They are certainly not the only ones but have arguably been the most successful at it. The model I’m speaking of is that of advertising. Every time you go to their website Google displays tiny advertisements that other companies have paid large amounts of money to have placed there. But we all know that the most effective advertising is targeted advertising, getting exactly the right ad to exactly the right person. This is where the rest of Google’s services come in. By providing a litany of well-built, completely free services Google invites thousands of users to connect to their systems. While doing this, they gain mountains of data about you, your interests, your social circle, and their interests. This allows them to create increasingly personalized advertisements for you and cash in with companies who want you to see those advertisements.

Bringing it back to the devices, when you purchase an Android tablet or phone the cash price is usually going to be lower up front. However, it is geared toward Google services (rightly so). Most people will use the built-in apps provided by Google and, in doing so, send large amounts of data about themselves to Google over time. This can be in the form of email (Gmail), IM’s (Hangouts), Movies/Music/TV (Play Store), location data (Google Maps), etc. It’s likely few if any of us realize the extent of the data that we send them. It’s all built on the foundation of advertising so it all works toward fueling that end. On the flip side, companies like Apple have a more traditional business model. You pay a price for a product. Yes the monetary cost is higher, but it’s the entire cost up front. They do utilize cloud services and have you input your data to use various features. But the data is collected in order to allow you to use those services, not to assist advertisers.

Don’t get me wrong, it isn’t like Google is hiding anything. They offer a free service or low-cost device and in return they get to use your data to target ads towards you. It’s all laid out from the start in their user agreements. The problem is that none of us really read those things. We just click through the agreement in a rush to see what cool features are in store for us. So, the takeaway here is just to be mindful. Nothing in this world is free, especially when it is made by a for-profit company. If you have privacy concerns then take a step back and evaluate what information you’re giving over for the services you use. If you’re happy with the return you get from those services and the more relevant advertisements that result from your data being used, then please continue to happily use their services and products. Just never assume that the only cost of your device was the cash value.

Disclaimer: This post was written using Google Chrome on an Apple Macbook Pro.

Love God and Love People

There are a multitude of verses in the Bible and endless topics of conversation that can be drawn from them. However, we often forget that complexity is not the point. There is a simple message that ties it all together: love God and love people. Below I have recorded the verses that, to me, sum up God’s heart and His intention for our lives. These are the simple guide points that direct my life. They by no means detail every responsibility of a believer, but rather provide the overarching themes under which those finer details are to be carried out.

Matthew 22:37-40

37 Jesus answered, “‘Love the Lord your God with all your heart, all your soul, and all your mind.’ 38 This is the first and most important command. 39 And the second command is like the first: ‘Love your neighbor as you love yourself. 40 All the law and the writings of the prophets depend on these two commands.”

James 1:27

27 Religion that God accepts as pure and without fault is this: caring for orphans or widows who need help, and keeping yourself free from the world’s evil influence.

Consider It Pure Joy

Consider it pure joy, my brothers and sisters, whenever you face trials of many kinds, because you know that the testing of your faith produces perseverance. Let perseverance finish its work so that you may be mature and complete, not lacking anything.” (James 1:2-4; NIV)

What on earth is James talking about? Consider it joy when you encounter trials? I think most of us would agree that trials are something to be avoided. They are certainly not something to accept, much less be joyful about. This flies in the face of everything our culture believes. Many of our actions are built upon the idea that if we work hard, do good, and are smart then trials in life can be avoided, and they should be.

The problem is that our focus is in the wrong place. This life we live, and the circumstances we encounter along the journey, aren’t about us. It’s about God, His Kingdom, and His purposes. When we encounter trouble, discouragement sets in because the life we had planned is interrupted. We mourn over the loss of comfort, peace, stability, or gain. On the contrary, God says that losing this life for Him is gain (Matthew 16:25). His perspective is an eternal one, knowing that our time in this life is very short and that its purpose is to prepare us for a much longer time in His presence.

Knowing that, James’ words start to make a little more sense. When we encounter these trials, our flesh (the part of us bound to this world) suffers. During that time, if we place our faith in Jesus for who He is and what He has promised, our faith grows. Not only that, but as the flesh decreases in its dominance over us the Spirit increases.  The affect of that might seem quite small at first, but don’t miss the power. Believers who have begun surrendering to Jesus in their trials can testify to the outcome James points to. When once you’ve made it through a trial and allowed God to use it in your life for His purposes, you know that you can take on the next one. And when the next one comes (and it will) you’re looking forward to what God is going to do through it. So as time goes on trials are transformed from something that you once dreaded into something that you EMBRACE. Now perseverance has started forming in you. Those petty fears that kept you from serving the Lord in this area or that melt away. Not only are you no longer afraid of the circumstances surrounding that decision but  you have JOY, knowing that God is going to use them in you and those around you.

Let me be careful to point out that this is not some natural tolerance or toughness you build as a result of going through difficulty. You don’t have to be saved to be tough. In fact, sometimes that’s a sign of drifting away from the Lord because we’ve come to rely on ourselves instead of God. In contrast, this is all about surrendering to God and letting Him work, not making it about our actions. We recognize that we are weak and rejoice in the fact that our weakness is used to bring Him glory (2 Corinthians 12:9).

Can you imagine the Christian who is not deterred by trials but rather fueled by them? Who doesn’t grow weaker as they endure life but stronger? Trials are guaranteed in life, no matter how much wealth, health, power, or privilege you have. The only question is whether you’ll allow them to drive you further into the flesh as Satan intends, or allow God to produce perseverance in you as He intends, making you “…mature and complete, not lacking anything.”

Is Optimizing for AdHoc Queries Worth It?

As you probably know, there is a server configuration option called “optimize for ad hoc workloads”. For more details on that option check out the MSDN article. The real question is, how do I know the extent to which my environment uses AdHoc queries? Well, as usual there is a DMV for that. Use the query below to get an idea of how many plan caches are being stored but only used once. The number changes often, so it’s a good idea to baseline your typical number. For instance, I’ve created a SQL Agent job to record the number to a table every ten minutes.  If you maintain high numbers then it might be a good idea to optimize for ad hoc workloads. As always, however, I would advise testing thoroughly first. Happy hunting.

USE Diagnostics

INSERT INTO Diagnostics.dbo.AdHocTracking
	SELECT COUNT (*) FROM sys.dm_exec_cached_plans
	WHERE objtype = 'Adhoc'
	AND usecounts = '1'

Credit to the Red Gate blog for my initial background information.

Managing CDC Jobs on an Availability Group

So, here’s the deal. You’ve got Change Data Capture going in your environment and want to move your database to an Availability Group on SQL Server 2012. That’s great, lots of advantages there. But what happens to CDC when your database fails over to a different node? Since the capture process runs through SQL Agent jobs you will no longer be getting the CDC information post-failover. This is the scenario I ran into recently. In order to mitigate the problem I’ve created a simple script to handle the enabling and disabling of jobs on both replicas. Of course, the jobs will need to be created on each replica before it will work. But this is an easy way of making sure you can take advantage of the benefits of AG’s while still keeping up your CDC captures. I have this running every five minutes on each node. Feel free to adapt/improve/delete it to your heart’s content. Note that I have hard-coded the replica_id. I know, not the most elegant method. But good enough for a first version.

****Update****

Through painful experience I’ve discovered that the replica id can change if you remove the replica and add it back in. A slightly better method of checking utilizes sys.dm_hadr_availability_group_states. In this case we’re checking against the instance name, which will always remain the same.

IF (select primary_replica from sys.dm_hadr_availability_group_states) = ‘YourReplicaName’

****Update 2****

For SQL Server 2014 and above you are not allowed to even run the USE statement against a database that is on a read-only replica (ie your secondary replica used for reporting). For that reason I’ve altered it to use sp_start_job instead. The updated version below also simplifies other syntax a bit.

--Check to see if this instance is the Primary Replica.
IF (select primary_replica from sys.dm_hadr_availability_group_states) = 'YourReplicaName'
	BEGIN
		--If this is the Primary and the capture job is disabled, enable and start it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_capture') = '0'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_capture', @enabled = 1;
				EXEC msdb.dbo.sp_start_job N'cdc.YourJobName_capture' ;  
			END
		--If this is the Primary and the cleanup job is disabled, enable it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_cleanup') = '0'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_cleanup', @enabled = 1;
			END
	END
--Actions to take if this instance is not the Primary Replica.
ELSE
	BEGIN
		--If this is not the Primary and the capture job is enabled, disable it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_capture') = '1'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_capture', @enabled = 0;
			END
		--If this is not the Primary and the capture job is enabled, disable it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_cleanup') = '1'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_cleanup', @enabled = 0;
			END
	END

****Update 3****

Check out this post to learn about firing off jobs based on SQL alerts. This would be a better approach than checking on a timed interval.