Exporting and Re-deploying SQL Server 2008 Maintenance Plans

Challenge: There’s lots of chatter out there about working around various issues related to pulling an SSIS package out of an initial definition server and then reinstalling it on other servers.  For example:

INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.

The following scripts are my effort to bundle all the steps into something that allows me to forget about the messy details.
For me this comes in handy for managing a standardized maintenance plan on many identical database instances... e.g. a bunch of replication subscribers.
Notes:

• Full blown BIDS/SSIS vs Maintenance Plan wizard - My needs were met by the basic Maintenance Plan facility under the “Management” node in SQL Server Management Studio (SSMS).  From what I understand, this supports a subset of the full SQL Server Integration Services (SSIS) functionality.  Therefore, there is a lot of chatter about mixing and matching the two, with care.  In my experience, once I had loaded my plan in BIDS (Business Intelligence Development Studio), attempted any modification and then tried to execute the corresponding DTSX back under Maintenance Plans, it was no longer compatible with that execution context and I simply didn’t try to decipher the errors any further.
• I found that it’s convenient to have a “staging” server with identical SQL Server instance and database to what you care to redeploy to… therefore the maintenance plan you export is largely ready to go as-is.
• One manual DTSX tweak that remains necessary is to delete the encrypted password node and add the password directly to the connection strings… I believe the encryption is tied to a machine based key and therefore isn’t portable but I could be wrong about that.
• I’m using SQL Server 2008 R2.  I’m assuming everything is 2008 “R1” compatible and could very well work on SQL Server 2005 but I haven’t tried it.

Here’s some working code.

• All these scripts depend on environment variables SQLCMDUSER & SQLCMDPASSWORD being defined
• export_dtsx.cmd – Batch file which pulls the maintenance plan out of SQL Server to a local .DTSX file
• upload_dtsx.ps1 – PowerShell script which uploads the specified .dtsx file to the specified server.  Most significantly, this logic handles pulling the pertinent GUID’s out of the dtsx XML which are required to create the corresponding scheduled job records.
• create_maint_plan_job.sql – upload_dtsx.ps1 execute this script to create the SQL Agent scheduled job corresponding to the maintenance plan… the current parameters establish an every night, midnight run.  To determine representative arguments for your own preferred schedule, simply schedule a dummy job and “Generate Script” for that via SSMS.

SQL Server – [SOLVED] “Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate."

Nutshell solution: Generate a self-signed certificate and plug it into SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for {instance_name} > Properties > Certificate > Certificate

I read through dozens of folks wrestling with this issue and various suggested remedies (reinstalling, sysprep, etc) yet I found none addressing the certificate error directly in this way … so it seems worth getting this message out there.

Cause: For me, my SQL Server 2008 R2 (v10.50.1600.1) install went wonky (ran into some group policy brick walls) and somehow the default self-signed certificate must've gotten wiped out.

Interestingly, on other servers where the install ran without issue, this certificate entry is also blank… so that tells me we’re fortunate SQL Server is able to utilize this new one we throw in.

One straightforward way to generate self-signed certs is with "SelfSSL.exe" from the IIS 6.0 Resource Kit Tools. Here's example command line usage:
             SelfSSL /N:CN={database server name} /V:1999999

• If you're not familiar with certs, the name must match the name of the machine exactly, or it won’t show up in SQL Server Configuration Manager’s certificate drop down list!… this name should be the “FQDN” (Fully Qualified Domain Name)… typically the “Full Computer Name” as listed under Control Panel > System.
• The /v option is the #days the cert is valid for... through empirical study I believe 1999999 is the max allowed… that currently pushes expiration out to the year 7487, which will hopefully last ya ;)
• SelfSSL often spews “Error opening metabase: 0x80040154” … This would probably be bad news if you wanted to use this certificate for IIS SSL but apparently it’s not a factor for SQL Server SSL.
• One can examine the certificates that have been generated this way by launching mmc.exe, adding the Certificates snap in, selecting “Computer account” and looking at the “Personal” certificate store.

In a blatant attempt to cast a wide net on search hits , here’s a typical log that’ll be spewed along with the aforementioned error:
2012-02-10 09:57:09.07 Server      Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.
2012-02-10 09:57:09.08 Server      Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2012-02-10 09:57:09.08 Server      Error: 17182, Severity: 16, State: 1.
2012-02-10 09:57:09.08 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or prope
rty.
2012-02-10 09:57:09.08 Server      Error: 17182, Severity: 16, State: 1.
2012-02-10 09:57:09.08 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for
previous errors. Cannot find object or property.
2012-02-10 09:57:09.09 Server      Error: 17826, Severity: 18, State: 3.
2012-02-10 09:57:09.09 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately pr
eceding this one in the error log.
2012-02-10 09:57:09.09 Server      Error: 17120, Severity: 16, State: 1.
2012-02-10 09:57:09.09 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related proble
ms.

