YES!! Windows Management Framework (WMF/PowerShell) 3.0 CTP2 is here.

Posted on December 3, 2011 by admin

YES!!  It’s another great accomplishment from the Microsoft PowerShell Team.  Just released on December 2nd 2011 this CTP (Community Technology Preview) come with fixes and more enhancements.  Please CHECK out PowerShell ISE, is really showing some progress.

PowerShell ISE V3 CTP2 Customizable Themes

Get it here: http://www.microsoft.com/download/en/details.aspx?id=27548

First thing!!!  READ:> IMPORTANT!!  YOU NEED TO UNINSTALL WMF 3.0 CTP1!  Or, suffer the consequences.

But, it you’re one of the few experiencing Windows 8 Preview and using Hyper-V 3.0 then you can create another VM for a clean CTP2 installation experience.  Or, just use the VM shapshot to to back and forth between version.  So, take a pick.

This version comes with some documentation, so make sure to ready it.  Also, don’t be afraid to submit bug reports at: https://connect.microsoft.com/PowerShell/. (you need to have a Live ID)

For example, I just put in a suggestion to include the ability to “Comment-In” and “Comment-Out” block of code: https://connect.microsoft.com/PowerShell/feedback/details/711231/ise-v3-need-to-be-able-to-comment-a-series-of-lines-in-a-block-of-code

*Hint*Hint*:  

  1. When running scripts, I notice there has been changes to how the “Set-ExecutionPolicy” behaves.  I’m used to set my execution policy to “RemoteSigned“, but for this version now I had to changed it to “Unrestricted”. (more test to be done)
  2. Also, don’t forget to execute the “Update-Help” as soon as you open PowerShell.  This is working for most part.

There will be more to come soon.

Posted in Hyper-V, PowerShell, Windows 8 | Comments Off

Windows 8 Hyper-V 3.0 – My Personal 8 Tips for the Newbie

Posted on October 31, 2011 by admin

Well, Here’s some tips to those who are first timers with Windows 8 Server (or Client) Preview edition.  I have to say, after been using the previous version of Hyper-V, I’m very happy for what i’ve seen so far in Windows 8 Server with Hyper-V 3.0.  As I finally completed updating/rebuilding all my VM’s for upcoming PowerShell and SQL Servers presentations, I have compiled 8 tips that might help the newbies get started using Microsoft Virtualization Technology.

From the Hyper-V Manager console, click on the VM Connect option to work with your VM's.

Tip #1 – Memory is important

Yes, I totally agree with this one.  After I upgraded my memory from 4GB to 8GB, all my VM’s are working OK.  I have experience giving 1.5GB to a Virtual SQL machine is ideal for development.

VM Memory Setting

Tip #2 – Enabling Hyper-V

First, There’s some differences between Hyper-V 3.0 server and Client.  But, let me be clear, that every machine might be different.  In my case, I have a 4 year old HP Pavilion dv7-* Entertainment laptop with Virutalization option enabled. I created a dual boot Windows 8 Server and Client version.

On the Windows 8 Server enabling “Hyper-V” role using the new “Server Manager” Dashboard was easy.  But, in my case, I had to manually enable “Hyper-V Core” on my Windows 8 client by turning it “ON” in Windows Features.  Here’s the command:

C:\Dism /online /enable-feature /featurename:Microsoft-Hyper-V

Tip #3 – Create your Hyper-V 3.0 Virtual Network Switch

This is very Important and one this you need to setup your Virtual Network connections in either Server or Client. Open “Hyper-V Manager” and look the “Virtual Switch Manager”.

Hyper-V Virtual Switch Settings

As you can see in my picture, I have a Loopback adapter Virtual Switch (External), and a Wireless Virtual Swtich(Externa) both setup as Legacy Adapters).   These two combination works great when VM’s talk to each other and, at the same time, have access to the internet for Windows Update.  Keep in mind, you can disable the wireless by going into the VM Settings option, going into the “Legacy Network Adapter”, and changing the “Virtual Switch” to “not Connected”.

