PowerShell The SQL Server Way
Written by Ian Stirk   
Monday, 08 July 2013

The July issue of the online subscription magazine SQL Server provides an 18-page introduction to the capabilities of PowerShell from a SQL Server viewpoint, inviting you to learn how to use the SQLServer provider and sqlps cmdlets.

PowerShell is Microsoft’s preferred management scripting tool for all windows server and platforms. It’s almost certainly true that DBAs in particular will find they get increasingly involved with PowerShell, so now is an ideal time to learn it. 


The article opens with examples of how you can use PowerShell to get help about itself. Scripts called cmdlets are explained, again with various examples. An overview of the SQL Server provider is given, this is software for interacting with structure like registry, file system, and Active Directory. 

The examples and screenshots continue, ending with a very useful and practical database backup and restore scripts. Finally, directions are provided on where to go next to build on your new knowledge. In summary, the article is a great introduction to PowerShell via SQL Server.

In order to make better use of system resources (e.g. memory, CPUs), there’s an increasing movement towards using virtual server. Michael Otey’s article Essential Tips for Virtualizing SQL Server should prove very useful in helping improve the performance and responsiveness of your virtual systems. The tips covered are:

  • Take advantage of second level address translation
  • Don’t over commit your physical processors
  • Use dynamic memory
  • Use fixed virtual hard disks or pass-through disks
  • Don’t use the default storage configuration
  • Be aware of licensing gotchas

I found the tip about not using the default storage configuration most interesting, it makes you wonder how many virtual servers are running below par due to these unchanged default settings.

Paul Turley provides a comprehensive (18 page), step-by-step example of Custom Programming to Enhance SSRS Reports. In essence, the article shows how you can create a .NET component in Visual Studio, compile it into a DDL, deploy it to SQL Server, and reuse its functionality via the extensibility model inbuilt into SSRS. If you want to extend SSRS this is certainly the article for you.

Itzik Ben-Gan provides a 27 page article on Interval Queries in SQL Server. I’m not sure how to describe its content, I lost interest after the first page, I’m not sure if this was due to a badly worded opening or what looks like a lot of theory in the article. This is a bit disappointing because Itzik usually provides insightful and interesting articles. I do wonder how many people will read and find it useful.

The magazine’s editors used the Microsoft’s TechEd 2013 conference to pick the Best of TechEd 2013 Award Winners. The product categories were:

  • Backup and Recovery
  • Cloud computing
  • Database
  • Hardware
  • Messaging and Unified Communications
  • Security
  • SharePoint
  • Software Development
  • System Management
  • Virtualization
  • Breakthrough Product

If you’re evaluating any of the above product areas, the product winners make a good starting point for further investigation.

This month’s editorial considers how partially Integrating SQL Server and the Cloud will allow you to gain important benefits of the cloud without moving your on-premises databases. Operations where gains can be made include backups and disaster recovery, including hosting your replicas in the cloud, and the use of AlwaysOn Availability, database mirroring, and log shipping. The article is quite a nice take on how you can gain cloud benefit without an all-or-nothing use of the cloud.

Michael K. Campbell’s speculation on SQL Server 2014 on the Horizon seems to be mistimed, since the Community Technology Preview 1 of SQL Server 2014 has been available since late last month http://technet.microsoft.com/en-US/evalcenter/dn205292. He does make an interesting point that Microsoft could have a game-changer if the Hekaton feature (slated to automatically improve SQL query performance by 10 to 100 times) isn’t restricted to the enterprise edition of the product.

5 SQL Server Encryption Options provide a brief roundup of SQL Server’s various encryption capabilities. Included with each one is an overview of what it is, together with useful links for discovering further information. The encryption options covered are:

  • Transparent Data Encryption
  • Column-level Encryption
  • Encrypting with the .NET Framework
  • Encrypting File System
  • BitLocker

In recent magazine issues there has been a move toward fewer, more in-depth articles at the expense of shorter, more varied content. What this means is, if you find an article that meets your requirements, then the new approach is very useful, for everyone else, I suspect it results in a weaker magazine.  

More Information

SQL Server Pro July 2013

Related Articles

Hekaton Adds In-Memory Transaction Support To SQL 

T-SQL Join Types In SQL Server Pro's June Issue

Inside SQL Server Pro Magazine May 2013

AlwaysOn Availability in SQL Server Pro Magazine

SQL Server In The Era Of Big Data

Improve Performance with SQL Server Pro Magazine February 2013

SQL Server Pro Magazine January 2013

Inside SQL Server Pro December 2012  

To be informed about new articles on I Programmer, install the I Programmer Toolbar, subscribe to the RSS feed, follow us on, Twitter, Facebook, Google+ or Linkedin,  or sign up for our weekly newsletter.

raspberry pi books



or email your comment to: comments@i-programmer.info


Explore SyncFusion's Blazor Playground

Syncfusion has provided an in-browser environment where you can write, compile and run code that uses Blazor components and get it previewed live.

Azure AI And Pgvector Run Generative AI Directly On Postgres

It's a match made in heaven. The Azure AI extension enables the database to call into various Azure AI services like Azure OpenAI. Combined with pgvector you can go far beyond full text search. Let's  [ ... ]

More News

Last Updated ( Sunday, 07 July 2013 )