Preview to my new set of SQL Developer PowerShell functions

Here’s a preview on what I’ve been doing for work.  I create about 12 new functions to assist me in automating a task to copy table(s) from one database to another.  Then, this process will also rebuild the data, indexes, and constraints from the scripts extracted during this process.

All these functions are using embedded T-SQL scripts to assist in the scripting of the SQL Table objects.  Finally, I will be ending having a module to load all my functions when is needed.

Just recently, I updated two on the main functions I posted in the Microsoft Script Repository:

1. “Function Write-SQLCreateTable” – Updated – 05/25/2011: Added three new switch parameters so we can create individual scripts for:DropTableOnly, CreateTableOnly, and TurnOffidentity.

2. “Function Write-SQLInsertSelect” – Updated – 05/25/2011: Add ability to detect if there’s an Identilty Column so it can add the “Set Identity_Insert …” line.  And, Add set-location go back to your “Home Drive” when the process completed.

These two updated functions require you have the SQLPS module or in won’t work.  That’s why I include to do both Import and Remove of SQLPS module.  Also, these functions will not script out Indexes, and Constraints.

In my new SQLDevTools Module, all functions will not import/remove the SQLPS module.  It will included functions to script out Indexes, Check Constraints, Foreign Keys and even Triggers.  Functions name are change slightly, and  a test functions script is included to assist in building rebuilding a table in another database.  More functions will be added to this module.  I will make it available to the community through CodePlex when is ready.

Here’s a preview snapshot of my new functions:

List of SQLDEVTOOLS functions

Special Thanks to the following bloggers for their contributions:

1. Martin Bell  (UK SQL MVP) – August 2009 posts: http://sqlblogcasts.com/blogs/martinbell/archive/tags/Scripting/default.aspx

2. Aaron Nelson (SQLVariant) – November 2010 post: http://sqlvariant.com/wordpress/index.php/2010/11/sql-source-control-powershell-and-tfs-ftw/

TechEd 2011 had some great PowerShell Sessions…

If you couldn’t make it to TechEd 2011 this year then you just missed two great PowerShell Sessions.  But, don’t worry, check out this link: (download and watch)

Advanced Automation Using Windows PowerShell 2.0

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/WSV406

Windows PowerShell Remoting: Definitely NOT Just for Servers

  • Date: May 18, 2011 from 1:30PM to 2:45PM
  • Day 3
  • WCL321
  • Speakers: Don Jones

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/WCL321

To check out TechEd 2011 other recorded sessions on many topics, such as: Azure, Hyper-V, Visual Studio, SQL Denali, Lync…  many more.  Click on this link: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011

Two new PowerShell functions for SQL Server

I just uploaded to new functions to the Microsoft Script Center:
The first will script out your table(s) structure, adn the second one will build your “insert into… Select…” script so you can populate the data of the empty table(s).  I included some documentation so is accessible using the get-help or help commands.
In the “Write-SQLCreateTable” function, I use some pre-defined scripting options but you may add/removed and only use the ones you need.  Here’s the ones I use:
    $scrp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Scripter $SQLInstanceName
    $scrp.Options.DriAll = $True
    $scrp.Options.ClusteredIndexes = $True
    $scrp.Options.IncludeIfNotExists = $True
    $scrp.Options.IncludeHeaders = $True
    $scrp.Options.Indexes = $True
    $scrp.Options.WithDependencies = $True
    $scrp.Options.AnsiPadding = $True
    $scrp.Options.NoIdentities = $True

For now, you need to provide the table schema, and the name of the tables will rename the same.  I will be updating this function to later allow to include a prefix to change the destination table name, and maybe include change the schema.

Keep in mind,  both functions will display the results on screen.  You can save the resutls to an PS Object and the use the “Invoke-SQLcmd” the  “-query” parameter to execute the scripts.

[string] $sqlquery = Write-SQLCreateTable …
Invoke-SQLCmd -ServerInstance MySQLServer -query $sqlquery

Sample pics

Function Write-SQLCreateTable
Function Write-SQLInsertSelect
Happy PowerShelling!!

Getting Started with Office 365 PowerShell

I have to admit that having setup Office 365 was a breeze.  Just make sure you have a reliable fast internet connection, and you are good to go. By the way… no servers equipment is needed.

and

Well, since I got Office 365 Beta setup in my computer I’ve been looking for information about how to use PowerShell.  Now, thanks to one of my PowerShell MVP
college (Sean Kearney) mention about this information been well documented in
Office 365 Beta.  I found it, and I couldn’t believe it was that SIMPLE!!!  YES,
SIMPLE!!

My computer environment is very basic.  I have no Exchange, Linc, or Sharepoint.  So now I’m part of the cloud experience with Office 365, and my answer of using PowerShell was at my fingertip.

Search for "Using PowerShell"

If you want to try it, then check out the links below, and copy/paste the code.  Make sure you meet all the requirements needed so you can experience using PowerShell with Office 365.  By the way, It’s highly recommended by our PowerShell MVP’s colleges to set your Execution Policy to “ RemoteSigned” and not “unrestrincted”.  Using “unrestricted” will still give you some security warning.

