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.
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;
Summary
3 things here.
You now know how I turn my computer off all the time
It’s amazing what you can do with Powershell, and
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.
No matter who wins Powershell or T-SQL, the GUI loses!
It’s T-SQL Tuesday time!
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.
Scripts
Unfortunately, in this case, the scripts were only partially useful.
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…
but nothing I could find helped me with the create commands.
These create commands are kinda important when you think about what they do.
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:
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("Microsoft.SqlServer.Rmo")
# Clear screen before each run
Clear-Host;
# Connect to the server.
$servername = "insert server here"
$repserver = New-Object "Microsoft.SqlServer.Replication.ReplicationServer"
$srv = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $servername
$srv.Connect()
$repserver.ConnectionContext = $srv
# Connect to the database
$databasename = "insert database here"
$repdb = $repserver.ReplicationDatabases[$databasename]
# Connect to the publication.
$publicationname = "insert publication here"
$publicationobject = $repdb.TransPublications[$publicationname]
<#
# Everything (troubleshooting)
$publicationobject.TransArticles | Where-Object SourceObjectName -EQ $article
#>
# Get everything. (from here on out, it's Butcher town :-( )
$Schoptions = ($publicationobject.TransArticles | Select-Object SourceObjectName, SchemaOption, PreCreationMethod )
$Schoptions `
| ForEach-Object `
{ `
$NewLine = "`n"
$WorkOnNow = $_.SourceObjectName
# Get SchemaOptions details.
$Schoptions = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object SchemaOption | Format-List | Out-string )
$schemaoptions2 = (($Schoptions -split ", ").Trim() ) -csplit "SchemaOption : "
$OptFormatted = ($schemaoptions2 | Where-Object {$_ -ne ""} | Where-Object {$_ -ne "PrimaryObject"} `
| ForEach-Object -Process `
{
Switch ($_)
{
"Identity" {"Identity columns are scripted using the IDENTITY property`t:`tTrue"}
"KeepTimestamp" {"Convert TIMESTAMP to BINARY`t:`tFalse"}
"ClusteredIndexes" {"Copy clustered index`t:`tTrue"}
"DriPrimaryKey" {"Copy primary key constraints`t:`tTrue"}
"Collation" {"Copy collation`t:`tTrue"}
"DriUniqueKeys" {"Copy unique key constraints`t:`tTrue"}
"MarkReplicatedCheckConstraintsAsNotForReplication" {"Copy check constraints`t:`tFalse"}
"MarkReplicatedForeignKeyConstraintsAsNotForReplication" {"Copy foreign key constraints`t:`tFalse"}
"Schema" {"Create schemas at Subscriber`t:`tTrue"}
"Permissions" {"Copy permissions `t : `t True"}
"CustomProcedures" {"Copy INSERT, UPDATE and DELETE stored procedures`t:`tTrue"}
default {"Extras present, please check"}
}
})
# Get PreCreationMethod details.
$CreationMethod = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object PreCreationMethod | Format-List | Out-String)
$CreationMethod2 = (($CreationMethod -split ":").Trim() | Where-Object {$_ -ne ""} | Where-Object {$_ -ne "PreCreationMethod"} `
| ForEach-Object -Process `
{
Switch ($_)
{
"None" {"Action if name is in use `t : `t Keep existing object unchanged"}
"delete" {"Action if name is in use `t : `t Delete data. If article has a row filter, delete only data that matches the filter"}
"drop" {"Action if name is in use `t : `t Drop existing object and create a new one"}
"truncate" {"Action if name is in use `t : `t Truncate all data in the existing object"}
default {"Error! Creation Method Switch has failed"}
}
})
#Report the details.
$NewLine
$WorkOnNow
Write-Host '----------'
$OptFormatted
$CreationMethod2
$NewLine
}