Wednesday, November 12, 2014

A quick note on writing PowerShell scripts -Part 2

In Part 2 we will script a PowerShell file and execute it. It is a very simple example. However there are a few preliminary things you should be aware of.

Launch Windows PowerShell in Windows 8.1

Launching Windows PowerShell in Windows 8.1 is easy. Windows 8.1 comes with PowerShell. Click the Search charm and type in 'Windows PowerShell' or, in the all apps, pick Windows PowerShell and click.

The Windows host launches the Windows PowerShell as shown.


You type in the command $PSVersionTable.psversion to get the version on your computer as shown. It will be probably version 4.0.
------------
 
Now let us create a file, a simple one and save it with the extension ps1. I will show you later what this file look like.

However to run a script file you need to know about the execution policy. You can get a good handle on this here:
http://technet.microsoft.com/library/hh847748.aspx

How do you know what policy is in force?
To see the policy in force, just type the commandlet Get-Policy as shown and you get an immediate answer.
====
PS C:\Users\Jayaram\Desktop> Get-ExecutionPolicy
Restricted
PS C:\Users\Jayaram\Desktop>
======
'Restricted' is the default policy in Window 8, Windows 8.1 and Windows Server 2012

What does 'Restricted' mean to you?
            - Permits individual commands, but will not run
              scripts.
            - Prevents running of all script files, including
              formatting and configuration files (.ps1xml), module
              script files (.psm1), and Windows PowerShell
              profiles (.ps1). 

Well, you see with 'Restricted' policy enforced, you cannot run a script file (I created one called Hello.ps1 and tried to run with default policy). Actually Power Shell tells you this, if you try to run one as shown,
=====
.\Hello.ps1 : File C:\Users\Jayaram\DEsktop\Hello.ps1 cannot be loaded because running scripts is disabled on this
system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ .\Hello.ps1
+ ~~~~~~~~~~~
    + CategoryInfo          : SecurityError: (:) [], PSSecurityException
    + FullyQualifiedErrorId : UnauthorizedAccess
====
OK. How do you change this policy?

Well, you need the policy changed to 'Remote signed'.
What does 'Remote Signed' mean to you?
It means,
- Scripts can run. This is the default execution
              policy in Windows Server 2012 R2.
            - Requires a digital signature from a trusted
              publisher on scripts and configuration files that
              are downloaded from the Internet (including
              e-mail and instant messaging programs).
          - Does not require digital signatures on scripts that
              you have written on the local computer (not
              downloaded from the Internet).
            - Runs scripts that are downloaded from the Internet
              and not signed, if the scripts are unblocked, such
              as by using the Unblock-File cmdlet.
            - Risks running unsigned scripts from sources other
              than the Internet and signed, but malicious, scripts.

How do we change the execution policy?
Well, Power Shell has this "Set-ExecutionPolicy" command. The syntax is:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

There is one more thing you need to take care of before you change the policy and that is, you should run the command with elevated permissions (you should run the script as an Administrator of the local machine). If you do not ,you will get this exception:
================
PS C:\Users\Jayaram\DEsktop> Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose
you to the security risks described in the about_Execution_Policies help topic at
http://go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): y
Set-ExecutionPolicy : Access to the registry key
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell' is denied. To change the execution
policy for the default (LocalMachine) scope, start Windows PowerShell with the "Run as administrator" option. To
change the execution policy for the current user, run "Set-ExecutionPolicy -Scope CurrentUser".
At line:1 char:1
+ Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : PermissionDenied: (:) [Set-ExecutionPolicy], UnauthorizedAccessException
    + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.SetExecutionPolicyComma
   nd
===============================
After launching Windows PowerShell as an administrator you will have more powers.

The following code listing shows how to run the command.
====
PS C:\Windows\system32> Set-ExecutionPolicy -Scope CurrentUser
cmdlet Set-ExecutionPolicy at command pipeline position 1
Supply values for the following parameters:
ExecutionPolicy: RemoteSigned

