SQL Server Configuration Manager: Where has it gone?

Why this blog post?

Now this is a short one.

A while back I was testing attempting to access SQL Server using a defined port number.

To do that, you have to access SQL Server Configuration Manager to specify the port number, or at least I think you do (If you don’t have to do it this way, please, oh please, let me know!).

So, since my laptop is running on Windows10, I open up the Start menu, type in “config” and…nothing!
No SQL Server Configuration Manager! The closest I got was the configuration manager for Reporting Server.

I’m shocked by this especially because when I type it into the Start menu now, I get…

sqlserverconfigurationmanager
Windows 10 making a liar out of me…

Ignoring the fact that it shows up in the result pane now, I had to go to MSDN and figure out where it’s default path is.

But WHY this blog post?

Well it turns out that, for me, the default path is now in…

C:\Windows\sysWOW64\SQLServerManager”<nn>”.msc

I say “<nn>” because it’s a number dependant on what version of SQL Server that you are running.
For example, I have a 2012, a 2014 and a 2016 version on my laptop so I have 3 versions of SQL Server Configuration Manager.

sqlserverconfigurationmanager_we
I may have been msc-taken, get it? 🙂

Seriously, is that it?

Nope, that ain’t it.

Opening up Windows Explorer, going all the way down to that level just to get the config manager? Ain’t nobody got time for that!

They say “imitation is the greatest form of flattery” so taking ideas garnered from dbatools and the fact that I’m just finished a pre-con for SQL Saturday Boston in PowerShell from Mike Fal ( b | t ), thank you Andy Mallon ( b | t ) for pushing me out of my comfort zone even if it was only to just sign up!, I’ve created a little PowerShell script to let me choose and open a SQL Server Configuration Manager.

Check it out! And let me know what you think.

Script me!

Get-ChildItem -Path C:\Windows\sysWOW64\ -Filter &quot;*SQLServerManager*&quot; |
Out-GridView -PassThru |
Invoke-Item

 

scriptme
Seriously “OutGridView -PassThru” should get an award!

Now, I only have 2014 service up on this time so when Out-GridView pops up, I’m going to choose SQLServerManager12.msc and click “Ok”

out-gridview
This doesn’t count as a GUI!

Which will open up our “missing” configuration manager!

SQLServerConfigurationManagerEnd.PNG
I may have just been doing something stupid though…

Exit:

PowerShell! Helping make my life easier since…whenever I actually figure it out. 😐

Why I Powershell my Laptop off

Could be my shortest blog post so far…

Intro

Kalen Delaney ( blog | twitter ) has an excellent blog post about Windows Fast Startup and, while I’m not going to repeat what she has said here because, like I already mentioned, it’s an excellent post and I encourage you to read it ( and maybe give her a lil’ subscribe 😉 ), what I will mention is that I encountered this feature with my new laptop and had it interfering with my SQL Server testing (again read her post as to possible causes why).

Using Powershell for documenting Replication had me wondering if there was a way I could get around this using Powershell. So while this is another post that is not about SQL Server, it is about Powershell.

Hey, at least I’m consistent in my consistencies.

What’s the Problem?

A quick lmgtfu, brought me to the following page and command:

shutdown /s

Which pops open a window saying the computer will shutdown and, after a delay, that’s what it does.

At this stage I’ve read enough documentation to know that
shutdown /s
doesn’t follow the standard Verb-Noun convention of Powershell and that delay was slightly annoying.

Plus, everyone raves about the Get-Help commandlet so I figured I would try that.

Get-Help *shutdown*

Gave me a list of commands and one of them seemed to fit what I wanted.

Get-Help Stop-Computer;

Powershell_stopcomputer

Summary

3 things here.

  1. You now know how I turn my computer off all the time
  2. It’s amazing what you can do with Powershell, and
  3. Kalen says

    So you might already know, but I didn’t know, until I learned it, of course.

I didn’t know, but found a work-around so didn’t learn it.
I’d advise you to follow Kalen’s approach (as I’m going to try from now on) but, hey, at least you now know mine.

T-SQL Tuesday #80 – Can Powershell Get What T-SQL Cannot?

No matter who wins Powershell or T-SQL, the GUI loses!

