Saturday, April 12, 2014

Starting Power Shell from SQL Server 2012

It's quite easy as Power Shell is integrated with SQL Server 2012.

It is quite easy to start from SQL Server 2012.

I have SQL Server Express 2012.

I open the SQL Server Management Studio 2012 from it shortcut in All Programs as shown.



In the Connect to Server dialog I fill in the details as shown.




Make sure you have started the SQL Server Windows Service from Control Panel.


If you still cannot connect, you may have connection problems.

Once connected to SQL Server click open a database node as shown. I have chosen Northwind database node, but you can choose any other node including System Databases or even Reporting Services.

Right click database node to display options.




Click on Power Shell to open DOS window for SQL Server Power Shell as shown.



You are in guys!


Well you have to learn how to work with Power Shell and SQL Server.



You can get a good intro to PowerShell/SQLServer 2012 here:

http://technet.microsoft.com/en-us/library/hh245198.aspx

Or you can get some hands-on help here:

You can buy this from here.

You can get details from http://hodentek.blogspot.com


Saturday, April 5, 2014

Active Geo-replication and Self-Service Restore features in the April 3 Update

For Premium Tier (GA) clients, Microsoft's Scott Guthrie announced that SQL databases will be available to store up to 500GB of data. This is a very significant jump from 150 GB. Yet another thing announced was the SLA covering 99.95% of the time.

http://blogs.msdn.com/b/windowsazure/archive/2014/04/04/sql-database-updates-coming-soon-to-the-premium-preview.aspx

* Active Geo-Replication:

More fail-over options /disaster recovery options with Geo Replication of SQL Azure databases. This feature is also available on Azure Storage. Of course these are for premium subscriptions only for now (available at the end of the month). You can create up to 4 active,readable secondarily in any Azure region allowing you to comply with data regulations .


  • Microsoft Adds Self-Service Restore
SQL Azure creates  backup automatically for up to 35 days although this feature will be available towards the end of the month when it is enabled. This should releve the onus on export/import service.


The above graphics are from Scott's blog.

Another resource for this info here:

http://techcrunch.com/2014/04/03/microsofts-azure-sql-can-now-store-up-to-500gb-gets-99-95-sla-and-adds-self-service-recovery/


Tuesday, April 1, 2014

SQL Server 2014 is ready to download. Download and Install now

SQL Server 2014 has lots of new stuff. If you worked with SQL Server  2012 it is time to upgrade. If you experimented with  SQL Server 2014 CTP1, you need to completely uninstall it.
 
 
Here are some highlights of this edition:
 
 
 
 

You can download SQL Server 2014 from the Technet Evaluation Center here:
http://technet.microsoft.com/en-US/evalcenter/dn205290.aspx

You can also download the express version here:
http://msdn.microsoft.com/en-US/evalcenter/dn434042.aspx

You get the following programs in the express edition:
  • LocalDB (SqlLocalDB)
  • Express(SQLEXPR)--Database Engine only
  • Express with Tools(SQLEXPRT)---Install & Configure database server+ SSMS
  • SQL Server Management Studio Express(SQLManagementStudio)
  • Express with Advanced Services(SQLEXPRADV)--includes Full Text Search & Reporting Services
Summary of some basic requirements:
  • .NET 3.5 SP1 is a requirement and is not installed by set up.
Download the above from here:
  • .NET 4.0 is also a requirement but the installaiton program downlaods and installs it.
  • PowerShell 2.0 is also a prerequisite and you have to install it if it is not present.
Operating Systems on which you can install:

Full list can be accessed here:
http://msdn.microsoft.com/en-us/library/ms143506(v=sql.120).aspx#hwswr
  • Enterprise and Business Intelligence versions installs only on Windows Servers (2012 and 2008 R2)
  • Standard Edition can be installed on Windows 7(x32 and x64) and windows 7 SP1(Ultimate to Professional)
  • SQL Developer covers the widest Windows operating Systems.
  • SQL Express can be installed on above mentioned Servers as well Windows 8, Windows 8.1 and windows 7 SP1


 

Sunday, March 30, 2014

SQL Training and SQL Server Reporting Services 2012 training in Honolulu