VM Settings menu - Changing Virtual Switch to "Not Connected"

Tip #4 – Don’t need a SAN Storage

FYI. Hyper-V 3.0 will allow you to create your VM’s practically anywhere.  In my case, I have an External USB 1.o TB Storage drive.  This is good new because you don’t need a SAN storage unit, and can save Vm’s outside your Physical server drives.

Tip #5 – Set VM’s TimeZone correctly

This might be only in my case, but after creating my VM’s and trying to connect them to join my Virtual Domain Controller, I experience connectivity issues between my VM’s due to their Time Zones not been the same.  So, If you have a Virtual Domain Controller, make sure both Time Zone machines are the same.

Here’s an interesting link on “Time Synchronization in Hyper-V” (by the “Virtual PC Guy’s Blog”) you may find useful: http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/11/19/time-synchronization-in-hyper-v.aspx

Tip #6 – Learn to use the VM Snapshot Feature

Yes! This feature is a life saver.  It can help you troubleshoot VM issues by you taking the snapshot of a VM machine before doing any irreversible updates that could force you start over to rebuild the machine over.  It’s perfect when creating multiple scenarios for in test machines and adding descriptions to it.  Also, you can also revert to a previous snapshot, or apply the changes.

List of a server Snapshot showing different stages during a SQL Server Installation

Adding a Description to your Snapshot

 

Tip #7 – Bringing Legacy Microsoft VM to Hyper-V3

If you want to move all your existing VM’s from: Virtual PC, Windows Virtual PC, and Hyper-V, just remember to uninstall all “Virtual Components”, or “Integration Services” to prevent any misbehaviour in your VM.  Then, you will be able to install the Hyper-V 3.0 “Integration Services” so that Hyper-V can manager your VM’s services such as: Snapshot, Start, Shutdown, or Save.

*hint*: I have experience some issues with Windows Servers 2008, and Windows 2003 SP1 after mvoing them to Hyper-V 3.0.  The mouse won’t work even after installing “Integration Services”.

Tip #8 – Start using Use PowerShell

Yes! YES! This is your opportunity to start using PowerShell.  You can user the Hyper-V module and start managing your VM’s.  Also, don’t forget that PowerShell comes with over 1695 and about  63 Modules.  Keep in mind,  This numbers will varies depending on the enabled Windows features and/or installed Server Applications containing PowerShell modules.

Here’s some one-liners command you may want to try get the estimate numbers of PowerShell commands and modules:

1. Get total number of available  commands and list their names:

(get-command -Module *).count; (get-command -Module *) | Select name;

2. Get total numbers of available (installed) Modules, and list their names:

(get-Module -ListAvailable).count; (get-Module -ListAvailable) | Select name;

I hope this information is helpful.  That’s it for now!

Posted in Hyper-V, PowerShell, SQL Server, Windows 8 | Comments Off

SQL Server PowerShell SMO – Simple way to Change SQL User Passwords

Posted on October 28, 2011 by admin

Here’s a simple PowerShell SMO code that shows you How-To change a SQL user password.  Keep in mind, SMO needs to be installed and the assemblies loaded before using this code.

To load SMO, you can:

1. Install the SQLPS Module (is using SQL Server 2012 “Denali”), or Chad’s Miller SQLPS module for SQL Server 2008/R2.

Import-Module SQLPS -DisableNameChecking

2. Or, Load the SMO Assemblies using the PowerShell V2 “AddType” command: (but carefull is you have multiple SQL Server versionsin the same box)

Add-Type -AssemblyName “Microsoft.SqlServer.Smo”

3. Or, use the still reliable the old PowerShell V1 load assemblies line using:

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

In the following example show how to connect to a SQL Server using both the default Windows Authentication (with high Administrator privileges already set), or SQL Server Authenbtication to later change a user password.

