One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Monday, May 23, 2016

How Do I Change My Domain Password on Windows Server 2012?

As a DBA I spend a lot of time in RDP sessions, both to SQL Servers and to "jump"/pass-through servers on client domains.  Most clients (unfortunately not all of them - security is important, people!) have some variant of password expiration in their domains requiring regular password changes every 30/90/180 days.

Prior to Windows Server 2012, this was relatively straightforward - in the Start Menu select "Windows Security" (sometime hidden under Administrative Tools>>Windows Security):


...which then gives you a friendly menu where you can choose to "Change A Password":



Easy right?

** Often unknown tip - from the Change Password Prompt:



You can edit the top line to any account to which you have access - your accounts in other domains (assuming there is access to domain controllers in the other domain) or even other accounts altogether!  Even though I am logged into the above server as DOMAIN\agalbraith, I could modify the line to change the password for SOMEOTHERDOMAIN\agalbraith or DOMAIN\SQLServiceAccount,

--

The catch to all of this is that in Windows Server 2012, the easy method...went away.  How could they do that???

https://themuseletter.files.wordpress.com/2014/11/61225_bill-gates-shrug.jpg

The Windows Security box is Dead....Long Live the Windows Security box!

--

Here are three different ways to get to the same screen in Windows Server 2012.  These three methods work in Windows Server 2008 and 2008 R2 as well, so once you get used to one of them you can use it on your old servers as well.

http://cache.gawker.com/assets/images/gizmodo/2009/08/old_pc.jpg
Maybe not *that* old....

--

The first method is the one I have known for a long time, and is very simple.  Instead of CTRL-ALT-DEL, use CTRL-ALT-END.  Most of the time, this takes you to the same prompt screen as we saw above:


--

The second method is a little more involved, but useful - and I have been in at least three situations where I *had* to use it - once when there was a keyboard mapping error in the RDP session (something I have only ever experienced once) and twice where I was several layers deep in RDP (RDP to RDP to RDP).  I found this method at http://www.tomshardware.com/answers/id-1629393/change-password-ctrl-alt-del-rdp-rdp.html.