These are the training schedules for the2nd Quarter of 2014 at the Pacific Center for Advanced Technical Training (PCATT). There are two courses that you may be interested in, it is recommended that they are taken in the order shown to reap maximum benefit.

You can also access the reviews of the previous session in 2013 at the PCATT site.

Introduction to Structured Query Language (SQL)


SQL Server Reporting Services 2012: Native Mode Implementation

Please register with  http://pcatt.org/registration    or send an email to gcerny@hawaii.edu

Thursday, March 27, 2014

March update of SQL Server Data Tools to support SQL Server 2014

On the heels of the new release date for SQL Server 2014 RTM, SQL Server Data Tools' (SSDT) availability was announced two days ago. It is fully compatible with SQL Server 2014.

Apart from bug fixes in the previous version, the following are the enhancements:
  • Static code analysis extensibility
  • Filter capabilities in the editable data grid
  • Saving data compare settings to file (.dcmp)
  • Additional TSQL Editor connection actions
With this all database tools are fully integrated with,
  • Microsoft Visual Studio 2013 Express for web
  • Microsoft Visual Studio 2013 for Windows Desktop
  • Professional
  • Premium
  • Ultimate
These will be pushed via VS Updates. You could also bring it in using Tools | Extensions and Updates menu.

Visual Studio 2013 with SQL Server tools can be downloaded from here:
http://www.visualstudio.com/downloads/download-visual-studio-vs

Note that both SSDT VS 2010 and SSDT VS 2012 are available stand alone.
Review what's new in this update here:
http://blogs.msdn.com/b/ssdt/archive/2014/03/25/sql-server-data-tools-for-sql-server-2014-is-available.aspx

Review the posts for earlier version:
http://hodentek.blogspot.com/2012/12/sql-server-data-tools-gets-updated.html

http://hodentek.blogspot.com/2013/02/about-ssdt-and-vs-2012.html

Follow link for more:
http://msdn.microsoft.com/en-us/data/hh297027

In SQL Server 2014 you can use Multidimensional model to create Power View

This feature is new in SQL Server 2014.

In SQL Server 2012 Reporting Services you had the option to create Power Views using the Reporting Services Service in SharePoint 2010. In order to create the Power Views you needed to create tabular data model in SQL Server Analysis Services 2012. You needed a separate instance of SQL Server Analysis Services installed to support Tabular Data Model as the default was Multi-dimensional.

With SQL Server 2014 Power Views can be created using Multidimensional models, the same model used in OLAP Cubes. Just as Tabular Data Models supported Data Analysis Expressions(DAX), multi-dimensional models also support querying with DAX.

Power View is not exclusive to SharePoint but can be created using Excel 2013 although somewhat limited in features compared to those created using SharePoint.

Chapter 6: Power View and Reporting Services

This chapter in my book describes how to create Power Views from scratch.

Saturday, March 22, 2014

What are 'views' in SQL Server?

Views are virtual tables created based on existing table(s). Views can have generally fewer columns than the underlying table(s). Views can be based on a single table or on multiple tables. Since you can pick and choose the columns, you can hide columns that you do not want to be seen and display only those you want. Since a view can choose from different tables (that can participate in a join), it is useful in front end design to query all the relevant tables and create a view.

For example the following query returns 91 rows of Customer data from Northwind database in SQL Server 2012 Express:

SELECT [CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[ContactTitle]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[Phone]
      ,[Fax]
  FROM [NORTHWND].[dbo].[Customers]

Let us say your front end application needs just CompanyName, Address, City, State, Country. Then you create a query as shown:

SELECT
      [CompanyName]
      ,[Address]
      ,[City]
      ,[PostalCode]
      ,[Country]+', '+[PostalCode]
     
  FROM [NORTHWND].[dbo].[Customers]

Now create the view based on this query as shown which you want to create a mail label

Create view MailLable
as
SELECT
      [CompanyName]
      ,[Address]
      ,[City]
      ,[PostalCode]
      ,[Country]+', '+[PostalCode] as 'Country'
      FROM [NORTHWND].[dbo].[Customers]

This creates a view object in View node of NORTHWND database called MailLable as shown.

The views in the database are themselves stored in a System View and you can find all existing views using the following query:

SELECT * FROM SYS.VIEWS

There are lot more things related to views we will take them up in another post.