Amiga – Future Tank game (Tron Font)

Latest Tron movie triggered some old brain cells since the title font is similar.

Yeah, I know, seriously stuck in the past.

 

   

Fantastic ORM details

http://samsaffron.com/archive/2011/03/30/How+I+learned+to+stop+worrying+and+write+my+own+ORM

Excel – Select (Delete) Blank Rows

From here: http://www.theexceladdict.com/_t/t031008.htm

Reflecting Microsoft’s current button naming in Excel 2010…

• Select a column possessing representative blanks, from top to bottom.
• F5.
• “Special…” button - here's where the magic happens...
• “Blanks” radio button.
• “Delete” ribbon button/menu (under Home tab) – “delete sheet rows” menu item.

Windows 8 – my initial perspectives

Decent developer oriented introduction to the new Win8 app dev paradigm called “Metro”: “Metro style apps using XAML: what you need to know” (by Joe Stegman, Group Program Manager for the Windows XAML team at the Microsoft //BUILD/ Conference, Sep.2011, Anaheim CA)

• "Metro" is the name they're putting on building applications that embrace the touch/gesture/handheld era we're in now.
• MS marketing is drawing a clear distinction between this and the "old school" "productivity" oriented desktop apps... one key pattern they're throwing around to distinguish the two is that these kinds of apps are highly CONTENT focused … raw content consumes the screen real-estate, traditional buttons (aka “chrome”) are hidden... gestures rule... the ubiquitous demo is the gesture-nav photo album... thumbnails are all you see to start... you flip around in the gallery with touch gestures... drill into images with another gesture, yada yada
• XAML still seems to be an active technology (which is a relief since i spent the last two years self educating there... I must say that if nothing else, databinding in XAML is definitely a strong evolution... i've already seen evidence of frameworks coming up after taking XAML as the current pinnacle from which to consider improvement)
• HTML5 + JavaScript looks to be the new UI go-to - which is interesting and wasn't discussed so i still have a mental gap about what that really means.
• significant: the .Net framework is now "native" and it's morphed into "WinRT" (Windows Runtime)... i.e. these APIs are now bundled directly within the OS just like we've been familiar with Win32 all these years... this was a very logical step and i'm glad they made it... but this leads to the next point...
• Metro/WinRT is Win8 only
  http://social.msdn.microsoft.com/Forums/en-US/windowsdeveloperpreviewgeneral/thread/d4850eb7-5fb2-45b6-9e89-cd13056c4797
  this will create some inevitable app fragmentation in the medium term... yet another choice for the harried mobile developer... such is progress... on a personal level i'm not really worried about it... i like seeing the upgrades... pretty much at all costs.
• the demo showed that basic Silverlight/WPF XAML syntax is mostly cut/paste compatible in "Metro" XAML app.
  The basic development approach of Metro is the same as SL/WPF.
• But you can tell from the tone, that there have been some breaking tweaks...
• and of course there's outright new controls to get cozy in this new content/gesture focused paradigm.
• i liked the part of the demo where he showed how trivial it was to slap on some new "flipview" type controls that handle all the momentum style scrolling where the scroll naturally speeds up, slows down and jiggles when it stops... we don't have to code that... we just drop the equivalent of a fancy listbox on the UI, bind it to a list of data and it does all that fancy footwork... that's pretty cool.
• One biggish thing they mention is that *everything* is now async... i know SL was already pretty much that way anyway... in WPF you had a choice... the basic motivation is that it leaves the UI responsive... i.e. no longer possible to create an app where the Window locks up and greys out with "(Not Responding)"... i agree that this is a good thing overall... i've been coding this way and there's some nice language facilities in C# that make it not that much different from sync programming from a code readability and maintenance standpoint (e.g. inline anonymous functions passed to an async context).
  
• of course, all this goes back to whether you think you need to tie yourself to a Windows platform in the first place... there's still pure web driven apps via HTML5, etc representing a strong viable option.


• from a consumer standpoint, it'll be cool to have a solid tablet oriented flavor of Windows knocking heads with the Androids and iPhones out there.
• Windows 8 of course fires up with the Windows 7 Phone style "tile" based "home page".
• And the familiar old Windows desktop/Start Bar is just a flip away if you need that.
• they're saying Win8 will run on both ARM and Intel so the whole mobile hardware spectrum is fair game.
• after a quick scan, I'm not the only one thinking about Win8 on a contemporary Samsung Galaxy tab.

Wading into MVVM

1. Commands rather than Events
• instead of event handlers, think in terms of firing commands that will find their way to the corresponding command properties declared on ViewModels
• i.e. use Command attribute of XAML widgets rather than Click event handler
• MVVM popularized class RelayCommand works well as a lightweight ICommand implementation to use on ViewModels
• or barring that, use the Expression Blend Interactivity DLL to map click events to Model object methods via "CallMethodAction" behavior