Wednesday, May 13, 2015

SQL Server 2016 Public Preview Coming Soon

The first public preview of SQL Server 2016 is imminent. This is deemed to be an historic version with real-time operational analytics; rich data visualization on mobile platforms and advanced analytics already built-in. It also has the latest hybrid cloud scenarios with latest Advanced security technology.

Key features:
Always Encrypted:
Straight from Microsoft Research. Data protection at rest (in Server) and in motion
Encryption key stays with application
Encryption, decryption takes place transparently inside the application
This feature supports the new Stretch Database as well.

Stretch Database:
This technology which is motivated to reduce cost for customers so that they can archive by moving bloated transactional data to Azure and maintain fast performance with hot data. This stretching of transactional data is dynamic and is secure using the latest Always Encrypted feature.



Real-time Operational Analytics and In-Memory OLTP:
Blazing fast transactional performance of real time operational analytics according to Microsoft. 100 times faster queries with in-memory OLTP compared to 30 times presently possible with disk-based system.




Built-in Advanced Analytics, Polybase and Mobile BI
Goes beyond OLTP to data warehousing and Business Intelligence and delivers advanced analytics made possible by Revolution Analytics. It uses R processing with excellent statistics support. Polybase will be integrated with SQL Server to address both relational and unstructured data using SQL Skills. There is a Data Scientist here!!


These are additional capabilities quoted from the blog:

•    Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always Encrypted.
•    Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load balancing.
•    Native JSON support to offer better performance and support for your many types of your data.
•    SQL Server Enterprise Information Management (EIM) tools and Analysis Services get an upgrade in performance, usability and scalability.
•    Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Azure and place your SQL Server AlwaysOn secondaries in Azure.

Download a copy of the datasheet here:

You could look up more details here (the pictures are from Microsoft Blog):



Monday, May 11, 2015

Migarting from SQL to JSON using DocumentDB migration tool

Recently Microsoft made it easy to migrate data form certain sources to DocumentDB on Microsoft Azure. Review post here.

The following source data can be migrated to DocumentDB using the migration tool:
  • CSV file or files
  • JSON file or files
  • SQL
  • MongoDB
  • DocumentDB

  • This post shows how you may obtain response to your SQL query in JSON format using the migration tool.

    Although it seems it is not supported on Windows 8.1 from download documents, the present post was created using the DocumentDB run on a Windows 8.1 machine. The download details are here.

    The following steps show how the SQL response is obtained in JSON format, a format more suitable for Internet of Things.

    This screen shows up after launch

     
    Specifying source information. Source chosen here is SQL.

    Source of SQL is an instance of SQL Server 2012 on the local machine and the query is run against the Northwind database. The connection string is OK except it is case sensitive. Type it exactly as shown. Trying to get to read from a .sql file on the drive.


    Locating the file on the drive.

     
    Just before processing.

     
    Processing in progress.

     
    The above process failed.
    -------------------------------------
    New Process to import, this time providing a query as shown.

     
    Providing location for the response from processing.



    Details of processing.

    Processing completed, zero errors.

     
    Sample of response from the migration tool at the specified file location (only a small part is reproduced)
     
     
    SQL Comments are ignored.
     
    Happy migration!
     

     

    Wednesday, May 6, 2015

    SQL Server 2016 will be in preview soon

    SQL Server 2016 will appear as a preview in October according to topics discussed at the recent Ignite meeting.



    SQL Server 2016 is supposed to provide " ....breakthrough performance for mission-critical applications and deeper insights on your data across on-premises and cloud".

    Expected new features:
    • Encryption of data on the move and at rest
    • Integration with R Programming Language
    Look up Revolution Analytics regarding R language which was acquired by Microsoft.

    SQL Server 2016 will have 'Stretch Database'.A way to push infrequently accessed data into the Microsoft Public Cloud

    The focus at Microsoft SQL Server will be on the hybrid cloud strategy that is  present neither in Amazon nor n Google cloud platforms.

    Friday, May 1, 2015

    Can Azure SQL Data Warehouse beat up Amazon's Red Shift?

    In recent times both Amazon.com and Microsoft have made bundles of cash with their cloud assets. Amazon had the advantage of being the first to get into action (2 years earlier) with Red Shift. In the Data Warehouse scenario Microsoft’s Azure SQL Data Warehouse and Red Shift are clear competitors.

    Now Scott Guthrie has challenged Amazon's Red Shift (http://aws.amazon.com/redshift/). According to Scott, Azure allows you to make adjustments to compute in just seconds that is beyond the capability of Red Shift.

    The other Azure capabilities are,
    • can pause/resume a cluster
    • can be turned 0ff so you only pay for storage
    • can be deployed and run from on premises
    Read more here:

    https://visualstudiomagazine.com/blogs/data-driver/2015/04/new-data-products.aspx
    http://sdtimes.com/microsoft-introduces-free-cross-platform-ide/2/

    Watch this Red shift video

     

    Hands-on Learning Event in Honolulu 2: SQL Server Reporting Services 2012

    The class is forming and is expected to start in August 2015. Please register at the PCATT.ORG site.





    SQL Server 2012 Express or Developer Edition will be used.

    For details you can also write to:
    Hodentek@live.com with course name in the Subject line.

    Here are Reporting Services books published by the instructor:

     
     
    You can buy these and many other computer and programming related books here:
     
     

    Tuesday, April 28, 2015

    Hands-on Learning Event in Honolulu: Introduction to Structured Query Language

    This was offered once in 2012 and once in the beginning of this year and was a total success. It is offered once again to those who could not make it.
    Please register at the PCATT.ORG site.

    New in 2015: You will also get an introduction to Windows PowerShell. SQL Server 2012 Express will be used.

    For details you can also write to:
    Hodentek@live.com with course name in the Subject line.

    Attaching a detached database in SQL Server 2012 with Windows PowerShell

    In my previous post a simple way to detach a database from SQL Server 2012 using SQL Server Management Studio was described. The same database will be attached in this post.

    It can be attached to the server instance with SQL Server Management Studio in the same way.

    However, trying to attach the detached database using Windows PowerShell succeeded but the database name was not restored but the database files were successfully attached. This was an unexpected result.

    The next picture shows the databases node before and after attaching the Feb6 database using the code shown in this post. After attaching the database while the name of the database expected was "Feb6", a new system assigned name was given to the attached database.


    The PowerShell statements for attaching the detached database were run in Windows PowerShell ISE and are as follows:
    --
    PS SQLSERVER:\sql\Hodentek8\RegencyPark\databases>
    $files = new-object system.collections.specialized.stringcollection
    $files.add("C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA\Feb6.mdf")
    $files.add("C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA\Feb6_log.ldf")
    $server=new-object Microsoft.SqlServer.Management.Smo.Server('Hodentek8\RegencyPark')
    $db=new-object Microsoft.SqlServer.Management.Smo.Server
    $dbname="feb6"
    $server.AttachDatabase($db,$files)
    ----------
    Note that the code was run in the databases context.

    Although I specified the database name as feb6, the database name was [Hodentek8] after attaching. Hodentek8 is the host name.

    Looking at the properties of the database [Hodentek8] shows that the files of database 'Feb6' were attached.

    I do not find Feb6 in the databases collection.


    However, I am still figuring out the name change.