*Hint*: If you want to include special characters, you need to use the single qoutes or PowerShell will think that it’s a variable name.

As you can see,  with just a few line of PowerShell SMO code you can start orchestrating a solution that can be applied to your SQL Server environments.

Just try it!

Posted in PowerShell, SQL Server | Comments Off

Creating a Standalone Windows 8 Server Virtual Machine with SQL Server 2012 CTP3

Posted on October 24, 2011 by admin

Here’s how I build my version of a standalone workgroup Windows 8 Server Virtual Machine(VM) with SQL Server 2012 (“Denal;i”) CTP3. When you create this VM, make sure to give enough memory.

How-To create a VM in Hyper-V Manager console:

I’m not going deep on this topic but the Hyper-V Manager GUI it’s easy to use.  I’m assuming you already got an *.ISO image of both: Windows 8 Server Preview and SQL Server 2012 CTP3.  If not you’ll have to find it at Microsoft website and/or your MSDN/Technet subscribtion.

Before Creating your new Virtual machine:

Keep in mind, you’ll need to setup you Hyper-V environment.  Meaning, if you’re using a laptop and/or a desktop computer (not a server),  still you need to make sure it meets the Hyper-V requirements or it won’t work.

So, at least you will need to use the “Virtual Switch Manager…” to assist you setting up your virtual network card to use by any VM you create.

Notice, in my environment, I have created three virtual network adapters:

  1. Wired Internet
  2. Wireless Internet
  3. Loopback

The first two adapters serve my purpose to be able to connect to my physical machine Internet connection so I can do windows update.  The loopback adapter is for my internal network connection to my virtual Domain Controller.

*Hint*: In order to allow your VMs to access your external wireless adapter, you need to enable in “Server Manager” the “Wireless LAN Service” feature before you create the virtual wireless adapter.

Now, you are ready to create a New Virtual machine, and just follow the wizard:

The “New Virtual Machine Wizard” will help you configured everything you need.   Make sure you create this VM with enough memory.  In my case I assigned 1.5GB of memory.

Opening your Virtual Machine Connection:

There’s two ways to open your VM Connection in your “Hyper-V Manager” console:

  1. By double-clicking at the actual virtual name.
  2. Or, double-click at the actual virtual machine preview pane at the bottom left side of the “Hyper-V” console.

Now that your connection GUI is open, it’s a good time to start doing a VM Snapshot in case you need to go back and troubleshoot in case of problems. Here’s some pictures on How-To create a VM Snapshot:

If a Snapshot box asking to add a Name to your snapshot, go ahead and do it.  This box only comes when there has been changes done to your VM.

Now, Ready for SQL Installation.

Installing SQL Server 2012 (“Denali”) CTP3:

After building the virtual server, if you try to immediately install SQL Server 2012, it won’t work.  And, when you try to run the setup.exe, you won’t have access to the “SQL Server Installation Center” to view the “Hardware and Software Requirements” information.

Check SQL Server Requirements:

So first, you may want to check this link to read about what’s required to install this new version of SQL Server: http://msdn.microsoft.com/en-us/library/ms143506(v=SQL.110).aspx

Need Role(s) and Feature(s):

In order to make my SQL Server installation work, I had to open Windows 8 “Server Manager” and follow the wizard to do the following steps:

  1. Install the Application Server Role (you can add more roles as you need during this process).
  2. Then, you need to add the following features: (again, you can add additional features as needed)
    a. Enable the .NET Framework 3.5
    b. Enable the PowerShell ISE

After doing this steps, then I was able to get the SQL Server setup to work and allow me to start my installation.  Use the VM Connect GUI to allow you to attached the SQL Server  *.ISO image for your VM to start the SQL Server Installation.

I’m not going to show all the SQL Server installation screens but here’s to show that I’m able to proceed with the installation.

