SQL 2014 RTM

SQL2014 is finally here. The evaluation is ready for people to start downloading. It has been release on TechNet evaluations and MSDN.

SQL 2014 Evaluation:

http://technet.microsoft.com/en-au/evalcenter/dn205291.aspx

SQL 2014 Training:

http://www.microsoftvirtualacademy.com/training-courses/mission-critical-performance-with-sql-server-2014-jump-start

Some of the key new features are;

  • In-Memory OLTP: Provides in-memory OLTP capabilities built into core SQL Server database to significantly improve the transactional speed and throughput of your database application. In-Memory OLTP is installed with the SQL Server 2014 Engine without requiring any additional actions and allows in-memory performance benefits without rewriting your database application or refreshing your hardware. In-Memory OLTP allows you to access the other rich features in SQL Server, while taking advantage of in-memory performance.
  • In-Memory Updateable ColumnStore: Provides higher compression, richer query support and updateability of the existing ColumnStore for data warehousing workloads giving you even faster load speed, query performance, concurrency, and even lower price per terabyte.
  • Extending Memory to SSDs: Seamlessly and transparently integrates solid-state storage into SQL Server by using SSDs as an extension to the database buffer pool, allowing more in-memory processing and reducing disk IO.
  • Enhanced High Availability
    • New AlwaysOn features: Availability Groups now support up to 8 secondary replicas that remain available for reads at all times, even in the presence of network failures. Failover Cluster Instances now support Windows Cluster Shared Volumes, improving the utilization of shared storage and increasing failover resiliency.
    • Improved Online Database Operations: Includes single partition online index rebuild and managing lock priority for table partition switch, reducing maintenance downtime impact.
  • Encrypted Backup: Provides encryption support for backups, on-premise and in Windows Azure.
  • IO Resource Governance: Resource pools now support configuration of minimum and maximum IOPS per volume, enabling more comprehensive resource isolation controls.
  • Hybrid Scenarios:
    • SQL Server Backup to Azure: Provides management and automation of SQL Server backups (from on-premise and Windows Azure) to Windows Azure storage.
    • AlwaysOn with Azure Secondaries: Easily add replicas in Windows Azure to on-premise Availability Groups.
    • SQL XI (XStore Integration): Supports SQL Server Database files (from on-premise and Windows Azure) on Windows Azure Storage blobs.
    • Deployment Wizard: Easily deploy on-premise SQL Server databases to Windows Azure.
  • SQL Server 2008 R2 SP2 Now Available

    The SQL Server team has released SQL 2008 R2 SP2!

    See here for more details: http://blogs.technet.com/b/dataplatforminsider/archive/2012/07/26/sql-server-2008-r2-sp2-now-available.aspx

    SQL Server Team

    As part of our commitment to delivering and supporting high-quality software to our customers, Microsoft is pleased to announce SQL Server 2008 R2 SP2. Customers with existing investments on SQL Server 2008 R2 are encouraged to explore Service Pack 2. Service Pack 2 for SQL Server 2008 R2 includes product improvements based on requests from the SQL Server community and hotfix solutions provided in SQL Server 2008 R2 SP1 Cumulative Updates 1 to 5. A few highlights are as follows:

    • Reporting Services Charts Maybe Zoomed & Cropped
      Customers using Reporting Services on Windows 7 may sometime find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false.
    • Batch Containing Alter Table not Cached
      In certain situations with batch files containing the alter table command, the entire batch file is not cached.
    • Collapsing Cells or Rows, If Hidden Render Incorrectly
      Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.

    Customers are highly encouraged to stay on a supported service pack to ensure they are on the latest and most secure version of SQL Server 2008 R2.

    Download today!

    · SQL Server 2008 R2 SP2

    · SQL Server 2008 R2 SP2 Express

    · SQL Server 2008 R2 SP2 Feature Packs

    SQL 2012 PowerShell script to detect CPU’s configured for licensing

    SQL 2012 has changed the license model around CPUs/Cores. The below script can be used to help detect how many CPU’s are on the host and also configured in SQL. If you detect that you do not have the correct CPU’s, then you may need to reconfigure SQL.

    Example outputs:

    SQL Server CPU check
    Checking server instance TESTSQL
    —————————————————
    Server Physical Cores : 4 ( 2399 mhz )
    Server Logical Cores  : 4
    SQL Server Cores      : 4
    SQL Edition           : Standard Edition (64-bit)
    —————————————————

    SQL Server CPU check
    Checking server instance TESTSQL
    —————————————————
    Server Physical Cores : 4 ( 2399 mhz )
    Server Logical Cores  : 4
    SQL Server Cores      : 2
    SQL Edition           : Standard Edition (64-bit)
    —————————————————

    Script:

    ################################################################################
    #
    # Script: DetectCPUCores.ps1
    #
    # Author: Craig Wilson
    # Date: 22/06/2012
    # Version: v1.0.0
    #
    # This script will connect SQL and the WMI of then host to collect CPU core
    # information
    ################################################################################
    
    function DetectCPUCores ([string]$SQLINSTANCE, [string]$HOSTNAME)
    {
    
        Write-Host "SQL Server CPU check"
        Write-Host " Checking server instance $SQLINSTANCE"
        Write-Host "---------------------------------------------------"    
    
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
        $SqlConnection.ConnectionString = "Data Source=$SQLINSTANCE;Integrated Security=TRUE" 
        $SqlConnection.Open() 
    
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
        $SqlCmd.CommandText = "select serverproperty('ProductVersion')" 
        $SqlCmd.Connection = $SqlConnection 
        $sql_ProductVersion = $SqlCmd.ExecuteScalar() 
    
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
        $SqlCmd.CommandText = "select serverproperty('Edition')" 
        $SqlCmd.Connection = $SqlConnection 
        $sql_edition = $SqlCmd.ExecuteScalar() 
    
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
        $SqlCmd.CommandText = "select count(*) from sys.dm_os_schedulers where status = 'VISIBLE ONLINE'" 
        $SqlCmd.Connection = $SqlConnection 
        $sql_schedulers = $SqlCmd.ExecuteScalar() 
    
        $SqlConnection.Close() 
    
        $property = "systemname","maxclockspeed","addressWidth","numberOfCores", "NumberOfLogicalProcessors"
        $cpu_info = Get-WmiObject -computername $HOSTNAME -class win32_processor -Property  $property | Select-Object -Property $property 
    
        Write-Host "Server Physical Cores :"$cpu_info.numberOfCores "("$cpu_info.maxclockspeed"mhz ) "
        Write-Host "Server Logical Cores :"$cpu_info.NumberOfLogicalProcessors
        Write-Host "SQL Server Cores : $sql_schedulers"
        Write-Host "SQL Edition : $sql_edition"
        Write-Host "SQL Product Version : $sql_ProductVersion"
        Write-Host "---------------------------------------------------"
    
    }
    
    DetectCPUCores "SQLInstance" "LOCALHOST"
    
    
    
    
    
    

    For more information on how the licensing impact you check out the following blog posts

    SQL Server 2012 Enterprise Editions

    http://blogs.msdn.com/b/saponsqlserver/archive/2012/06/15/sql-server-2012-enterprise-editions.aspx

    SQL Server 2012 Licensing (Resources Pane)

    http://www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx

    Announcing PASS Summit 2012 Community Sessions and Pre-Con

    SQL PASS Summit 2012 is open for registration, go to http://www.sqlpass.org/summit/2012/ for more details. Here is the announcement.

    A Who’s Who of leading SQL Server authorities – including SQL Server MVPs, authors, trainers, and consultants – will share their expertise at PASS Summit 2012 , Nov. 6-9, in a record 195 sessions, hand-picked to help you get the most out of your database implementations and technical careers.

    Today, we’re excited to announce this year’s community sessions, including:

    Plus, join us for 2 days of in-depth, full-day Pre-Conference Sessions by some of the top experts in the SQL Server community Monday, Nov. 5, and Tuesday, Nov. 6.
    A big thanks to everyone who submitted sessions and to our hardworking review teams, and stay tuned for sessions from Microsoft’s leading experts coming soon.
    Until then, don’t miss your chance to save $700 on PASS Summit registration, only until June 30.

    SQL Versions and their latest Cumulative Updates (CU)

    Microsoft releases a number of updates for SQL and other products. The following table outlines the current versions of SQL server with the Service Pack and Cumulative update levels.

    Product Version Latest Service Pack Latest Cumulative Update Release of CU Build Number
    SQL Server 2012 CU 2 (KB 2703275) 18-06-2012 11.0.2325.0
    SQL Server 2008 R2 SP1 (KB 2528583) CU 6 (KB 2679367) 16-04-2012 10.50.2811.0
    SQL Server 2008 SP3 (KB 2546951) CU 5 (KB 2696626) 31-05-2012 10.00.5785.00
    SQL Server 2005 SP4 (KB 2463332) CU 3 (KB 2507769) 22-03-2011 9.00.5266
    SQL Server 2000 SP4 (KB 290211)

    Cumulative update package 2 for SQL Server 2012 is available

    Microsoft has now released Cumulative update package 2 for SQL 2012.

    Check the Update Center for Microsoft SQL server(http://technet.microsoft.com/en-us/sqlserver/ff803383.aspx) for other SQL updates.

    SQL Server 2012 CU 2 (Build 11.0.2325.0) Download:

    KB 2703275 http://support.microsoft.com/kb/2703275

    Microsoft SQL Server 2012 Exams

    The entire Microsoft SQL Server 2012 exam portfolio is now available!

    Explore the different exams available on SQL Server 2012.

    Learning Plans and Classroom Training for this exam:

    Learning Plans and Classroom Training for this exam:

    Learning Plans and Classroom Training for this exam:

    Learning Plans and Classroom Training for this exam:

    Announcing Windows Azure SQL Reporting General Availability

    The Microsoft BI Team has announced SQL Reporting on Azure!

     

    Read more here:

    http://blogs.msdn.com/b/microsoft_business_intelligence1/archive/2012/06/12/announcing-windows-azure-sql-reporting-general-availability.aspx

     

    With SQL Reporting on Azure, developers can use familiar tools such as the Business Intelligence Development Studio and SQL Server Data Tools to author reports, just as they do today when running SQL Server Reporting services on-premises. SQL Reporting on Azure provides consistent APIs to view, execute and manage reports along with rich formatting and data visualization options.

    Additional capabilities of SQL Reporting on Azure include:

    Elastic scale and high availability

    • The SQL Reporting scales as your requirements grow with easy self-provisioning.
    • The Service has built-in high availability and fault tolerance. SQL Reporting is available across Microsoft Data centers around the world.

    Report Formats

    • With SQL Reporting, you have the ability to export reports to various popular file formats including Excel, Word, HTML, PDF, XML, and CSV.

    Secure access

    • The rich authentication and authorization model in SQL Reporting gives secure access to reports and underlying data. It keeps your data secure while offering access to even more users.

    SQL Reporting on Azure offers a fully backed SLA and enables you to publish reports to the cloud or embed reports directly within on-premises applications that can be accessed via your browser, mobile devices or desktops.

     

    clip_image002

    Poster showing SQL Azure Federation features, process, and key terms

    Microsoft has releases a poster explaining the federation process for Azure.

    http://www.microsoft.com/en-us/download/details.aspx?id=29213

    Overview

    The poster explains the federation process, including creating the federation root database, creating the scheme, and then using the split operation to partition the federation member into two partition members by row. It also explains the database infrastructure and includes sample T-SQL statements.

    image

    http://www.microsoft.com/en-us/download/details.aspx?id=29213

    SharePoint Recovery with SQL and SharePoint Backup

    There has been some confusion around backup with SharePoint agents over SQL native backups. In short you should use one and only one. Pick a solution and test it, then test it again, and test once more for luck. SharePoint agent and SQL native backups both have their place in recovery but can create problems when used together.

    SQL is a transactional databases, SharePoint is a transactional system; both need to be reviewed when you create your backup solution. While this post will not cover different recovery options I will highlight a problem with point in time recovery.

    The issue is when you backup with the SharePoint native backup (PowerShell Backup-SPFarm cmdlet or Central Administration) it updates the LSN in SQL.

    First let’s start by checking the LSN in SQL:

    use msdb;

    select database_name,first_lsn,last_lsn,checkpoint_lsn from dbo.backupset where database_name =
    ‘SP_Portal_PortalContent’;

    Returns:

    Next, let’s open a SharePoint PowerShell (As Administrator) and kick off a backup

    Now that the SharePoint Backup has completed, let’s check the SQL LSN again.

    You can see now that the number of backups has increased. SharePoint has used SQL Native Backup to back up the database.

    So what happens now when we do a transaction log back and point in time restore?

    Let kick of a transaction log backup;

    To do this I just created a standard SQL maintenance plan to run a transaction log backup on the database in question.

    Let’s check the LSN again and you will see another line has been added which is our transaction log file;

    So the next question is how to restore?

    If you open SQL manager and kick off a restore, SQL will default to the last Full Backup and any Transaction logs from the last full back up till now.

    As you can see the last Full Backup is the SharePoint Native Backup we run from power shell. An SQL restore now needs the SharePoint Backup files to recover.

    If we attempt to restore, after a few days, the SharePoint Backup location may have changed. The SQL will report a missing device.

    While the file is maybe safe somewhere on the file system, if the SQL server cannot find the file the below error will happen.

    So now we need to find the backup files. The files will be located in the SharePoint backup location. To find this run the following query;

    SELECT dbo.backupset.database_name, dbo.backupmediafamily.physical_device_name


    FROM dbo.backupset


    INNER
    JOIN dbo.backupmediafamily


    ON dbo.backupset.backup_set_id=dbo.backupmediafamily.media_set_id where dbo.backupset.database_name =
    ‘SP_Portal_PortalContent’

    Now we can see the location of the backup file.

    If we need to restore, we would need the SharePoint Backup file and the SQL transaction log file.