PS C:\Windows\system32>
==========================
After you type in RemoteSigned and return, you get a message from the ExecutionPolicyChange as follows:
"The execution policy helps protect you fromscripts that you do not trust. Changing the execution policy might expose you to security risks described in the about_Execution_Policies help topic at http://go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy? with three ptions, yes,no and suspend.
Click Yes.
You will not get a message that the policy has been changed but it will have changed.
========
Now we run the file as shown in the next listing.
=========
PS C:\windows\system32> C:\Users\Jayaram\Desktop\Hello.ps1
Hello World
PS C:\windows\system32>
================
Finally what was the content of the 'Hello.ps1'?

It is the 'Hello World' example and the file has only the following content with just two lines typed into Notepad and saved to Desktop as 'Hello.ps1':
============
$StringToPrint="Hello World"
Write-output $StringToPrint

 

A quick note on writing PowerShell scripts - Part 1

PowerShell scripts are a sequence of PowerShell commands written one after another (executed in that order) using a text editor such as Notepad. The extension of a PowerShell script file is <filename>.ps1. The 1 ins ps1 has nothing to do with the version of PowerShell on your computer and you may have more than one in
your computer,
 http://hodentekhelp.blogspot.com/2014/08/can-multiple-versions-of-powershell-can.html

You also have the option of choosing which version you want to run
(http://hodentekhelp.blogspot.com/2014/08/if-you-have-multiple-versions-of-power.html).

In addition to Notepad there are other dedicated script editors like,
and many others. For a complete listing follow this link:

In order to run a PowerShell script file you need to create a file with extension ps1 and then run
this script in Windows PowerShell or a similar programme.

In Part 2 you will learn writing a simple script file.

Monday, November 10, 2014

SQL Server Driver for PHP released

SQL Server driver (SQLServer31.exe, 499KB, version 3.1.CTP) for PHP is now available as a CTP. Now PHP scripts can access Microsoft SQL Server and Microsoft Azure SQL Database.

You can download the CTP here:
http://www.microsoft.com/en-us/download/details.aspx?id=20098&WT.mc_id=Blog_SQL_Announce_DI

Details of this version:
  • This version supports PHP Version 5.5 and requires Microsoft ODBC driver 11 (or higher)
  • It supports SQL Server 2012; localDB abd AlwaysOn features
  • Buffered Queries are included
All SQL Server user can get it at no extra cost.

This widens the interoperability range.
The soon to be released JDBC driver compatible with JDK7 for SQL Server will enable applications to connect to SQL Server widening connectivity with the Java Platform.

Saturday, August 30, 2014

How do you use SQL Server PowerShell provider?

The following post looks at SQL Server 2012 instance on a Windows 8 Professional Computer.

SQL Server PowerShell provider enables you easily and interactively look at all objects much like you look at all the objects in a folder/file system.  You can use the command line to traverse paths in a SQL Server.

The server objects available as folders when you use Windows Power Shell are:
SQL
SQL Policy
SQL Registration
Utility
DAC
Data Collection
SQL Server Integration Services
SQL Server Analysis Services


In order to acces objects on SQL Server you need to run SQLPS.exe which you find in the following directory:
---------------------------------------------
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules>dir
 Volume in drive C is TI10672700E
 Volume Serial Number is 16FB-D230

 Directory of C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\M
odules

 
07/06/2014  06:33 PM    <DIR>          .
07/06/2014  06:33 PM    <DIR>          ..
07/06/2014  06:37 PM    <DIR>          SQLASCMDLETS
07/06/2014  06:40 PM    <DIR>          SQLPS
               0 File(s)              0 bytes
               4 Dir(s)  880,764,026,880 bytes free

----------------------
When you run SQLPS by double-clicking the file you spawn a new scripting shell 

------------
Microsoft SQL Server PowerShell
Version 11.0.2100.60
Microsoft Corp. All rights reserved.

which you can use with SQL Server as shown:
PS SQLSERVER:\>
--------------
In this shell you can access the previously mentioned SQL Objects much like you would acces folders/files using you DOS commands as shown here.
---------------
PS SQLSERVER:\> cd SQL
PS SQLSERVER:\SQL> cd..
PS SQLSERVER:\> cd SQLPolicy
PS SQLSERVER:\SQLPolicy> cd..
PS SQLSERVER:\> cd SQLRegistration
PS SQLSERVER:\SQLRegistration> cd..
PS SQLSERVER:\> cd Utility


How do we access the Server databases?
In order to do this you have to know the SQL Server Instance(s) and then the objects can be accessed as shown here:
-------
Get the instance name:
--------------------------
PS SQLSERVER:\> cd SQL
PS SQLSERVER:\SQL> dir

MachineName
-----------
HODENTEK8
localhost


PS SQLSERVER:\SQL> cd LocalHost
PS SQLSERVER:\SQL\LocalHost> dir
Instance Name
-------------
REGENCYPARK
------------------------------
Let us say we want all the objects on the AdventureWorks 2012 database on this instance.
You can find them as shown here:
--------------
PS SQLSERVER:\SQL\LocalHost\RegencyPark\Databases\AdventureWorks2012>
ApplicationRoles
Assemblies
AsymmetricKeys
Certificates
DatabaseAuditSpecifications
Defaults
ExtendedProperties
ExtendedStoredProcedures
Federations
FileGroups
FullTextCatalogs
FullTextStopLists
LogFiles
PartitionFunctions
PartitionSchemes
PlanGuides
Roles
Rules
Schemas
SearchPropertyLists
Sequences
ServiceBroker
StoredProcedures
SymmetricKeys
Synonyms
Tables
Triggers
UserDefinedAggregates
UserDefinedDataTypes
UserDefinedFunctions
UserDefinedTableTypes
UserDefinedTypes
Users
Views
XmlSchemaCollections

That's all for now.

Mahalo

Accessing PowerShell in SSMS go here:
http://hodentekmsss.blogspot.com/2014/04/starting-power-shell-from-sql-server.html

Lower prices with enhanced SLAs for Azure SQL Databases in September

Another name is price war and the battle is on going on all fronts. There has been news on the
internet regarding Microsoft's pricewar with Amazon and Google vis-a vis its cloud offerings.
http://www.informationweek.com/cloud/infrastructure-
as-a-service/microsoft-battles-google-amazon-in-
cloud-price-war/d/d-id/1141625

Although Cloud business (the thriving one) is only a small part of Microsoft right now it makes sense to make it invincible.

http://www.citeworld.com/article/2456966/business-money/microsofts-cloud-business-is-on-fire.html

Old prices and service tiers here:
http://hodentek.blogspot.com/2014/05/microsoft-azure-sql-database-service.html

Come September the price for Azure SQL Databases will be  slashed.
Reduced pricing and enhanced SLA available in September. Read here:
http://azure.microsoft.com/blog/2014/08/26/new-azure-sql-database-service-tiers-generally-available-in-september-with-reduced-pricing-and-enhanced-sla/

The new pricing is as in the following image from the above site:

Note that a new S0(S-Zero) performance level has been added in the Standard tier meant for lower-cost entry point.

Sunday, August 24, 2014

SQLDiag on Windows 8.1 Professional

SQLDiag utility collects diagnostics information from SQL Server(s) including System information. This utility was run against a SQL Server 2012 Developers edition on a Windows 8.1 Professional OS. It is available in all versions starting from SQL Server 2005 to SQL Server 2014.

SQLDIAG collects the following types of information:

•Windows Event Logs (Application, System, and Security) in .CSV files

•System Monitor (aka Perfmon) performance counters in .BLG file/s

•SQL Server Profiler traces in .TRC file/s

•SQL Server Blocking chains in a _BLK.TRC file

•SQL Server error logs, configuration, point-in-time snapshots of several DMVs in
a .OUT file

•System information via the Msinfo32 utility in a .TXT file
You can customize the utility by editing (manually)  the XML configuration file
of the utility, SQLDiag.xml
There is also a GUI tool which you can use to visually edit the xml file at the
codeplex site here.
http://sdct.codeplex.com/

File location on your computer:
If you have installed SQL Server in the present post SQL Server 2012 it should be
installed in the same directory as SQL Server which is usually,
%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn
Running it in console:
Open the command CMD preferably in Runas Administrator mode. Change the directory
to the one menitoned above.
------------
Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
C:\Users\Jayaram>cd %ProgramFiles%\Microsoft SQL Server\110\Tools\Binn
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>
---------------
Now type SQLDiag /? to get the help on SQLDiag Utility
-------------
Oops! on Windows 8.1 Professional instead of showing the help for this item, it
goes to User Access Control and if you choose to run it, the executbale is run as shown. Probably this is a bug.
Let us take up that problem later. The following console message is received  when the program starts:
2014/08/22 17:27:00.62 SQLDIAG Command line parameter C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLdiag.exe
2014/08/22 17:27:00.62 SQLDIAG Collector version 
2014/08/22 17:27:00.62 SQLDIAG
IMPORTANT:  Please wait until you see "Collection started" before attempting to reproduce your issue
2014/08/22 17:27:00.63 SQLDIAG Output path: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDIAG\
2014/08/22 17:27:00.83 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\RS\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\Microsoft SQL Server\MSSQL11.REGENCYPARK key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Collecting from 1 logical machine(s)
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\RS\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\Microsoft SQL Server\MSSQL11.REGENCYPARK key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.85 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.85 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 259. Message: No more data is available.
2014/08/22 17:27:00.87 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 259. Message: No more data is available.
2014/08/22 17:27:00.87 <computername>\* Error opening MSSQLSERVER key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.87 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 259. Message: No more data is available.
2014/08/22 17:27:00.87 <computername>\* SQL Server version: 11
2014/08/22 17:27:00.88 <computername>\* Machine name: <computername> (this machine) 
2014/08/22 17:27:00.88 <computername>\* Target machine is not a cluster
2014/08/22 17:27:00.88 <computername>\* Instance: REGENCYPARK (64-bit)
2014/08/22 17:27:10.85 SQLDIAG Initialization starting...
2014/08/22 17:27:28.76 <computername>\* MsInfo: Get MSINFO32
2014/08/22 17:27:32.58 <computername>\* MsInfo: Get default traces
2014/08/22 17:27:36.72 <computername>\* MsInfo: Get SQLDumper log
2014/08/22 17:27:41.89 SQLDIAG Initialization complete
2014/08/22 17:27:41.89 <computername>\* Collecting diagnostic data
2014/08/22 17:27:42.89 SQLDIAG Collection started.  Press Ctrl+C to stop.
2014/08/23 20:34:00.35 SQLDIAG Ctrl+C pressed.  Shutting down the collector
2014/08/23 20:34:03.16 <computername>\* Shutting down the collector
2014/08/23 20:34:03.20 <computername>\* Getting SQLConfig report(s)
2014/08/23 20:34:30.15 SQLDIAG Ending data collection.  Please wait while the process shuts down and files are compressed (this may take several minutes)
In this case the collection was terminated and it can quite a long time.
The results are written to files found in the SQLDiag folder
C:\Users\Jayaram>%Program Files%\Microsoft SQL Server\110\Tools\Binn\SQLDiag



 

Tuesday, July 22, 2014

July 2014 Update to SQL Server Data Tools

Latest release of SQL Server Data Tools(SSDT) is now available here:

http://msdn.microsoft.com/en-us/data/hh297027

This update is now available for Visual Studio 2012 and 2013

Instead of the above link you can also get it from inside Visual Stuido 2012 via Tools -> Extensions and Updates->Updates.

What's new in this:
Of course more bug fixes and some of the following enhancements:
  • Schema Compare update
         Added MSBuild support for Schema Compare with text and XML output.  A blog post is    available with more information.
  • Improved Windows Azure SQL Database node in the Server Explorer
        Added Token-based authentication using a Microsoft account (MSA) or organizational account (OrgId)
        Added supported for VS2012
  • Improved (Parallel Data Warehouse) PDW support
       PDW tooling is now part of the Microsoft Visual Studio Express 2013 for Windows Desktop SKU.  This requires the VS 2013 Update 2 or later to be installed
      Support for PDW appliance updates in both VS2012 and VS2013

Also read:
http://brianwmitchell.com/2013/11/sql-server-to-pdw-migration-whitepaper/

SSDT for SQL Server 2014 here:
http://hodentek.blogspot.com/2014/03/sql-server-data-tools-to-go-with-sql.html

SSDT BI_Templates here:
http://hodentek.blogspot.com/2013/10/ssdt-bi-templates-for-sql-server-2012.html

SSDT Web Site:
http://msdn.microsoft.com/en-us/data/tools.aspx