THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

  • SQL Server vNext–Linux edition

     

    It has been an incredible day for us the SQL Server Community. First of all we have a new SQL Server 2016 SP1 (described here) which changes literally everything. 

    It is not all as we can also play with the SQL Server vNext Linux Edition.

    How can we play?

    First of all – it is a public preview (CTP1) so you can download the bits.

    In case you would like to start now that there is an option to visit TechNet Virtual Labs page.

    There are 4 labs waiting:

    image

    Don’t forget and visit the Release Notes page  and check out what’s new.

    Cool stuff is coming!!

    Cheers

    Damian

  • SQL Server 2016 SP1 shocks the world!!!

    The entire World has been shocked today! We have never expected to see something like this. Go, grab SQL Server 2016 SP1 and you get all those:

    (picture from SQL Server Release Services page)

    Holly Molly!!!!

    Now in SQL Server 2016 Express you have (for FREE):

    • row level security
    • dynamic data masking
    • database snapshot
    • columnstore indexes!!!!
    • partitioning!!!!!!!!
    • compression (page, row…)
    • in memory oltp
    • Always encrypted
    • polybase…..
    • auditing!!!!!
    • multiple filestream containers

    It is like roller coaster especially for companies that use Standard edition as their primary engine.

    For details – check the SQL Server Release Services page.

     

    Cheers

    Damian

  • Great news - MVP Reconnect has been announced

    The official announcement can be found at https://blogs.msdn.microsoft.com/stevengu/2016/11/03/announcing-the-mvp-reconnect-program-the-2016-mvp-global-summit/

    What is MVP Reconnect?

    • MVP Reconnect is Microsoft’s way of keeping former MVPs in touch with Microsoft and each other.
    • The idea behind the program can be best described with an analogy from another industry; The Oscars are one of the most well recognized and prestigious awards in the film industry. From the moment someone wins their first Oscar, they become part of a very special community – that of Academy Award Winners – and they belong to this community forever, even if they don’t win an award every year. The same is true for MVPs: once an MVP, always part of the MVP community! 

    Why is this program being launched?

    • Over the years, Microsoft has received feedback from former MVPs that they were looking for ways to stay in touch with the program and with their peers. This program is an extension of Microsoft’s commitment to the MVP program and another way to recognize the expertise and engagement that folks contribute to technical communities.

    Why should you join?

    • MVP Reconnect allows members to stay connected to other community leaders and Microsoft. If your contributions to the technical community slow down temporarily, staying connected means that you maintain a good position for future recognition as an MVP. If you have a long history with the MVP program, you’ll be able to share your experiences and coach others to take full advantage of all the opportunities that the MVP award can provide.
    • Benefits include networking opportunities, recognition, and engagement offerings like invitations to community events. 

    Who is eligible for the program? What are the requirements for membership?

    • All former MVPs with a minimum award period of one year who ended their tenure “in good standing” are eligible to join. “Good standing” means that an MVP must not have been retired due to an NDA or Code of Conduct violation. There are no limitations based on technical expertise or award category!

    Where can you find more information about the program?

    How can you join?

    • In the future, MVPs who reach the end of their award tenure will be invited automatically. 
    • Former MVPs can request to join by filling out the form at https://mvp.microsoft.com/en-us/Pages/reconnect-requestform. After validating the request, Microsoft will send an official email invitation. 
    • Please note that the validation process is made easier by providing more information on the form. Specifically, supplying your MVP ID and primary email used as an MVP will help expedite the process.

     

    Cheers

    Damian

  • What you should read :) during the upcoming !!! weekend - weekly SQL Server blogs review part 16

    I have found some great articles to be read

    Hope you will have a lot of fun reading all of them.

     

    Author

    Title

    Link

    Adam Machanic

    sp_whoisactive: The Big Fixes

    http://bit.ly/2f1ddJA

    Jorg Klein

    Azure Data Lake Analytics U-SQL decryption possibilities

    http://bit.ly/2dQ03MT

    CSS SQL Server Engineers

    How It Works: Online Index Rebuild – Can Cause Increased Fragmentation

     

    http://bit.ly/2ehn3nL

    CSS SQL Server Engineers

    SQL 2016 — Why can’t I STRETCH my database (I have the right user name and password)?

    http://bit.ly/2eCLqg4

    Grant Fritchey

    A Subquery Does Not Hurt Performance

    http://bit.ly/2f1mZvc

     

    Cheers

    Damian

  • Technical review–SysTools SQL Recovery

    SQL Server is a relational database that saves the entire data, which can be easily repaired by software when it is required. SQL MDF files are the main database files of SQL database. They are the base files of the primary data of SQL server and any damage to these files may result in the fall down of the complete database. Therefore any sort of corruption in these MDF files should be repaired and eliminate the risk of corruption of the entire database. The most common reason that is behind the corruption of MDF files is improper system shutdown. In addition, the SQL database may be infected with viruses and malwares that not only corrupt a single file but also have the capacity to destroy complete database. Now, the question comes how to retrieve the corrupted SQL database? There is one solution to overcome from such a situation is to utilize the third-party utility, i.e. SysTools SQL Recovery Software that repairs corrupt SQL database. This review discusses about the software  is based upon the testing performed by the application.

    Introduction

    This SQL file repair tool can repair both corrupted MDF as well as NDF files and export it to different file formats of a SQL Database. It repairs both primary and secondary database in, as it is form. It scans and retrieve numerous of NDF files or complete folder containing all NDF files. It supports SQL Server 2014 and all the below editions to recover database from mdf and ndf files.

    Different Versions to Repair Corrupt SQL Database

    The SQL Recovery software is available in two versions, i.e. Demo as well as Licensed. Users can select any version accordingly.

    · Demo Version

    The Demo version is totally free to utilize by simply downloading it from the official website of the SysTools Group. As it is a free edition, so there are limitations, i.e. it permits users to preview all the repaired components of SQL database, but cannot allow them to store and export them.

    · Licensed Version

    It is a paid edition of the software that can be bought from the official site of organization. It allows users to repair, save, and export all the data into SQL Server database.

    Features of SQL .mdf File Repair Tool

    Repair both MDF & NDF files

    The tools help to retrieve both primary as well as secondary database files such as MDF and NDF files. It maintains the originality of file data after repairing the database table’s data and even previews the complete components in it.

    Automatically Detects Server Edition

    The application is programmed in a way that it automatically detects the edition of both NDF as well as MDF files. However, if the edition is known by the servers then, users can check it manually on their own.

    Dual Scanning Way

    The SQL .mdf file repair tool provides two modes for scanning the complete SQL server database file. One mode is Quick scan that is for normal database corruption. Another mode is advance scan, which is for the deep corruption of the database files (.mdf & .ndf).

    Dual Scanning Way

    Store Scanned Files For Further Usage

    The SQL database repair tool provides the option to save the scanned data in .str file format. This feature enables the user to avoid re-scanning of the corrupted .mdf or .ndf file again and again.

    Supports Both Database Keys

    The SQL database repair tool supports both the primary as well as foreign keys along with database tables. Both the keys are supported after exporting the database that is performed via a software.

    Supports Advance Data Types

    The software supports the advance data types to repair SQL database that includes sql_variant, hierarchyid, geography, geometry data types, Datetime2, datetimeoffset, etc. Moreover, the application supports Unicode as well as ASCII XML data types.

    Quickly Recover  SQL .mdf File Components

    Once the database files scanned by SQL database recovery tool, It provides a preview of recovered items like Tables, Functions, Stored Procedures, Views, Triggers, Rules, Associated Primary Keys, Data Types, Unique Keys, and all the other SQL database components.

    Quickly Recover  SQL .mdf File Components

     

    Retrieve Deleted Table Data

    The data of SQL table gets deleted accidentally due to various situations. In such a situation, the software will help to recover the deleted data of table in exact form without losing the data.

    Repair SUSPECT SQL Server Database

    If the user is unable to access the database because of SUSPECT error then Software will helps to repair database which is marked as suspected.

    Export to SQL Server Database with Desired SQL Files

    The utility gives an option to export the repaired data directly to live SQL Server database. The users only need the credentials such as Database name, Username, Server name and password etc.

    After providing the above necessary information, user can export the desired database items from both MDF and NDF files and store it. It permits users to check or unchecked the recovered items accordingly and then exporting it to SQL Server database or as a SQL Server Compatible SQL Scripts.

    Export to SQL Server Database with Desired SQL Files

     

    Retains the Data Integrity

    The tool repairs the complete data files of SQL Server in exact form. It preserves the on-disk folder structure, formatting, styling, etc. in the same form as it was.

      Conclusion

    Considering the overall performance of the application I have to say I like it. It is also very intuitive and works very quickly. Although the software fails to repair the bulk, MDF files. However, it accurately repair highly corrupted data files (tested on 3 damaged databases). Moreover, it has easy and user-friendly interface, which is very necessary for users in repairing a corrupt SQL Server database. To sum it all up my recommendation for all the numerous of users is that this application is definitely worth trying.

  • SQL Server 2014 Service Pack 2 Cumulative Update #2 Released!

    The 2nd cumulative update release for SQL Server 2014 SP2 is now available for download at the Microsoft Downloads site.

    Good thing is that registration is no longer required to download Cumulative updates.

     

    VSTS bug number KB article number Description Fix area
    8147939 3191487 Query performance improvement for some columnstore queries in SQL Server 2014
    8055445 3184099 SQL Server 2012 crashes with an access violation when you use the TRY…CATCH construct for bulk copy SQL service
    8001813 3189229 FIX: "Element not found" error occurs when you browse to the FileTable database directory in SQL Server 2012 or 2016 SQL service
    8198005 3171681 Reporting Services counter is missing from Windows Performance Monitor after you install SQL Server 2014 SP1 CU6 Reporting Services
    8343827 3180060 FIX: Queries that use CHANGETABLE use much more CPU after you install SQL Server 2014 SP1 CU6 SQL service
    8198037 3181260 SQL Server Analysis Services crashes when you have multiple roles and run a CREATE SESSION CUBE statement in SQL Server 2014 Analysis Services
    8197973 3161403 The file size of the PDF file generated by SSRS 2014 or 2016 is larger when compared to the earlier versions of SQL Server Reporting Services
    8204790 3195825 FIX: A deadlock occurs when you execute a query plan that has a nested loop join between two hash joins working in the batch mode in SQL Server 2014 SQL performance
    8197979 3164104 Unable to run DBCC CHECKDB when the database files reside in Azure Premium Storage in SQL Server 2014 SQL service
    8197999 3168740 An access violation occurs when you execute two parent packages that run the same child package in parallel in SQL Server 2014 or 2016 Integration Services
    8275510 3189033 FIX: Cannot uninstall service packs for SQL Server 2014 after a cumulative update is installed Setup & Install
    8198023 3025408 FIX: Access violation occurs when you run and then cancel a query on distinct count partitions in SSAS Analysis Services
    8198033 3180263 FIX: Canceling a query takes a long time in a Tabular instance of SQL Server 2014 Analysis Services Analysis Services
    8198040 3174370 A memory leak occurs when you use Azure Storage in SQL Server 2014 or 2016 SQL service
    8292291 3189645 FIX: Access violation when you run a query that uses clustered columnstore index with trace flag 2389, 2390, or 4139
    8198029 3176993 FIX: The "MSRS 2014 Windows Service: Active Session" counter incorrectly displays a very high value for SSRS 2014 or 2016 Reporting Services
    8273664 3194961 db_name() and db_id() functions fail to trigger permissions check in SQL Server

    To learn more visit pages:

     

    Cheers,

    Damian

  • What you should read :) during the upcoming !!! weekend - weekly SQL Server blogs review part 15

    Welcome back! I have found some great articles to be read

    Hope you will have a lot of fun reading all of them.

     

    Author

    Title

    Link

    Rob Farley

    Passwords – a secret you have no right to share

    http://bit.ly/2efVLNg

    Paul White

    The sorts that spills to level 15000

    http://bit.ly/2dwd7XL

    Rob Farley

    Implementing a custom sort

    http://bit.ly/2dK24rq

    Arun Sirpal

    The dark side of Fn_dump_dblog

    http://bit.ly/2ezBMgs

    Grant Fritchey

    SELECT * Does Not Hurt Performance

    http://bit.ly/2epbCcH

     

    Cheers

    Damian

  • SQL 2016 RTM is out

    Following is the summary of all the links to acquire SQL 2016 RTM.

    The links come from Parikshit Savjani (Microsoft) so all credits to him :)

     

    Azure Link -  http://go.microsoft.com/fwlink/?LinkID=733372

    MSDN Subscribers - http://msdn.microsoft.com/subscriptions/downloads

    MSDN Technet - http://technet.microsoft.com/subscriptions/downloads

    Technet/MSDN Evaluation - https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

    MSDN Evaluation – Express - https://www.microsoft.com/en-us/server-cloud/Products/sql-server-editions/sql-server-express.aspx

    Microsoft Command Line Utilities 13 for SQL Server - http://go.microsoft.com/fwlink/?LinkID=797863

    Microsoft SQL Server 2016 Feature pack – http://go.microsoft.com/fwlink/?LinkID=398150

    Microsoft SQL Server 2016 PowerPivot for SharPoint 2013 - http://go.microsoft.com/fwlink/?LinkID=398152

    Microsoft SQL Server 2016 PowerPivot for SharePoint 2016 - http://go.microsoft.com/fwlink/?LinkID=716860

    Microsoft SQL Server 2016 RS add-in for SharePoint – http://go.microsoft.com/fwlink/?LinkID=398153

    Microsoft SQL Server 2016 MDS Add-in – http://go.microsoft.com/fwlink/?LinkID=398159

    Microsoft SQL Server 2016 Report Builder - http://go.microsoft.com/fwlink/?LinkID=398161

    Microsoft SQL Server 2016 Semantic – http://go.microsoft.com/fwlink/?LinkID=398160

    (SSEI) Microsoft SQL Server 2016 Express http://go.microsoft.com/fwlink/?LinkID=800043

    (SSEI – SQL Server Installer is a new friction-free experience to perform direct installations or download the setup media for SQL Express editions. More details on this coming soon)

     

    Cheers

    Damian

  • What you should read :) during the upcoming !!! weekend - weekly SQL Server blogs review part 14

    Today I have really something extraordinary. We have been waiting so long to have a comprehensive wait statistics and latches description. And now we have them! Paul Randal prepared and posted a great source of information. This is a must read! By the way – Paul have had really a great week posting so much great stuff!

    Hope you will have a lot of fun reading all of them.

      

    Author

    Title

    Link

    Paul Randal

    SQL Server Wait Statistics Library

    http://bit.ly/21QmRwL

    Paul Randal

    SQL Server Latch Classes Library

    http://bit.ly/1OkTAm4

    Paul Randal

    Reconciling set-based operations with row-by-row iterative processing

    http://bit.ly/1VR3vaG

    Paul Randal

    Code to analyze the transaction hierarchy in the log

    http://bit.ly/1s8NPDM

    Paul Randal

    Updated sys.dm_os_waiting_tasks script to add query DOP

    http://bit.ly/1OkTURQ

     

    Cheers

    Damian

     

  • What you should read :) during the upcoming !!! weekend - weekly SQL Server blogs review part 13

    It was long time ago I posted a blog from this series. That was also due to SQLDay conference preparation.  

    I have found a very interesting series posted by Aaron Bertrand (who will be one of the stars during this conference). I strongly advice to take some time and read these articles. And those are only the newest ones!

    Hope you will have a lot of fun reading all of them.

      

    Author

    Title

    Link

    Aaron Bertrand

    STRING_SPLIT() in SQL Server 2016 : Follow-Up #1

    http://bit.ly/1T2eIEX

    Aaron Bertrand

    STRING_SPLIT() in SQL Server 2016 : Follow-Up #2

    http://bit.ly/1NXfbWV

    Aaron Bertrand

    Performance Surprises and Assumptions : DATEADD

    http://bit.ly/1T0NqyN

    Aaron Bertrand

    Performance Surprises and Assumptions : STRING_SPLIT()

    http://bit.ly/23q94fi

    Aaron Bertrand

    Instant File Initialization : Impact During Setup

    http://bit.ly/1rvz8di

     

    Cheers

    Damian

  • Stellar SQL Database Toolkit - review no 2

    Hello SQL Folks.

    Being a MVP gives me the chance to freely use and test some fancy tools and this time I would like to share my thoughts about one of such.  Today I would like to bring your attention to the Stellar SQL Database Toolkit.

    What is Stellar SQL Database Toolkit

    The tool is able to help you in three types of problems:

    ·         Password recovery

    ·         Backup recovery

    ·         Database repair

     

    There is a good explanation in what business scenarios the toolkit is really helpful so I strongly recommend you to visit the page: http://www.stellarinfo.com/sql-database-toolkit.php.

     

    Let’s run the program then. The initial screen looks like on the picture below.  You can pick one of the three mentioned options:

     

     

    I started with the SQL Password Recovery as this particular option was the most interesting to me. The idea behind that is if you accidentally lost a password (could be sa password or any user password) than you would be able to change it. 

     

     

    I have prepared a login (Login4Recovery) that I am going to check. I stopped my SQL Server instance and copied the master data file into other location.

     

     

    As you can see the list of all logins is displayed. I selected the “Login4Recovery” and pressed the Change Password button. I typed a new password and that is all!

     

     

     

     

     

     

    I moved the master data file to its previous location and restarted SQL Server. I tested if the operation was really successful by using the new password to my Login4Recovery login. Everything went fine. But – you should ask yourself a question in what circumstances this operation should ever be done.

    The second option I have tested was Database Repair.  I took a sample database that has a corruption that prevents me from attaching it to the server:

     

     

    I opened the tool and the first information is as follow:

     

     

    That makes sense as it is hard to perform any operations on database files if those are under the control of the database engine. The only way is to take the data file or files away from the SQL Server.

    The next step is to select the data files. There are two possible ways to do so as you can see on the picture below:

    -          You can point in to the database

    -          You can search for it

     

     

     

    When you try to use the “Search in Folder” option that you have to

    ·         specify the location (“Look in” field)

    ·         select the file type

    ·         optionally select the “Search in Subfolders” checkbox

    The search operation is started when you press “Find” button. As you can see on the screen there are 28 primary data files found on my laptop. That does not mean all of them are corrupted. The tool finds all data files and it is up to you to check if they are corrupted or not. This step is just for selecting a file to be examined.

     

     

     

    What is really worth to notice is the last checkbox “Include Deleted Records”.  Checking this options allows the tool to search for deleted records and possibly to recover them. I have selected the option before going to the next step.

     

     

     

    I started with the first option (“Select Database”) and clicked the “Repair” button.

    Now the application started to work out and after a while I was informed that it is possible to repair my database. The applicaton presented the short report of all actions it had taken. 

     

     

    It is possible to take a look at the database, examine all objects and even to see the data. You can even see that for the table HumanResources.Employee two entities are displayed:

    ·         HumanResources.Employee

    ·         HumanResources.Employee (delete)

    That means that for this particular table it is possible to recover the deleted records.

    At this point it is possible to repair and save the database (means also to attach) to the given instance of SQL Server. Remember that you can pick (by selecting the checkboxes) only these objects you really need to be saved.

    To do it you have to press “Save” button from the ribbon:

     

     

    You can then decide how the repaired database be saved. There are 4 options to choose:

    ·         MSSQL file

    ·         CSV

    ·         HTML

    ·         XLS

     

     

     

    I have selected “MSSQL”. Now I had to choose how the database is saved:

    ·         New database

    ·         Live database

     

    New database means that the restored file will be created on the given server. In that case you need to specify the server and instance name (or better – to select them from the list) . Then you need to decide where the data file is located – that could be the default location for the instance or new one.

     

     

    After you press “Connect” the database is going to be created on the server and data are copied:

     

     

    And the magic happened J The database is up and running!

     

     

     

    Are you interested how the Live database option works? You have to specify the server name and an existing database. The recovered data will be saved to the database

     

     

     

    I was also wondering how other save options work. For example the option to save data to the CSV file:

     

     

    I configured the file location to be stored on my local drive and after clicking “OK” the fileswere created. You will notice that under the AdventureWorks_Data folder a new folder is created. It will be created each time you perform the save operation

     

     

    There are a lot of files in this directory – one file for each object like a table or a view:

     

     

    I opened one of the file just to check if it is properly formatted:

     

     

    Now I can see that the tool can be also used to easily export data in some particular cases!

    Let’s go back to the application once again. The very last thing to mention are the other options that are available in this Database Repair tool:

     

     

    There are a lot of options on the top menu, but please check the one described as KB – clicking on this option will open a new browser window and you will be moved to Stellar Phoenix Knowledge Base.  You can also register your product, purchase new ones or for example update it:

     

     

    Take a look also on the “Find Tree Item” search. That will search through all objects so you can very quickly navigate to them. The “Match case” and “Match whole word” check boxes are very intuitive and most of us are familiar with these options form other tools so I so not spend time describing them.

     

    The third functionality offered by the Stellar SQL Database Toolkit is the SQL Backup Recovery. As you can see the options are very similar to the previously described SQL Database Recovery.

    There are two options of searching for a database backup:

    a)       Selecting a file

    b)      Searching in a directory

    I have already described both options. I have selected a damaged database backup file and tried to fix it

     

     

    Then you have to pick a backup from all that exist in the file. In my case it was just one backup in the backup file but it is nothing strange to see more then one. I will not deliberate about backups in this post but I assume you know which one has to be restored first.

     

     

    After I had pressed the “Next” button I saw an information that the backup can be fixed by the tool:

     

     

    There is a chance to review the  objects and data that exist in the backup file. You can take a look on the tables, views and other objects and see the data or the object’s definition. You can pick only the needed objects to be restored by checking the checkboxes on the left side of the objects:

     

     

     

     

    At this point you have to decide what to do with the recovered file. There are options to save it to a database (for example as a new one or to overwrite the existing one) or to other types of files.

     

     

    I started with restoring the backup as a new database on one of my instances. The idea is exactly the same as previously when I was describing the data file fixing. Simply fill the necessary fields:

    a)       Server \ instance name

    b)      File location

     

     

     

    Now press the “Connect” button and the restoring process is taking place. If everything is fine you should see a confirmation message at the end of the process and the database is restored.

     

     

    As you see the database is now present in my instance.

     

     

    I have also tried to save the objects to the CSV file. This is one of the options you can select before the file is saved.

     

     

    The file was created exactly as I expected

     

     

    Conclusion

    I like the tool. It is  very intuitive and does not ask to many questions. It definitely be a helpful in many cases for bot experienced and novice DBA’s. What I would really like to have in the tool in the future would be the possibility to have more description in the log file of the operation that are in progress. I know that this could be a very technical description but I think people would like it. I would!

     

  • What's New in SQL Server 2016 Release Candidate (RC0)

    Hello SQL folks! Are you still excited that SQL Server will be available on Linux soon?

    Now, as you probably know the SQL Server 2016 RC0 is now public so everybody can play with it now!

    You can download the version here

    You can also go and log into the Microsoft Azure and play with the SQL Server there - the VM is ready waiting for you!

    Please read what's new in the product since the first CTP here

    If you are interested what has changed since we have had version CTP 3.3 - here you are the release notes

    Stay in touch and if by any chance you are in Prague this week - I will be there! There is a MVP Open Days conference and I will be talking about the execution plans.  Drop me a note here or on twitter (@damianwidera) and we could talk about the SQL Server (but not only ;)) drinking the best Czech beers! 

    Cheers

    Damian 

  • My presentation and codes from SQLUG.SE users group meeting

    Hello SQL Folks

    I have spend the whole day in Stockholm doing presentation about SQL Server 2016 features:  QueryStore and StertchDb.

    Thanks to Johan Ahlen and Steinar Andersen I was able to be there. Thank you guys once again. It was also a chance for me to meet one of my SQLServer heroes - Erland Sommarskog. I made it! 

    It is always a great fun and big opportunity to meet people from other countries and discuss! Especially the discussion during the coffee break were very, very constructive!

     

    If you are interested in my presentation and code you can find it here

     

    Cheers

    Damian 

     

     

  • Technical Overview: SQL Server 2016 Community Technology Preview 3.3

    Hi Sql Folks!

    Accoring to the TechNet site (check here for more deatils) we can play with SQL Server CTP 3.3 now! 

     

    Here is what has changed or was updatec / fixed 

    New Stretch Database improvements in CTP 3.3 include:

    • Support to enable TDE on a remote DB if the local database has TDE enabled
    • Azure Stretch database edition preview with support for up to 60TB
    • Alter and drop index support for stretch tables
    • Add, alter and drop columns support for stretch tables
    • Point-in-time restore and geo-failover support
    • Query performance improvement

    SQL Server Management Studio improvements in this release include:

    • Additional Wizard features:
      • Added new SQL db credential management functionality
      • Integrated Table validation and selection updates to prevent stretch of unsupported datatypes at selection time
      • Table search functionality for table select page
      • Table selection column reordering
      • Support for temporal tables during table select
      • Integrated Azure sign in and SQL sign in credential
      • Add support for stretching using federated accounts
      • New firewall configuration and subnet detection functionality
      • Updated summary page details with pricing information
      • Improved SSMS visualization with StretchDB icons
    • Object Explorer:
      • Fly out menu updates to support disable and un-migration functionality
      • Un-migrate support functionality at database and table level

    Read the SSMS blog post to learn more.

    CTP3.3 adds support with In-Memory OLTP for:

    • Automatic update of statistics on memory-optimized tables: The statistics for memory-optimized tables are now updated automatically, removing the need for running maintenance tasks that update statistics manually.
    • Sampled statistics for memory-optimized tables: Sampling of statistics for the data in memory-optimized tables is now supported, alongside the previously supported fullscan statistics. This reduces the time it takes to collect statistics for large tables.
    • Use of LOB types varchar(max), nvarchar(max), and varbinary(max) with built-in string functions (‘+’, len, ltrim, rtrim and substring) in natively compiled modules, and as return type of natively compiled scalar UDFs.
    • Memory-optimized tables with row size > 8060 bytes, using non-LOB types. CTPs 3.1 and 3.2 supported larger rows using LOB types; as of CTP3.3, memory-optimized tables support also larger rows using types varchar(n), nvarchar(n) and varbinary(n). See below for an example.
    • The OUTPUT clause can now be used with INSERT, UPDATE and DELETE statements in natively compiled stored procedures.

    Autostats improvements in CTP 3.3

    Previously, statistics were automatically recalculated when the change exceeded a fixed threshold. As of CTP 3.3, we have refined the algorithm such that it is no longer a fixed threshold, but in general will be more aggressive in triggering statistics scans, resulting in more accurate query plans.

    Foreign Key Support enhancements in CTP 3.3

    SQL Server 2014 and earlier versions have limitations on the number of FOREIGN KEY references a table can contain, as well as the maximum number on incoming foreign key REFERENCES. The documented recommended maximum is 253, and when performing DML on tables with large numbers of incoming REFERENCES, statements time out with stack overflow error messages.

    This improvement increases the number of supported incoming foreign key REFERENCES to a table, while maintaining good performance for DML operations in both the referencing and the referenced table. The new maximum is 10,000. However, with the CTP 3.3 release, we have certain limitations on this feature:

    • We ONLY support Delete DML operation on foreign key references that go beyond the current recommended maximum of 253. Therefore, we will validate that no referencing rows exist before deletion.
    • Update and Merge operations are not supported with this release. Update will be available in RTM.
    • You will not see any change in behavior for cascading actions.
    • This is not available in ColumnStore, Hekaton or StretchDB.
    • This change is not applicable to a primary key table that is self-referencing (that is, if the table has a foreign key to itself). In this case, the behavior would remain the same as before.
    • This is not supported for partitioned foreign key tables for CTP 3.3. However, partitioned tables will be supported in RTM.

    SQL Server Analysis Services (SSAS) includes multiple additions in this release. Read the SSAS CTP 3.3 blog post to learn more.

    SQL Server Reporting Services (SSRS) includes an updated preview of its brand-new web portal with additional functionality:

    • Add the KPIs and reports that matter most to you to your Favorites and view them all in one place.
    • Manage shared data sources for your KPIs and reports and perform other management tasks.

    Read the SSRS blog post to learn more.

    Master Data Services (MDS) improvements in this release include:

    • Business rule changes
      • New, easier-to-use web UI administration page
      • Support for NOT conditional operator
      • Support for ELSE section that contains a set of actions to execute if the IF condition is false
      • Removed management UI from Excel add-in
    • Added support for purging (hard-deleting soft-deleted members) of an entity version
    • Added to the web explorer page a button to open the current entity view in the Excel add-in

     

    Happy playing!

    Damian 

  • What you should have read :) during the weekend - weekly SQL Server blogs review part 12

    Hello! Happy New Year to you all! I was really busy at the end of the last

    year because I was trying to close all opened projects. Which was not

    possible and now I know this!

    I am coming back to you with a list of posts that are a must to read !!!

    Hope you will have a lot of fun reading all of them.

      

    Author

    Title

    Link

    Jack Li

    What to do when you run out of disk space for In-Memory OLTP checkpoint files | CSS SQL Server Engineers

    http://bit.ly/1OBAms9

    Klaus Aschenbrenner

    How to pollute your Plan Cache with parameterized SQL statements

    http://bit.ly/1ntWgqK

    Adam Machanic

    SQLQueryStress: The Source Code

    http://bit.ly/1RPJTCi

    Mark Broadbent

    Problem removing files from TempDB

    http://bit.ly/1OsM1M9

    Joe Celko

    Declarative SQL: Using References

    http://bit.ly/1JVwTYK

    Michael Sorens

    Documenting Your PowerShell Binary Cmdlets

    http://bit.ly/1n6UL16

     

    Cheers

    Damian

More Posts Next page »
Privacy Statement