On the previuos picture, notice that I selected most of the features to install except the for the two Distributed Replay services. At the same time, I took a live snapshot of my VM before the actual installation process.

Installation Completed Successfully:

Yes! I got my SQL Server 2012 installed without a glitch!

Testing SQL PowerShell:

Now, I immediately testing the PowerShell SQLPS module. I was so excited that forgot to do something first.  Here’s my result of my first try:

Yes! I forgot to set my “Set-ExecutionPolicy” to “RemoteSigned“, then close and reopen my Windows Console.  I also I was able to SQLPS.exe from SSMS Database option just to test that there’s no errors.  So, everything works at least for now.

Ooops! Except for PowerShell ISE.  Yes! If you try to do an “Import-Module SQLPS -DisableNameChecking” then you get an error:

Don’t Worry!  PowerShell ISE is not the only editor.  You can still use Notepad to create/modify your script(s).  Or, just try downloading one of the free community editors from: SAPIEN, and PowerGUI just to name a few.

To test SQLPS I use the following command line:

Invoke-SQLcmd -database ReportServer -Query “Select top 3 * from dbo.DBUpgradeHistory

Final comments:

I know I may have skip some steps but the bulk on How-To create Window 8 Server VM in shown in this blog.  One important thing to keep in mind, these are still Community Technologies Preview (CTP) and it will change.

So, Don’t be afraid to try it!  This is why we have the ability to create Virtual Machines in our own developement machines.  Again, take advantage of Hyper-V.

The opportunity we have is to learn from them, assist giving feedback, and MOST IMPORTANT, it help us to stay ahead in upcoming technologies.

Enjoy, Learn, and Collaborate!

Bonus!!… Bonus!!

If you want a HACK to fix the PowerShell ISE that here it is: (Hack provided by one of our PowerShell MVP’s – Joel “Jaykul” Bennett
http://HuddledMasses.org
http://PowerShellGroup.org
)

  1. Go to you x:\Windows\System32\WindowsPowerShell folder (make sure to access this folder “As an Administrator” or it won’t work)
  2. Create a blank text filename: powerShell _ise.exe.config (Yes! this file extension is “.config”)
  3. Then, add the following XML lines, and save the file when done:
(Updated: 10/26/2011 – I missed the “runtime” section)

<configuration>
<startup useLegacyV2RuntimeActivationPolicy=”true”>
<supportedRuntime version=”v4.0″ />
</startup>
<runtime>
<loadFromRemoteSources enabled=”true”/>
</runtime>
</configuration>

Reopen PowerShell ISE and try to use the “Invoke-SQLcmd” command.

That’s it!

Posted in Hyper-V, PowerShell, SQL Server, Windows 8 | Comments Off

SQL Saturday #86 Tampa BI edition: SSIS – Analyzing your data integrating PowerShell session

Posted on October 21, 2011 by admin

I’m excited to bring this new topic to #SQLSat86 Tampa for the BI community.  So, here’s a brief rundown of what I will be covering on Saturday, November 5th morning session:

Topic: SSIS – Analyzing your data integrating PowerShell

*Note: This is not an all PowerShell Session but it plays a big role in this SSIS solution.

What will be covering:

1. Some basic to intermediate SSIS and PowerShell.
2. How to pass arguments between PowerShell and SSIS steps.
3. How to use PowerShell to assist in analyzing our tables and/or Data.
4. How to Integrate PowerShell in our SSIS Solution (when needed).
5. Tool available to allow these two technologies to integrate in our ETL solution.

This is going to be an ALL DEMO session.

Requirements:

1. Have some interest in PowerShell.
2. Some basic SSIS ETL experience.
3. Willing to be open to new technologies.
4. Most Important: No PowerShell experience is not required.

I will Demo:

1. Passing Arguments between PowerShell Applications in SSIS.
2. Work with both Script Task and Script Components.
3. Use of both VB and C# (CSharp) .NET Scripting code.
4. Some PowerShell.

