Skip to content

Announcing SQL Server Management Studio - 16.4.1 Release

Geschrieben von Database Magician am

'Today, we are very pleased to announce the latest generally-available (GA) quality release of SQL Server Management Studio (SSMS) 16.4.1. This update features new PowerShell cmdlets to help perform SQL Server login management and read/write data, and many improvements/bug fixes addressing 19 Connect Items!

Get it here:                                                          

Download SSMS 16.4.1 release

  • The version number for the latest release is 13.0.15900.1

New in this release

  1. Fixed an issue where attempting to ALTER/Modify a Stored Procedure fails (Microsoft Connect item #3103831) 

  2. New ‘Add-SqlLogin’ cmdlet to enable new login management scenarios using PowerShell.(Microsoft Connect item #2588952)

  3. New ‘Read-SqlTableData’, ‘Read-SqlViewData’, and ‘Write-SqlTableData’ cmdlets to view and write data using PowerShell.(Trello Read-SqlTableData Card)(Microsoft Connect item #2685363)

  4. Improved support and usability for users connecting to various national clouds.

  5. Fixed an issue where an Out Of Memory Exceptions were being thrown.(Microsoft Connect item #3062914)(Microsoft Connect item #3074856)

  6. Fixed an issue where filtering by schema was not a valid filter option.(Microsoft Connect item #3058105)(Microsoft Connect item #3101136)

  7. Fixed an issue where the Monitor window for a stretched database would not be accessible.

  8. Fixed an issue where the F1 Help always opened online content. Users can now select whether they prefer online or offline help via the “Set Help Preference” in the Help menu.(Microsoft Connect item #2826366)

  9. Fixed an issue where scripting out a 1200-level Analysis Services tabular model wouldn’t strip out the password for scripting, even though the server version had (client model object is now sync’d before scripting).

  10. Fixed an issue where ‘SELECT TOP N ROWS’ option generated deprecated syntax for the the TOP operator.(Microsoft Connect item #3065435)

  11. Fixed various layout issues throughout SSMS, including the Login Properties page and Advanced Query Execution Options.(Microsoft Connect item #3058199)(Microsoft Connect item #3079122)(Microsoft Connect item #3071384)

  12. Fixed an issue where a solution was created automatically whenever a user opened a new query window.(Microsoft Connect item #2924667)(Microsoft Connect item #2917742)(Microsoft Connect item #2612635)

  13. Fixed an issue where temporal tables could not be expanded in Object Explorer when in system databases.(Microsoft Connect item #2551649)

  14. Fixed an issue where SSMS runs a query to SELECT @@trancount after executing a batch.(Microsoft Connect item #3042364)

  15. Fixed an issue in Analysis Services where creating a script from a server’s properties page resulted in a hidden connection dialog.

  16. Fixed an issue where Ctrl+Q would not select the Quick Launch toolbar.

  17. Fixed an issue where changing the MaxSize of a database using the Server Properties dialog was broken for databases > 2 TB.(Microsoft Connect item #1231091)

  18. Fixed an issue where the Restore Database wizard wouldn’t accept filenames with leading whitespaces:(Microsoft Connect item #2395147)

  19. Fixed an issue in SSMS where an Analysis Services Server admin after scripting server properties may see a “No permissions” error in the dialog.

  20. Fixed an issue where the Server Properties page could show the incorrect collation for databases in Azure.

  21. Fixed an issue where creating a database in Azure threw a ConnectionFailure exception.

  22. Fixed an issue where SSAS PowerShell scripts wouldn’t work on machine without Analysis Services server instance also installed.

Please visit the SSMS download page for additional details, and to see the full changelog.'...

https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-management-studio-16-4-release/

Always Encrypted (Database Engine)

Geschrieben von Database Magician am

Always Encrypted

Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access). By ensuring on-premises database administrators, cloud database operators, or other high-privileged, but unauthorized users, cannot access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

Always Encrypted is available in SQL Server 2016 and SQL Database. For a Channel 9 presentation that includes Always Encrypted, see Keeping Sensitive Data Secure with Always Encrypted.'...

https://msdn.microsoft.com/en-us/library/mt163865.aspx?MC=SQL&MC=SecSys

What is Deadlock Scheduler? How to Reproduce it?

Geschrieben von Database Magician am

'If you have come on this page by internet search, then you must have seen below error in the ERRORLOG which is about deadlock scheduler.

2016-07-25 08:50:29.50 Server * ******************************************
2016-07-25 08:50:29.50 Server *
2016-07-25 08:50:29.50 Server * BEGIN STACK DUMP:
2016-07-25 08:50:29.50 Server * 07/25/16 08:50:29 spid 1448
2016-07-25 08:50:29.50 Server *
2016-07-25 08:50:29.50 Server * Deadlocked Schedulers
2016-07-25 08:50:29.50 Server *
2016-07-25 08:50:29.50 Server *
*******************************************************************************'...

http://blog.sqlauthority.com/2016/07/29/sql-server-deadlock-scheduler-reproduce/

Limitations of the Missing Indexes Feature

Geschrieben von Database Magician am

'The missing index feature has the following limitations:

  • It is not intended to fine tune an indexing configuration.

  • It cannot gather statistics for more than 500 missing index groups.

  • It does not specify an order for columns to be used in an index.

  • For queries involving only inequality predicates, it returns less accurate cost information.

  • It reports only include columns for some queries, so index key columns must be manually selected.

  • It returns only raw information about columns on which indexes might be missing.

  • It does not suggest filtered indexes.

  • It can return different costs for the same missing index group that appears multiple times in XML Showplans.

  • It does not consider trivial query plans.

The following sections provide more information about these limitations.'...

https://technet.microsoft.com/en-us/library/ms345485(v=sql.105).aspx

Top 10 SQL Server Integration Services Best Practices

Geschrieben von Database Magician am

'How many of you have heard the myth that Microsoft® SQL Server® Integration Services (SSIS) does not scale? The first question we would ask in return is: “Does your system need to scale beyond 4.5 million sales transaction rows per second?” SQL Server Integration Services is a high performance Extract-Transform-Load (ETL) platform that scales to the most extreme environments. And as documented in SSIS ETL world record performance, SQL Server Integration Services can process at the scale of 4.5 million sales transaction rows per second.'...

https://blogs.msdn.microsoft.com/sqlcat/2013/09/16/top-10-sql-server-integration-services-best-practices/

Cumulative Update #1 for SQL Server 2014 SP2

Geschrieben von Database Magician am

'Dear Customers,

The 1st cumulative update release for SQL Server 2014 SP2 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

To learn more about the release or servicing model, please visit:

https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-1-for-sql-server-2014-sp2/?WT.mc_id=AID522292_EML_4522181

HEAPS in Verbindung mit DELETE-Operationen

Geschrieben von Database Magician am

'In einem Projekt wurde den Entwicklern gesagt, dass man grundsätzlich mit Heaps arbeiten solle, da durch die Verwendung eines Clustered Index viele Deadlocks verursacht worden sein. Daraufhin hat man für fast alle Tabellen in der Datenbank die geclusterten Tabellen wieder zu Heaps konvertiert. Die Deadlocks sind laut Aussage vollkommen verschwunden – jedoch hat man ein paar Dinge nicht beachtet, die sich nun nachteilig auf die Performance auswirken; und es sind nicht fehlende Indexe gemeint!'...

http://www.db-berater.de/2016/08/heaps-in-verbindung-mit-delete-operationen/?wt.mc_id=AID520629_EML_4516654

Upcoming SQL Server 2018 Features

Geschrieben von Database Magician am

'I happened to be in Seattle this week after finishing up an Alaska cruise, and I had the chance to sit down with some of the PMs responsible for upcoming SQL Server features. It turns out they’re already deep in development with some truly revolutionary capabilities.

Now, I probably shouldn’t be sharing these with you, dear reader, but I bet you’re going to be as excited as I am, so here goes: SQL Server 2018’s upcoming feature list'...

https://www.brentozar.com/archive/2016/08/upcoming-sql-server-2018-features/