Thursday, February 16, 2017

[Forthcoming] Doing it wrong - Hekaton.

Okay, I've got an idea for a series of blog posts.  I've recently implemented In-Memory OLTP (IMOLTP, which I will now start pronouncing Im-Ho-Tep), and learned a bunch in a short period of time.  Don't do it like me.  Do it better.

Many thanks to Ned Otter for helping out a ton during this time.



Create in-memory FG
Create in-memory table
Create second in-memory FG
Create/destroy to new name
Stored procedure not working - still needs to only be in THAT database. No cross-DB work in SPs!
Resource issues!  (show event log)
Missing inserts copying from _live to _main? Why? (all about the COMMITs)
Answer: flip the order we get the IDs.
Pare down for now so we don’t run out of memory
DBCC caused problems with memory
Identity value on both table – have to monitor
Resource governor to the rescue?
 (I did this part, but remember to take the DB offline/online and sweat while it takes forever to come up)
Rebalance the In-memory filegroups if you have older data in there!

[Powershell] scripting out a servers via Red-Gate's SQL Compare

Grant Fritchey posted a basic powershell script that uses Red-Gate's SQL Compare to script out a database (http://www.scarydba.com/2011/01/31/powershell-to-automate-sql-compare/).  When I did it, it skipped partitioning and compression, so I wanted to put down my actual script somewhere for the next time I need this.  Specifically, "/options:none", so everything gets included.

Now that I've done it, I'm looking at the resulting folder structure - which doesn't match the structure I already have in place, using a different script (PoSH + SMO).  But I've written it, so here you go.


set-Location "c:\Program Files (x86)\Red Gate\SQL Compare 12\";
. C:\powershell_scripts\invoke-sqlcmd2.ps1

$Servers = "ser-ver-a","ser-ver-b"
$Path = "C:\temp\decom_server_scripting_via_redgate"

foreach ($server in $servers){
Invoke-Sqlcmd2 -Query "sp_databases" -ServerInstance $Server | ForEach-Object {Invoke-Expression "./sqlcompare.exe /s1:$Server /db1:$($_.DATABASE_NAME) /mkscr:$($Path)\$($Server)\$($_.DATABASE_NAME) /options:none" }
}