I hope yopu will enjoy this presentation

Posted in PowerShell, SQL Server | Comments Off

PowerShell working with SQLPS or SMO…

Posted on September 22, 2011 by admin

Today, while help @meltondba with his SQLPS question on the enumerating jobs history, @LaerteSQLDBA provided a oneliner to provide this result.  There’s one concern, should we use SQLPS instead of SMO.   For this answer I’m going to point out MSDN article regrading the future of SQLPS in upcoming SQL Server releases: http://msdn.microsoft.com/en-us/library/cc280450(v=SQL.110).aspx

This article states that “… This feature will be removed in a future version of Microsoft SQL Server.  Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use the sqlps PowerShell module instead. For more information about the sqlps module, see Import the SQLPS Module. …”

So, during this exercise I found out that I can out with the same number of line for either SQLPS and SMO.  Both giving me the same results.

Here’s some picture ilustrating basic code snippet of both SQLPS and SMO to get some SQL Server Jobs information:

SQLPS sample of getting jobs information

SMO sample of getting jobs information

This pictures shows, it look simple enough.  So, if you’re building SQLPS scripts, you can easily start transitioning your code to SMO.

Posted in PowerShell, SQL Server | Comments Off

Windows Developer Preview – Looking at the Eye of the Storm

Posted on September 16, 2011 by admin

There’s no doubt, there’s a bright future with Windows 8 and it does have lots of exciting features.  But,  did everything looks good and easy.  Well, easy!! Not really.

I’m sure I will not be the only person who have experience problems installing Windows 8.  Now, please understand, this not to critize Microsoft in any way.  Every IT PRO and/or Developer will have different scenario, and in one point in time, will have installation issues.

I had a problem trying to install from Windows 7 the new “Windows 8 Developer Preview” client.  At first I thought it was my download was corrrupted.   I didn’t have any problems with the Server version.

But then, after restarting my computer, I lost my “Bootmgr”.

Now, this happened because I boot to multiple OS’s: Windows Server 2008 R2 and Windows 7.  I had one successfull installation of the “Windows 8 Server Developer Preview” but on my Windows 7 the installation failed to install over.  The problem is, you will not notice your “BootMgr is missing” error after a reboot was performed.

So, what precautions you should take to prevent this issue:

  1. Make sure you have create a “System Repair Disk” for you Operating System.
  2. Also, do a Windows Backup.

In most cases, using the “System Repair Disk” will get you out of the hold.  In my situation, I had to delete all my System, and my Windows 7 Partition.  This way I took a longer route and able to put back a clean Windows 7 OS, and only then I could successfully install “Windows 8 Developer Preview” client.

Now,  looking back to what I had done, I realized that MAYBE I would have avoided this issue if I had paid attention to the Windows 7 “System Configuration“.  And, here’s why:

There’s a possibility the failed installation of the Windows 8 client was due to not having my Windows 7 “Set as Default” boot OS.   Now, if this doesn’t work, then delete the partitions and try again.

At the end, I finally got my BootMgr working with two OS’s: Windows 8 Server and Windows 8 Developer Preview working fine.

I’m ready!! To continue install applications, and doing more test with Windows 8.

To download Windows 8 Editions, go to your MSDN subscription, or get it at this link: http://msdn.microsoft.com/en-us/windows/home/

Happy Testing!!

Posted in Windows 8 | Comments Off

Yes! Windows 8 Editions with PowerShell V3 included

Posted on September 15, 2011 by admin

Tonight I was able to download both Windows 8 Server with Hyper-V and the Windows Developer Preview ISO.  I will create VM using the Developer Preview ISO, and then take a look at PowerShell V3 new cmdlets.  As, everyone got an early start downloading the Windows Developer Preview edition, I will be working on the server version.