It’s T-SQL Tuesday time! tsql2sday-150x150

Chris Yates (blog | twitter) has given the T-SQL bloggers a “carte blanche” with regard to this month’s theme so even though this T-SQL Tuesday falls on his birthday, he’s the one giving us a gift (awfully nice of him I think).

So a white blank page to work with…in this case it seems only appropriate to write about Powershell. Mainly because if I were to write about it normally, all you would be getting is a white blank page. Basically, about Powershell, I don’t know much…

Therefore to start off this blog post, a little back story about why I’m talking about Powershell is appropriate…

Documenting Replication.

If you really want to get up to scratch with something that you are working with then you can’t go wrong with documenting it. Or at least that’s what my Senior DBA told me just before he went back to his laptop laughing maniacally.

So needing a high level documentation of the publications, articles and article properties of what we replicate, I turned to the only thing I knew at the time; the GUI.

GUI.

Now, due to an unfortunate incident when I was a Software Support Engineer that involved a 3 week old backup and a production database, I prefer to not to use the GUI if I can help it.

I’m not joking about that as well, if there is ANY way that I can accomplish something with scripts instead of the GUI, I will take it!

Especially when the need was to document the properties of over 100 articles, I was particularly not looking forward to opening the article properties window for each of the articles and copying them out individually.

Replication_ArticleProperty
100 X 40 = 4000 no thanks

 

Scripts

Unfortunately, in this case, the scripts were only partially useful.

Oh they were great for the publications

EXEC sys.sp_helppublication;

and to get the articles

EXEC sys.sp_helparticle @publication = publication_name;

but the article properties themselves remain elusive!

From BOL, the only way to actually interact with them seemed to be when you were creating the articles or if you wanted to change them, yet nothing for just viewing the states of them.

Finally after a lot of Google-fu, I managed to get most of the schema options with a good few temp tables and Bitwise operators

Replication_PreCreationCommand_SchemaOptions

but nothing I could find helped me with the create commands.

Replication_PreCreationCommand

These create commands are kinda important when you think about what they do.

Replication_PreCreationCommand_Options

Drop the object, truncate all data and the delete data. The delete data option is probably most dangerous if you have a row filter set up as you may not even be aware that data has been deleted until it’s too late and users are screaming at your door!

So in a blind fit of panic and a desperate attempt to thwart my GUI foe, I turned to Powershell.

Powershell

I was thankfully able to find an elegant, well-explained script by Anthony Brown and then proceeded to butcher it without remorse until it returned what I wanted.

I’ve included the full script at the end of this post with a few…shall we say…forewarnings.

The main point that I had to add was simply this:

PseudoCode:

For whatever article on now,
get the article properties
where the source article is what we’re looking for
return only the PrecreationCommands
formatted in a list
and returned in a string:


$publicationobject.TransArticles `
| Where-Object SourceObjectName -Like $WorkOnNow `
| Select-Object PreCreationMethod `
| Format-List `
| Out-String

Finally returning what I want, which is a simple copy and paste into the relevant section of a Word document

Replication_PreCreationCommand_Final

Time taken:

  • Powershell: 100 + articles all finished in around 4 seconds 🙂
  • GUI: 100+ articles hand typed out in a time I’d not like to figure out, plus however long I spent washing my hands afterwards.

Final Word

As I’ve said before

one of the best thing about SQL Server is, that for all it’s restrictive syntax and rules, there is no 1 way to do anything.

…and there is no excuse for relying on the GUI, unless you want to!
Powershell is an amazing tool to add to your belt and one that I’m definitely going to learn more about.

I challenge you to think about an aspect of your work that is not automated or for which you use the GUI for (shudder).

Now see if there’s a way around it…

Final Powershell Script

The following is the final script used to get the code. I make no apologies for it as I don’t know Powershell yet it’s served it’s purpose and then some. It has returned my creation commands, taught me some fundamentals of the language and ignited a desire to learn it.

However I do apologise for the look of the script. There is something configured with the blog that squashes the script and requires a scroller, I’m working on fixing it.

