Following on from my initial PowerShell post I decided to expand on that script.

The idea behind this PowerShell script was to obtain and display Windows Server information instantly. I was thinking along the lines of someone walking up to your cubicle and throwing any of the following questions at you:

  • How much disk space do we have on server xxx?
  • When was server xxx last rebooted?
  • Who are the local administrators on server xxx?
  • What shares exist on server xxx?
  • Etc

How quickly could you obtain these answers? How many different tools and utilities would you start firing up?  Would this result in a whirlwind of whirlwind of CTRL + TAB between various applications? The answers to the above questions (and more) could be obtained by using any of the following tools and utilities e.g. 

  • Windows Explorer
  • The Computer Management console
  • VMWare console
  • Event Viewer
  • SCOM
  • SSMS
  • Remote connecting to the server

More than likely you probably have many other scripts, tools or utilities that gather this information daily.  However, what I’m really trying to achieve is to answer all those questions in the minimum amount of key strokes and time. How about 4 keyboard strokes!! Yes, 4!

  1. Keyboard Windows Key (1 key stroke)
  2. Type ‘qc’ (2 key strokes) in the run/search box
  3. Press {enter}  (1 key stroke)

Now that’s fancy! Someone high up and important stands behind you and watches an old school DOS window popup, and all the server information stream out in the same time that it takes them to pull up and chair.

Shorterned example of the output:

PowerShell Quick Server Check

How to install

  1. Download these two files: (quickcheckBATcode) and (quickcheckPScode)
  2. Save the contents of [quickcheckBATfile.docx] into a [qc.bat] file within [c:\windows\system32] folder.
  3. Save the contents of [quickcheckPScode.docx] into a [QuickCheck.ps1] file within [c:\windows\system32] folder.

Note: It’s not best practice to save into this system folder, but we doing this to achieve the minimum number of key strokes as mentioned above. I’m also supplying word files as wordpress has restrictions on what file types can be uploaded. The bat file simply calls the PowerShell (ps1) file.

How to execute

  1. Keyboard Windows Key or click on the Windows Start Icon
  2. Type ‘qc’ in the run/search box and press {enter}
  3. Type in your servername at the prompt

QuickCheck v1 currently returns the following information:

  • Operating System
  • Server Specification
  • Current Disk Space
  • Last Reboot time
  • Network Shares
  • Services (running under a service account)
  • Services (set to automatic, but stopped)

Thanks for reading.

 

This is a follow on to my initial post which was a high-level overview of the SQL Server 2012 Always On feature.

Since then a project has come up which requires SQL Server Always On feature and SQL Server Master Data Services. In preparation for this task I started compiling high level steps on what would be involved when installing/configuring AlwaysOn.

Hopefully you’ll find it useful too.

Windows Failover Cluster

  • Install/configure Windows Failover Cluster feature on all nodes that will participate in AlwaysOn using Failover Cluster Manager
  • Add the nodes that will participate in the cluster
  • Provide Cluster name (windows name) and VIP – this creates DNS and AD objects.

Enable AlwaysOn in SQL Server Configuration Manager

  • SQL Server Configuration Manager – SQL Server properties – Notice the extra tab for AlwaysOn. The value here displays the Windows Cluster name. If the Windows cluster is not configured, it will mention this in the field. Check the AlwaysOn text box.

AlwaysOn

  • Repeat this on each node individually, or use SQL Powershell cmdlets from SSM.
[Enable -SqlAlwaysOn -ServerInstance xxx -Force]

Configure AlwaysOn

  • SSMS, AlwaysOn section, use the AlwaysOn wizard.
    • Create AlwaysOn Name
    • Select the databases to be part of AlwaysOn
    • Select Replicas, Automatic Failover, Synchronous commit, Read only on Secondary
    • Add other nodes, with same settings as above.
    • There are other tabs here, Endpoints, backup preferences and listener.
    • Select initial data synchronization – http://msdn.microsoft.com/en-us/library/hh231021.aspx
    • Allows you to script out process or let wizard do the work.

Simulating Failover

  • SSMS, Right click the AlwaysOn Availability group and select failover
  • Select instance to use as failover node.
  • Connect to that instance
  • A script can be generated at this point for this process or let the wizard action:
ALTER Availability group [xx] FAILOVER
  • Applications will fail / Clients disconnect – if specifying the instance name.
  • Challenge is to make applications failover seamlessly.
  • This is where we use the listener tab mentioned above.
  • Add the listener name.
  • This creates the AlwaysOn listener name which is the virtual name for the AlwaysOn group.
  • If the application or client is specifying this listener name then the failover will be seamless to the client, redirecting to the correct instance that’s hosting the primary replica.

Backups

 

Thanks for reading.

BCP – password prompt?

December 24, 2012

BCP is a very useful tool.

How and why we use it is explained in the following link and in BOL: http://msdn.microsoft.com/en-us/library/ms162802.aspx

I wanted to write about a small little issue that you may come across which can drive you up the wall. Below is a simple example, where I’ve launched the command prompt and run the following command which exports the contents of table testable to a csv file test.csv

bcp.exe TEST..TestTable out “d:\temp\test.csv” -c -t, -T -Slocalhost

BCP

Notice the password: prompt? Even though I’ve specified –T which tells the bcp utility to run using trusted authentication.

“- T specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.”– Microsoft

What password? I’m using the service account which is a local windows administrator and a system administrator in SQL Server and I’m exporting to a local folder – so what f@ck3n! Password! Turns out that even though it’s prompting for a password, the real problem is actually the path that you are executing bcp from, in my case: c:\documents and settings\serviceaccount\

Press Ctrl + C to abort the above BCP command:

BCP CTRL C

Type cd\ {enter}

and then change directory to the physical location of the bcp executable

cd program files\microsoft sql server\90\tools\binn

and now run the exact same command

bcp.exe TEST..TestTable out “d:\temp\test.csv” -c -t, -T –Slocalhost

BCP Success

Success!

It’s the kind of thing that can catch you out and waste more time that it really needs to. BCP is also a utility that you may not use very often so unexpected stumbling blocks like this can really stump you for a bit.

If you’re using the command prompt you may figure out the problem fairly quickly. However, in my case I was writing a stored procedure, using the code below, and when running the query it never completed or failed. It seemed to run forever. Sp_who2 showed no signs of blocking.

All along it was waiting for a password that would never be entered.

BCP T-SQL

Merry Christmas and thanks for reading!

Documentation = Learning

December 15, 2012

I came across this great article by John Sansom, and it got me thinking.

I personally think about documentation as a way of cementing what I’ve just installed, configured or developed. It helps me remember. So I document everything.

“If you can’t explain it simply, you don’t understand it well enough” – Albert Einstein.

I think of documentation in the same way.

We have a team wiki where all our documentation is saved and I pride myself in directing team members to docs that I’ve written. I use the level of follow up questions asked as an indicator on the quality of the material.

Quite simply, if there are too many questions, then it’s not good enough. It’s an easy way of learning and making sure you understand it as well as you think you do.

It’s quite similar to mentoring junior members of the team. Never shy away from the opportunity as it’s such a great learning resource. New members or juniors throw all sorts of questions at you and it really helps you revisit your theory, and areas that you’ve either forgotten about or simply not visited in a while.

Thanks for reading.

Added 19/02/2013 – Another great blog post by Argenis Fernandez related to documentation, focusing on using a Wiki. My comments below:

 A wiki is a great tool to store documentation. We currently use confluence which is easy to use and adopted well within the team – even for collueages who are not fond of documentation they quickly saw how easy it was to update and then how good the search utility is (searches within uploaded content too).

One downside (to add to Brent’s comment) is when we perform our yearly off-site DR test, this wiki server has to be built and recovered first (using printed off DR build documents), before we can recover any of our other servers.

In addition, we’ve implemented a script (vbs/wmi) that runs weekly, which grabs all prod server information and writes to idividual server text files, which are then imported into a infrastructure database. At a moments notice we can email a servername_date_config.txt file to whoever needs it – which is a lot fast than querying an infrastructure database and formatting the output for someone to read. 

Scripting really is the only way to ensure that your infrastructure database is up-to-date and correct. Humans are really good at forgetting to update. Great article!