As you already know by now, PowerShell V3 is here with lots of new command to help manage your computer system.  But, don’t take my word for it, if you’re at the BUILD Conference, don’t forget to attend Jeffrey Snover PowerShell Sessions: http://www.buildwindows.com/Sessions?q=PowerShell&x=0&y=0

There’s a possibility that Jeffrey’s BUILD sessions will be recorded and can be viewed later on the following address: http://channel9.msdn.com/Events/Speakers/jeffrey+snover

To download Windows 8 Server and/or Developer Preview editions, look in both your MSDN Subscription, and/or at the following link: http://msdn.microsoft.com/en-us/windows/home/

Windows 8 Server w/Hyper-V

The installation started with executing the downloaded *exe: en_windows_server_developer_preview_with_hyper-v_virtual_machine_x64_735222.exe

This executable will extract a VHD file:

Now, this VHD is good if you a media were you can boot from a thump-drive, or use the Windows 7 Boot from VHD feature.

Now, you’re better off downloading the ISO and burned it into a DVD.  So, look for the “Windows Server Developer Preview (x64) – DVD (English)” in your MSDN Subscription.

I lost my BootMgr!

Yeap! I was trying to use the Boot from VHD in Windows 7 but the documentation out there doesn’t tell you to activate the attached VHD and make it active to boot.  So, I try a couple fo things but to end up with “… Bootmrg is missing …” message.  Well, how can I resolved this new issue.  The answer was found in Microsoft Answer: “windows 7 bootmgr is missing– resolved using WinRE” – http://answers.microsoft.com/en-us/windows/forum/windows_7-system/windows-7-bootmgr-is-missing-resolved-using-winre/8bb148bc-8035-4847-9696-888f7a0a9720

So, I prepare a recovery DVD using the “Create a system repair disc” found in the “Backup and Restore” section.  It works like a charm!

Back to Windows Virtual Machnes

By the way, I did try to create Windows 8 VM’s in both Windows 2008 R2 Hyper-V and Windows 7 “Windows Virtual PC” but didn’t work.

Now, I’ve heard that it’s possible to create a Windows 8 VM using VirtualBox.  Try it!

On my laptop, I decided to install “Windows 8 Server w/Hyper-V” over my “Windows Server 2008 R2 SP1″.  This way I can start setting up my new VM’s and see how the my older ones work under this new OS.  This installation took about a couple of hours with a series of reboots.

Before, creating my VM’s, I needed to install the Hyper-V role and enable PowerShell ISE using the new Server Manager dashboard:

When you click on the Windows Flag to look for your application, it will take you to the new Start menu:

You will notice that “PowerShell” is available next to”Server Manager“.  We click on Powershell and enter the $PSVersionTable to check version:

As you can see, this is PowerShell Version 3.  This version comes with a total of 56 available Modules, 521 Cmdlets (excluding Modules command), 637 functions, and 146 Aliases.  You got plenty of learning opportunities with this new version.

Now, As you all know, under the server features you need to “enable” PowerShell ISE.  I try to use the Classic “Control Panel” but it didn’t work for me.  So, you need to use the Server Manager, and click on the Manager Menu to get to “Add Feature” option:

After you have selected both Role(s),and Feaures then you’re ready to install them:

When the installation is completed, you will find PowerShell ISE ready to use from the new START menu:

After having installed Hyper-V, I was able to create both 64bit version of Window 8 Server and Desktop.

Now, how do you Shutdown your Windows 8 Server?  Just do a Ctrl-Alt-Del and then at the “Shutdown” button found at the bottom right of the screen.

Let’s the fun begin!!

Big Hint!!  Just found this tweet about booting from VHD: “New Blog: How to Boot from VHD with Windows 8 Developer Preview: http://t.co/JIBBQze3“

Additional Note:  The client version of Windows Developer Preview, altought PowerShell V3 is included, it doesn’t show in your Start Menu.

Posted in PowerShell, Windows 8 | Comments Off