From a command prompt, type osk to bring up the On-Screen Keyboard (something I didn't know even existed at the time):


With the OSK up, press CTRL and ALT on your actual physical keyboard, and then click DEL on the OSK (CTRL-ALT-DEL all on the OSK just functions like a regular CTRL-ALT-DEL):



BOOYAH!

--

The third method was recently offered up by a member of the team here at Ntirety, Mike Skaff.  It is one more example of #YouCanDoAnythingWithPowerShell.

I don't know where Mike found it, but I was able to find references in a couple of places, including http://wiki.mundy.co/Change_password_on_Remote_Desktop_Server_2012

From a PowerShell prompt, enter the following:
(New-Object -COM Shell.Application).WindowsSecurity()

Sure enough:


Like the OSK method above, this PowerShell method works from RDP in RDP in RDP as well - and it's PowerShell!

--

Hope this helps!


Tuesday, May 17, 2016

Catching Up on The #SQLPASS Virtual Chapters

(Didn't realize until I was posting that this was my 100th post...whew!)

--

A few months ago we purchased a new "smart" Blu-Ray player and one of its intriguing features was its built-in WiFi with pre-installed Internet apps like Amazon Video, YouTube, Vudu, etc. (Previously we had a 6-year-old "dumb" DVD player that was barely smart enough to eject its tray when you pushed the button).

https://i.ytimg.com/vi/4pvHKG4VMDs/maxresdefault.jpg
Interestingly, the limitations of these pre-installed apps became one of my frustrations as we started to use the player more and more.  I wanted to be able to view Pluralsight videos and TED Talks, and my wife wanted to watch Craftsy videos, and lacking customized applications we couldn't do so.

http://www.famousmarketer.com/images/but_wait.jpg
After some searching on my laptop I found that TED Talks are available on YouTube!  Some more digging and I found a few PASS videos as well - and more intensive searching found that almost every single PASS Virtual Chapter has its own YouTube channel where they upload recordings of their presentations!

Add captionhttp://www.sqlpass.org/images/chapterlogos/logo_pass_vc.png

Below I have lifted the list of PASS Virtual Chapters (current as of May 2016) and the descriptions provided by PASS.  I have gone through YouTube and found the link for each chapter's YouTube channel and listed them below.

(I apologize to any international readers but I have omitted the non-English speaking chapters as I don't have the language skills to properly search for their content.)

Chapter Name
Short Description
Virtual Chapter URL
YouTube Recordings
Application Development
Training and information for Application Developers
Big Data
Discuss Big Data technologies & Hadoop-based systems
Business Analytics
The PASS Business Analytics Chapter provides virtual train...
Business Intelligence
Connecting BI Professionals globally
Cloud
Enabling cloud knowledge sharing
Data Architecture
Focusing on your data architecture concerns
Data Science
Advanced Analytics, Machine Learning, Data Mining
Database Administration
Forum for discussion on DBA topics
DBA Fundamentals
Rock solid foundations!
Excel Business Intelligence
Helping users achieve excellence in Excel
Healthcare
Connecting SQL Server Pros in the healthcare industry
High Availability and Disaster Recovery
Reduce the risk and impact of system faults and outages
Hybrid
Cover all technologies and solutions that may be integrate...
N/A
In Memory VC
Take advantage of the new In-Memory features
Performance
Discuss SQL Server performance-related content
PowerShell
Learn and share best practices around PowerShell
Professional Development
Join the conversation and share resources
Saturday Night SQL
Forum for discussion about BI and Databases
Security
Guidance and education on SQL Server security topics
Virtualization
Improving management of SQL Server in virtual environments
Women in Technology
Forum for discussion of issues pertinent to WIT

What I like to do is to "subscribe" to each of the relevant channels and that makes it easy for me to receive content on our Blu-Ray player (as well as on the YouTube app on my Android).

The Virtual Chapter websites listed above do have archives of the recording of their meetings as well, but they aren't centralized and they are only available from a regular web browser as opposed to a custom app on your TV or phone.

There are also several other useful  YouTube channels I recommend:

--

Can't beat free info - it's free, and it's info!

http://blogs.opentext.com/wp-content/uploads/2016.01-Iheartfreestuff-new-660px.jpg

--

Hope this helps!

Thursday, May 12, 2016

Upcoming SQL Saturdays - June 2016

I am speaking at two different SQL Saturdays in June!

--



First up on June 4th is SQL Saturday #517 Philadelphia (#SQLSAT517) - I am speaking at 2:45pm on "Getting Started with Extended Events."  This is quickly becoming my favorite of the talks that I give because I learn something new every time I present it:
Few subjects in Microsoft SQL Server inspire the same amount of Fear, Uncertainty, and Doubt (FUD) as Extended Events.  Many DBA's continue to use Profiler and SQL Trace even though they have been deprecated for years.  Why is this? 
Extended Events started out in SQL Server 2008 with no user interface and only a few voices in the community documenting the features as they found them.  Since then it has blossomed into a full feature of SQL Server and an amazingly low-impact replacement for Profiler and Trace. 
Come learn how to get started - the basics of sessions, events, actions, targets, packages, and more.  We will look at some base scenarios where Extended Events can be very useful as well as considering a few gotchas along the way.  You may never go back to Profiler again!
Register for SQL Saturday Philadelphia now (before it goes to wait-list!) at https://www.sqlsaturday.com/517/registernow.aspx!

I am also excited about this event because I will be attending Karen Lopez's (blog/@Datachick) Friday pre-con "Model-Driven Database Development: Physical to Implementation" - Karen is a very engaging and entertaining speaker who really knows her stuff.  There are still tickets available at https://www.eventbrite.com/e/model-driven-database-development-physical-to-implementation-karen-lopez-tickets-24628652964

 --


The following Saturday June 11th is SQL Saturday #523 - Iowa City (#SQLSatIowa) - I am up at 10am with "Does it Hurt When I Do This? Performing a SQL Server Health Check":
How often do you review your SQL Servers for basic security, maintenance, and performance issues?  Many of the servers I "inherit" as a managed services provider have quite a few gaping holes. It is not unusual to find databases that are never backed up, servers with constant login failures (is it an attack or a bad connection string?), and servers that need more RAM/CPU/etc. (or sometimes that even have too much!)  
Come learn how to use freely available tools from multiple layers of the SQL Server stack to check your servers for basic issues like missing backups and CheckDB as well as for more advanced issues like page life expectancy problems and improper indexing. If you are responsible in any way for a Microsoft SQL Server (DBA, Windows Admin, even a Developer) you will see value in this session!
This talk covers a wide array of tools to check out your servers, including the awesome DMV scripts from Glenn Berry (blog/@GlennAlanBerry) of SQLskills.

Register for SQL Saturday Iowa City now at https://www.sqlsaturday.com/523/registernow.aspx!

I am not headed to any pre-cons in Iowa City but there is an impressive line-up from many top speakers including my buddy David Klee (blog/@kleegeek) - he is covering "SQL Server Infrastructure Tuning for Availability and Performance" - tickets for his (and the other) pre-cons are available at http://www.eventbrite.com/e/sqlsaturday-iowa-city-2016-pre-conference-sessions-registration-24792389705?ref=ebtn

--

I have written multiple times about how amazing SQL Saturdays are as both training and networking opportunities.  If you are in the area either of these weekends come see me and check it out!

--

Hope this helps!



Monday, May 2, 2016

SQL 2016 - The Time Has Come!

Microsoft dropped it on us this morning:


https://media.makeameme.org/created/yes-finally.jpg

I read the article and was pleasantly surprised, although after some consideration it was more like this:

https://i.imgflip.com/4hu4p.jpg
If you haven't already started working with SQL Server 2016 this is definitely the time.  Here are some suggestions:

  • Get a copy of SQL Server Developer Edition - Microsoft told us at the end of March that Developer Edition is now free for SQL Server 2014, and that it will be continue to be free when it is released for SQL Server 2016 later this year.  As always Developer Edition is a fully-featured edition of the SQL Server product (with all of the same features as Enterprise Edition) that is intended for use in development and test environments and "and not for production environments or for use with production data" (a curve ball called out in the linked release article above that many people don't consider).  Even though SQL 2016 isn't out yet, prepare yourself by getting a free copy of 2014 Developer Edition now so that you are ready to move to 2016 when it comes out.
  • Attend SQL Saturday sessions - I have written before about how amazing SQL Saturdays are and the many reasons for taking part, and now the impending release of the new SQL Server 2016 product makes it even more important than usual!  I don't have any talks in my repertoire about the new version, but both of the SQL Saturdays I am scheduled to speak at in early June have multiple sessions about SQL Server 2016 (as do most other SQL Saturdays - check the full SQL Saturday schedule here):
  • Read Blogs and Whitepapers online
    • Microsoft has an array of whitepapers and demo videos for SQL Server 2016 available on their official 2016 site.  
    • Several major tools vendors have plenty of blogs about SQL Server 2016, especially SQL Sentry and Solarwinds.
    • The community has a wide array of blogs related to SQL Server 2016 - a great place to start is on SQLServerCentral
--

Get in front of this now - we can't all be like this:
http://cdn1-www.dogtime.com/assets/uploads/gallery/funny-dog-memes-part-4/funny-dog-meme-learning-tricks-doesnt-matter-when-youre-really-really-ridiculously-good-looking.jpg
--

Hope this helps!




Friday, April 22, 2016

Where Is My Primary Replica Again?

We have many clients with multi-node Availability Groups - that is, AGs with more than two replicas.  One of the problems I have always had with Availability Group management via the GUI (admit it, you use the GUI sometimes all you non-PowerShell geeks) is the fact that most of the work needs to be done from the primary replica.  You can connect to the Availability Group manager on a secondary replica, but you can only see limited data about that particular secondary replica and can't see much about the other replicas, including *which* replica is the current primary replica!

To perform management you almost always need to connect to the primary replica, but how can I figure out which one is primary without just connecting to the instances one by one until I get lucky?

https://cdn.meme.am/instances/500x/55766239.jpg
Enter the T-SQL:
SELECT
AG.name AS AvailabilityGroupName
, HAGS.primary_replica AS PrimaryReplicaName
, HARS.role_desc as LocalReplicaRoleDesc
, DRCS.database_name AS DatabaseName
, HDRS.synchronization_state_desc as SynchronizationStateDesc
, HDRS.is_suspended AS IsSuspended
, DRCS.is_database_joined AS IsJoined
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as HAGS
ON AG.group_id = HAGS.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS HARS
ON AR.replica_id = HARS.replica_id AND HARS.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS DRCS
ON HARS.replica_id = DRCS.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS HDRS
ON DRCS.replica_id = HDRS.replica_id
AND DRCS.group_database_id = HDRS.group_database_id
ORDER BY AG.name, DRCS.database_name
This query can be run on any of the replicas, and it will return information about the Availability Groups and their member databases, *including* the name of the primary replica instance!

http://vignette4.wikia.nocookie.net/randycunningham9thgradeninja/images/9/97/YES_cat.jpg/revision/latest?cb=20150330230809
Hope this helps!

Thursday, April 14, 2016

How Long Did That Job Run?

When you use the msdb tables to try to gather information on your SQL Server Agent jobs, inevitably you will end up in a situation like this:
SELECT TOP 10 
sj.name as JobName
,sjh.run_date as RunDate
,sjh.run_time as RunTime
,sjh.run_duration as Duration
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj
on sjh.job_id = sj.job_id
where step_id=0
JobName RunDate RunTime Duration
ServerA_RESTORE_FROM_PROD_V2 20140904 222821 4131
ServerA_RESTORE_FROM_PROD_V2 20140904 231032 3055
ServerA_RESTORE_FROM_PROD_V2 20140904 231130 5003
Bloomberg_Pkg 20140904 231233 205
ServerA_RESTORE_FROM_PROD_V2 20140904 231350 2343
DatabaseIntegrityCheck - SYSTEM_DATABASES 20140923 1500 433
DatabaseBackup - SYSTEM_DATABASES - FULL 20140923 10000 23
syspolicy_purge_history 20140923 20000 36
DatabaseBackup - USER_DATABASES - FULL 20140923 40000 2333
DatabaseIntegrityCheck - SYSTEM_DATABASES 20140924 1500 113
So....when did "DatabaseIntegrityCheck - SYSTEM_DATABASES" start? At 1500 - is that 3pm?  You may be able hash out that this translates to 12:15am local time...but what if you want to perform datetime-style math on the RunDate/RunTime?  Sure you can do multiple leaps to say (RunDate>X and RunDate<=Y) AND (RunTime>A and RunTime<=B), but you then need to explicitly format your X, Y, A, and B in the appropriate integer-style format.  Wouldn't it be easier to just be able to datetime math?

The next part is even worse - quick - how long did the first instance of "ServerA_RESTORE_FROM_PROD_V2" run?

4,131 somethings (seconds, ms, etc), right?

http://ct.fra.bz/ol/fz/sw/i51/5/7/25/frabz-no-191096.jpg
In truth, the job ran forty-one minutes and thirty-one seconds.

http://upandhumming.com/wp-content/uploads/2015/11/serious.jpg
Yes, yes I am.  No precision beyond seconds, and no quick way to do math.  if you want to figure out how much longer instance 1 of the job ran than instance 2, you have to do some serious goofiness to figure it out.

Here is the fix for both of these problems!

--

The first item is easy although not directly obvious without a little help from that old standby:

https://imgflip.com/i/12hu6g
(Yes I am that old...)

Microsoft included a system scalar function msdb.dbo.agent_datetime(run_date, run_time) that turns the combination  on run_date and run_time into a datetime:
SELECT TOP 10
sj.name as JobName
,sjh.run_date as RunDate
,sjh.run_time as RunTime
,msdb.dbo.agent_datetime(sjh.run_date,sjh.run_time) as RunDateTime
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj
on sjh.job_id = sj.job_id
where step_id=0
JobNameRunDateRunTimeRunDateTime
ServerA_RESTORE_FROM_PROD_V220140904222821
09/04/2014 22:28:21
ServerA_RESTORE_FROM_PROD_V220140904231032
09/04/2014 23:10:32
ServerA_RESTORE_FROM_PROD_V220140904231130
09/04/2014 23:11:30
Bloomberg_Pkg20140904231233
09/04/2014 23:12:33
ServerA_RESTORE_FROM_PROD_V220140904231350
09/04/2014 23:13:50
DatabaseIntegrityCheck - SYSTEM_DATABASES201409231500
09/23/2014 00:15:00
DatabaseBackup - SYSTEM_DATABASES - FULL2014092310000
09/23/2014 01:00:00
syspolicy_purge_history2014092320000
09/23/2014 02:00:00
DatabaseBackup - USER_DATABASES - FULL2014092340000
09/23/2014 04:00:00
DatabaseIntegrityCheck - SYSTEM_DATABASES201409241500
09/24/2014 00:15:00
I agree with the author of this post who calls the agent_datetime() function "undocumented" since there wasn't a record of it in Books Online - I checked around and couldn't find any standard documentation of it on MSDN or TechNet.

Now that we have a datetime, we can perform all of the regular datetime manipulation functions such as DATEDIFF() on the values.

--

The second part is a little more obnoxious - there isn't a quick Microsoft function (documented or otherwise) to make the run_duration into a process-ready value.

To hash the run_duration into a useful value I wrote a CASE statement some time ago:
SELECT TOP 10
sj.name as JobName
,CASE len(sjh.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(sjh.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(sjh.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(sjh.run_duration,3),1)
+':' + right(sjh.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(sjh.run_duration,4),2)
+':' + right(sjh.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(sjh.run_duration,5),1)
+':' + Left(right(sjh.run_duration,4),2)
+':' + right(sjh.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(sjh.run_duration,6),2)
+':' + Left(right(sjh.run_duration,4),2)
+':' + right(sjh.run_duration,2) as char (8))
END as 'Duration'

,run_duration
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj
on sjh.job_id = sj.job_id
where step_id=0
JobName Duration run_duration
ServerA_RESTORE_FROM_PROD_V2 00:41:31 4131
ServerA_RESTORE_FROM_PROD_V2 00:00:03 3
ServerA_RESTORE_FROM_PROD_V2 00:00:05 5
Bloomberg_Pkg 00:00:02 2
ServerA_RESTORE_FROM_PROD_V2 00:00:02 2
DatabaseIntegrityCheck - SYSTEM_DATABASES 00:00:43 43
DatabaseBackup - SYSTEM_DATABASES - FULL 00:00:02 2
syspolicy_purge_history 00:00:36 36
DatabaseBackup - USER_DATABASES - FULL 00:00:02 2
DatabaseIntegrityCheck - SYSTEM_DATABASES 00:00:11 11

Interestingly I recently discovered a more elegant solution (while looking for a answer to a different problem) that utilizes the STUFF() function.  Look at this forum post on SQLServerCentral.  The fourth item down from "Mudluck" is almost exactly the same as my CASE statement above, but look at the reply below it from "JG-324908":
SELECT stuff(stuff(replace(str(run_duration,6,0),' ','0'),3,0,':'),6,0,':') FROM sysJobHist
Sure enough, running this STUFF() code results in the same output as the much more complicated CASE statement above.  JG notes that he/she found it in a forum post somewhere and I dug around a little to see if I could find the original author without any luck :(

--

As with many other bits of code, I keep these things in a big NotePad file of useful code snippets (some people like OneNote instead, and there are dozens of choices - use the one you normally prefer) so that I can quickly reference them when needed.  I always note the website or forum post where I found the code if I didnt create it myself as well as the original author.  This lets me give credit where credit is due when showing the code to others, but it also gives me an idea of someone to approach in the future if I have a question about a similar topic.

It was especially interesting to find the STUFF() code because STUFF() isn't a function I often use, and in this case it was perfect.

--

Hope this helps!