Office 365 – PowerShell – Resource Information Currently
available under: “Outlook Web App Help”

Install and Configure Windows PowerShell: http://help.outlook.com/en-us/140/cc952756.aspx

Reference to Available PowerShell Cmdlets: http://help.outlook.com/en-us/140/dd575549.aspx

Use Windows PowerShell in Exchange Online: http://help.outlook.com/en-us/140/cc546278.aspx

**Control Users’ Access to Windows Remote
Management:
http://help.outlook.com/en-us/140/Dd256962.aspx

Connect Windows PowerShell to the Service: http://help.outlook.com/en-us/140/cc952755.aspx

**You need to understand one thing, and quote this from the
help “… By default, all new user accounts are allowed to use Windows
Remote Management (WinRM) to access the cloud-based organization with Windows
PowerShell …”.  But, the help documentation gives you the
code to prevent users to access remoting with Office 365. (read the help documentation)

By the way, there’s a recent change to the connection URI:

… -ConnectionUri “https://pod51011psh.outlook.com/PowerShell-LiveID?PSVersion=2.0

I love our PowerShell MVP Community.  Here’s an observation from Aleksandar Nikolic:  “… i think you should use generic Connectionuri(https://ps.outlook.com/powershell) and not specific server. you’ll be redirected  … office365 will redirect you to closest, the least busy server  …”.   Thanks Alek.:)

This is totally true, and the only reason I included the change was to avoid the redirection warning message.  So you can still use what’s documented:

-ConnectionUri https://ps.outlook.com/powershell/

Sample PS Console creating a Office 365 PS Session

Sample Script:

##----  Getting Started with Office 365 PowerShell  ----##

## - Get your Office 365 Credential
$LiveCred = Get-Credential

## - Prepare your Office 365 PS Session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
-ConnectionUri "https://pod51011psh.outlook.com/PowerShell-LiveID?PSVersion=2.0" `
-Credential $LiveCred -Authentication Basic -AllowRedirection

## - Load Office 365 PowerShell module into your current PS Console session.
$Office365PS = Import-PSSession $Session

## - Using the exported functions get a list of your mailboxes
Get-User

## - Get command information, count and list names.
$Office365PS.ExportedFunctions.count
get-command -Module $Office365PS | more

In my case, I got a total of 216 more PowerShell commands to manage and/or automate my Office 365 cloud application: (listed below)
Add-DistributionGroupMember
Add-MailboxFolderPermission
Add-MailboxPermission
Add-RecipientPermission
Add-RoleGroupMember
Clear-ActiveSyncDevice
Clear-TextMessagingAccount
Compare-TextMessagingVerificationC
Complete-Migration
Disable-InboxRule
Disable-Mailbox
Enable-InboxRule
Enable-Mailbox
Enable-OrganizationCustomization
Export-MigrationReport
Get-AcceptedDomain
Get-ActiveSyncDevice
Get-ActiveSyncDeviceAccessRule
Get-ActiveSyncDeviceClass
Get-ActiveSyncDeviceStatistics
Get-ActiveSyncMailboxPolicy
Get-ActiveSyncOrganizationSettings
Get-AdminAuditLogConfig
Get-CalendarNotification
Get-CalendarProcessing
Get-CASMailbox
Get-CASMailboxPlan
Get-Contact
Get-DistributionGroup
Get-DistributionGroupMember
Get-DynamicDistributionGroup
Get-FailedContentIndexDocuments
Get-FederatedOrganizationIdentifie
Get-FederationInformation
Get-FederationTrust
Get-Group
Get-HotmailSubscription
Get-ImapSubscription
Get-InboxRule
Get-IRMConfiguration
Get-LinkedUser
Get-LogonStatistics
Get-Mailbox
Get-MailboxAuditBypassAssociation
Get-MailboxAutoReplyConfiguration
Get-MailboxCalendarConfiguration
Get-MailboxCalendarFolder
Get-MailboxFolder
Get-MailboxFolderPermission
Get-MailboxFolderStatistics
Get-MailboxJunkEmailConfiguration
Get-MailboxMessageConfiguration
Get-MailboxPermission
Get-MailboxPlan
Get-MailboxRegionalConfiguration
Get-MailboxSpellingConfiguration
Get-MailboxStatistics
Get-MailContact
Get-MailUser
Get-ManagementRole
Get-ManagementRoleAssignment
Get-ManagementRoleEntry
Get-ManagementScope
Get-MessageCategory
Get-MessageClassification
Get-MessageTrackingReport
Get-MigrationBatch
Get-MigrationStatus
Get-MigrationUser
Get-MoveRequest
Get-MoveRequestStatistics
Get-OrganizationalUnit
Get-OrganizationRelationship
Get-OwaMailboxPolicy
Get-PerimeterConfig
Get-PopSubscription
Get-ProvisioningRequest
Get-Recipient
Get-RecipientPermission
Get-RecipientStatisticsReport
Get-RemoteDomain
Get-RemovedMailbox
Get-RetentionPolicy
Get-RetentionPolicyTag
Get-RMSTemplate
Get-RMSTrustedPublishingDomain
Get-RoleAssignmentPolicy
Get-RoleGroup
Get-RoleGroupMember
Get-SecurityPrincipal
Get-SendAddress
Get-ServiceStatus
Get-SharingPolicy
Get-Subscription
Get-SyncConfig
Get-TextMessagingAccount
Get-ThrottlingPolicy
Get-ThrottlingPolicyAssociation
Get-ToolInformation
Get-TransportConfig
Get-User
Import-ContactList
New-ActiveSyncDeviceAccessRule
New-ActiveSyncMailboxPolicy
New-AdminAuditLogSearch
New-DistributionGroup
New-DynamicDistributionGroup
New-HotmailSubscription
New-ImapSubscription
New-InboxRule
New-Mailbox
New-MailboxAuditLogSearch
New-MailboxFolder
New-MailContact
New-MailMessage
New-MailUser
New-MessageClassification
New-MigrationBatch
New-MoveRequest
New-OrganizationRelationship
New-OwaMailboxPolicy
New-PopSubscription
New-ProvisioningRequest
New-RemoteDomain
New-RetentionPolicy
New-RetentionPolicyTag
New-SharingPolicy
New-Subscription
Remove-ActiveSyncDevice
Remove-ActiveSyncDeviceAccessRule
Remove-ActiveSyncMailboxPolicy
Remove-DistributionGroup
Remove-DistributionGroupMember
Remove-DynamicDistributionGroup
Remove-InboxRule
Remove-Mailbox
Remove-MailboxFolderPermission
Remove-MailboxPermission
Remove-MailContact
Remove-MailUser
Remove-MessageClassification
Remove-MoveRequest
Remove-OrganizationRelationship
Remove-OwaMailboxPolicy
Remove-ProvisioningRequest
Remove-RecipientPermission
Remove-RemoteDomain
Remove-RetentionPolicy
Remove-RetentionPolicyTag
Remove-RoleGroupMember
Remove-SharingPolicy
Remove-Subscription
Resume-MoveRequest
Search-AdminAuditLog
Search-Mailbox
Search-MailboxAuditLog
Search-MessageTrackingReport
Send-TextMessagingVerificationCode
Set-AcceptedDomain
Set-ActiveSyncDeviceAccessRule
Set-ActiveSyncMailboxPolicy
Set-ActiveSyncOrganizationSettings
Set-CalendarNotification
Set-CalendarProcessing
Set-CASMailbox
Set-Contact
Set-DistributionGroup
Set-DynamicDistributionGroup
Set-FederatedOrganizationIdentifie
Set-Group
Set-HotmailSubscription
Set-ImapSubscription
Set-InboxRule
Set-LinkedUser
Set-Mailbox
Set-MailboxAuditBypassAssociation
Set-MailboxAutoReplyConfiguration
Set-MailboxCalendarConfiguration
Set-MailboxCalendarFolder
Set-MailboxFolderPermission
Set-MailboxJunkEmailConfiguration
Set-MailboxMessageConfiguration
Set-MailboxPlan
Set-MailboxRegionalConfiguration
Set-MailboxSpellingConfiguration
Set-MailContact
Set-MailUser
Set-MessageClassification
Set-MoveRequest
Set-OrganizationConfig
Set-OrganizationRelationship
Set-OwaMailboxPolicy
Set-PerimeterConfig
Set-PopSubscription
Set-RemoteDomain
Set-RetentionPolicy
Set-RetentionPolicyTag
Set-RoleGroup
Set-SharingPolicy
Set-TextMessagingAccount
Set-TransportConfig
Set-UMMailbox
Set-UMMailboxPIN
Set-User
Start-ManagedFolderAssistant
Start-MigrationBatch
Start-ProvisioningRequest
Start-RetentionAutoTagLearning
Stop-MigrationBatch
Suspend-MoveRequest
Test-MAPIConnectivity
Test-MigrationServerAvailability
Test-OrganizationRelationship
Update-DistributionGroupMember
Update-RoleGroupMember
Write-AdminAuditLog

As you can see, there’s plenty of commands to learn.  So, let’s get busy.

Happy PowerShelling!!

South Florida IT Camp Saturday 2011 on July 23rd

We are pleased to announce the first South Florida IT Camp on Saturday, July 23rd 2011 from 8:00 am to 5:00 pm at Nova Southeastern in Fort Lauderdale.

Parking and admittance to this event is free.  Lunch will provided. Please Register now. Let your friends and colleagues know about this event.

Join system administrators IT professionals and database professionals in addition to managers at all levels in Florida that work with Microsoft technologies for a “IT Camp Saturday”. IT Camps are a free, one day learning event for anyone seeking professional development. This event serves IT professionals and students with a focus on IT Pro related technologies. IT Camps offer a conference style learning environment free of charge to attendees and is open to presenters of
all backgrounds and expertise.

Have questions? Comment or Contact Us.

Call for Speakers is Open

The call for speakers is open until 6/11. Topics can be anything applicable to System admins, and/or IT Pros including SharePoint, System Center, SQL Server, PowerShell, Active Directory, or Windows 2008 R2 and Windows 7.

To submit your session, please Click Here, or if you have questions? Comment or contact us.