Presenting at Orlando SQL Saturday #85: Extend your T-SQL Scripting w/PowerShell

Posted on September 12, 2011 by admin

Date: Saturday, September 24th – Session starts at: 2:45PM

Session Level: Intermediate

I’ll be covering some of the different way we can use PowerShell to extend our T-SQL scripting.  This session will include a mix of using SMO, .NET classes, and SQLPS to help you understand the power for new scripting technology.  At the end, we’ll be creating a solution that put together all this techniques.

See You all there!!

 

Posted in Uncategorized | Comments Off

No-Hassle SQL Server Remoting with PowerShell V2

Posted on September 6, 2011 by admin

Although, PowerShell give the whole remoting experience, you may still need to ask you network administrator to let you configure remoting on all the machines you need to access.  Of course, unless you hold the keys of the kingdom.  But, it is much  simpler without PowerShell Remoting.

When you use the SQLPS Module it automatically let you connect to any of your SQL Servers on your network.  So, YES!! As long as you have permission to manage these servers, SQLPS already gives you the ability to remotely connect to other machines.  There’s no need to setup the Remoting with  Enable-PSRemoting or Set-WSManQuickConfig commands.

Simple Remoting #1

So, How can I change from my SQL Server local prompt to another instance?  Here’s an example how:

1. First load the SQLPS Module:

Import-Module SQLPS -DisableNameChecking

2. Confirm you have the SQL Server drive available:

Get-PSDrive SQL*

3. Let’s navigate to your SQL Server Instance:

cd SQLServer:\SQL\Your-Machine-name\Default  #or

cd SQLServer:\SQL\Your-Machine-name\Your-InstanceName

4. Now you can continue and navigate through your SQL Server drive like in DOS filesystem.

Dir Databases -force

5. Finally, to remote connect top another session just change the “Your-Machine-name”, and your “Your-InstanceName”.

Simple Remoting #2

One thing you need to understand, after you load SQLPS module, now you have access to all SMO (SQLServer Management objects) .NET Classes. This will help you build short and very effective .NET that extend the scripting of PowerShell.  Also, you don’t need to be at the SQLServer: drive to use it.

Here’s a basic example using SMO:

1. First load the SQLPS Module:

Import-Module SQLPS

2. Create a variable to hold your local SQL Server objects:

$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server

3. Now that your .NET object has been create you need to see all what you can grab from it:

$MySQL | Get-member | Out-GridView

4. I decided to grab the “Information” property so I can display all the information, and verify that this is my local SQL instance:

$MySQL.Information

5. Finally, to remote connect to another machine, I just add the SQL Server InstanceName to the line creating my variable:

$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name"

Or

$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name\Your-Instancename"

Keep in mind, you can another variable holding your SQL Server name with the instancename and do something like:

$SQLInstanceName = "Your-Machine-name\InstanceName"
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstanceName
$MySQL.Databases[$SourcedbName].tables | sort -desc dataspaceused | select Parent, `
 @{Label="DisplayName";Expression={$_.Schema+"."+$_.Name};} | `
 sort DisplayName;

Again, no need to setup PSRemoting.

Simple Remoting #3

And this is the simplest of all, if you like to reuse your T-SQL scripts.  This will be your most used PowerShell SQLPS cmdlet “Invoke-SQLcmd“.

1. Load the SQLPS module:

Import-Module SQLPS

2. Just start using the SQLPS “Invoke-SQLcmd” command: (basic form)

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "."  #or

$SaveResult = Invoke-Sqlcmd -Query "SELECT * FROM [AdventureWorks].[dbo].[AWBuildVersion];" -ServerInstance "Your-Machine-name\InstanceName"

$SaveResult

Again, there’s no need to be at the SQLServer: drive in order for this to work.  As you can see, with just a few lines of code you can connect to another SQL Server instance.

Go ahead and try it!

Posted in PowerShell, SQL Server | Comments Off