# Load the assembly needed. (Only required once at the start).
[System.Reflection.Assembly]::LoadWithPartialName(&quot;Microsoft.SqlServer.Rmo&quot;)
# Clear screen before each run
Clear-Host;

# Connect to the server.
$servername = &quot;insert server here&quot;
$repserver = New-Object &quot;Microsoft.SqlServer.Replication.ReplicationServer&quot;
$srv = New-Object &quot;Microsoft.SqlServer.Management.Common.ServerConnection&quot; $servername
$srv.Connect()
$repserver.ConnectionContext = $srv

# Connect to the database
$databasename = &quot;insert database here&quot;
$repdb = $repserver.ReplicationDatabases[$databasename]

# Connect to the publication.
$publicationname = &quot;insert publication here&quot;
$publicationobject = $repdb.TransPublications[$publicationname]

&lt;#
# Everything (troubleshooting)
$publicationobject.TransArticles | Where-Object SourceObjectName -EQ $article
#&gt;

# Get everything. (from here on out, it's Butcher town :-( )
$Schoptions = ($publicationobject.TransArticles | Select-Object SourceObjectName, SchemaOption, PreCreationMethod )
$Schoptions `
| ForEach-Object `
{ `
$NewLine = &quot;`n&quot;
$WorkOnNow = $_.SourceObjectName

# Get SchemaOptions details.
$Schoptions = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object SchemaOption | Format-List | Out-string )
$schemaoptions2 = (($Schoptions -split &quot;, &quot;).Trim() ) -csplit &quot;SchemaOption : &quot;
$OptFormatted = ($schemaoptions2 | Where-Object {$_ -ne &quot;&quot;} | Where-Object {$_ -ne &quot;PrimaryObject&quot;} `
| ForEach-Object -Process `
{
Switch ($_)
{
&quot;Identity&quot; {&quot;Identity columns are scripted using the IDENTITY property`t:`tTrue&quot;}
&quot;KeepTimestamp&quot; {&quot;Convert TIMESTAMP to BINARY`t:`tFalse&quot;}
&quot;ClusteredIndexes&quot; {&quot;Copy clustered index`t:`tTrue&quot;}
&quot;DriPrimaryKey&quot; {&quot;Copy primary key constraints`t:`tTrue&quot;}
&quot;Collation&quot; {&quot;Copy collation`t:`tTrue&quot;}
&quot;DriUniqueKeys&quot; {&quot;Copy unique key constraints`t:`tTrue&quot;}
&quot;MarkReplicatedCheckConstraintsAsNotForReplication&quot; {&quot;Copy check constraints`t:`tFalse&quot;}
&quot;MarkReplicatedForeignKeyConstraintsAsNotForReplication&quot; {&quot;Copy foreign key constraints`t:`tFalse&quot;}
&quot;Schema&quot; {&quot;Create schemas at Subscriber`t:`tTrue&quot;}
&quot;Permissions&quot; {&quot;Copy permissions `t : `t True&quot;}
&quot;CustomProcedures&quot; {&quot;Copy INSERT, UPDATE and DELETE stored procedures`t:`tTrue&quot;}
default {&quot;Extras present, please check&quot;}
}
})

# Get PreCreationMethod details.
$CreationMethod = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object PreCreationMethod | Format-List | Out-String)
$CreationMethod2 = (($CreationMethod -split &quot;:&quot;).Trim() | Where-Object {$_ -ne &quot;&quot;} | Where-Object {$_ -ne &quot;PreCreationMethod&quot;} `
| ForEach-Object -Process `
{
Switch ($_)
{
&quot;None&quot; {&quot;Action if name is in use `t : `t Keep existing object unchanged&quot;}
&quot;delete&quot; {&quot;Action if name is in use `t : `t Delete data. If article has a row filter, delete only data that matches the filter&quot;}
&quot;drop&quot; {&quot;Action if name is in use `t : `t Drop existing object and create a new one&quot;}
&quot;truncate&quot; {&quot;Action if name is in use `t : `t Truncate all data in the existing object&quot;}
default {&quot;Error! Creation Method Switch has failed&quot;}
}
})

#Report the details.
$NewLine
$WorkOnNow
Write-Host '----------'
$OptFormatted
$CreationMethod2
$NewLine
}