At my DAC talk at TechEd last week, the thing that seemed to cause the most interest was the PowerShell scripts to use DACPACs/BACPACs. I thought I'd post that here, along a question about cmdlets in general. Using PowerShell against the library allows admins to use all of the functionality that's in SSMS, with the options available in SqlPackage (think DacOptions object) and more (like BACPACs)… The premise is that administrators (DBAs or others) might want to integrate this functionality as part of their deployment into a PowerShell script.
PowerShell doesn't officially support .NET 4.0 assemblies until PowerShell 3.0, so you'll need a config file. You'd name this PowerShell.exe.config and place it in the same directory as the PowerShell.exe:
<?xml version="1.0"?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0.30319"/>
<supportedRuntime version="v2.0.50727"/>
</startup>
</configuration>
Then the scripts. Granted, I'm using the defaults, not the DacOptions or list of tables to export.
# load in DAC DLL (requires config file to support .NET 4.0)
# change file location for a 32-bit OS
add-type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"
# make DacServices object, needs a connection string
$d = new-object Microsoft.SqlServer.Dac.DacServices "server=."
# register events, if you want 'em
register-objectevent -in $d -eventname Message -source "msg" -action { out-host -in $Event.SourceArgs[1].Message.Message }
# Extract DACPAC from database
# Extract pubs database to a file using DAC application name pubs, version 1.2.3.4
$d.extract("c:\temp\pubs.dacpac", "pubs", "pubs", "1.2.3.4")
# Export schema and data from database pubsdac
$d.exportbacpac("c:\temp\pubsdac.bacpac", "pubsdac")
# Load dacpac from file & deploy to database named pubsnew
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load("c:\temp\pubs.dacpac")
$d.deploy($dp, "pubsnew")
# Load bacpac from file & import to database named pubsdac2
$bp = [Microsoft.SqlServer.Dac.BacPackage]::Load("c:\temp\pubsdac.bacpac")
$d.importbacpac($bp, "pubsdac2")
# clean up event
unregister-event -source "msg"
Now, the question for the reader. There has been a lot of work going on to make sets of APIs into libraries of cmdlets. I thought about this, but it seemed to be such a simple API that I'd be writing a cmdlet encapsulating 2-3 lines of code each time. So when is it useful to "cmdlet-ize" APIs? Always? Or is it too much to cmdlet-ize for every two lines of code? How about 5 lines of code? Is there a cutoff? Let me know on Twitter or in comments…
Thanks to those of you who attended the session.
@bobbeauch
One thought on “Using PowerShell with DAC 3.0”
IMHO, Going beyond that you can put your function in a module and use as if it were a native cmdlet because it is a simple detail, the great benefit that you have when creating functions (or cmdlet-ize) is to code reusability and scalability. Of course if this is possible, othwrsise I do not see why create it. If not possible, use script.I can see in this script a potential reusable functions. I need to understand better DAC process to say something, but if you will use the code again, do it in a function. Otherwise Script it. In my experience, I firmly believe in functions not scripts. I only use scripts or scriptblocks in small parts of code (automation stuffs – and using the functions too) and not reusable. 🙂
Comments are closed.