[EN] SQL Server 2012 SP2 Cumulative Update #7 is available!

Just one update package in July. In this Cumulative Update No 7 {build 11.0.5623.0} for Microsoft SQL Server 2012 Service Pack 2 (SP2) released two weeks ago Microsoft fixed thirty four bugs. Important information for all DBA (and their SEC friends) is that SQL Server 2012 SP2 Cumulative Update 7 contains the recently released Security Bulletin MS15-058 (KB3065718).

VSTSBUG: 3983191 | 3029096
FIX: The changes in Oracle databases are not replicated to SQL Server 2012 or SQL Server 2014 databases correctly
Integration Services

VSTSBUG: 4346613 | 3030352
FIX: The change table is ordered incorrectly for updated rows after you enable change data capture for a Microsoft SQL Server 2012 database
SQL service

VSTSBUG: 4178633 | 3052461
FIX: Some attributes in element are ignored when you transform XML file in SSIS 2012
Integration Services

VSTSBUG: 4639762 | 3062001
FIX: “Failed to create kernel event for collection set” error occurs when you use Data Collection in SQL Server 2012
Management Tools

VSTSBUG: 4602046 | 3061215
FIX: “Cannot read 8 bytes from file” error occurs when you use SSIS 2012
Integration Services

VSTSBUG: 4713547 | 3064454
FIX: Hash or merge join hints may be ignored when you execute a query in SQL Server 2012
SQL performance

VSTSBUG: 4715206 | 3065060
FIX: “Unable to create restore plan due to break in the LSN chain” error when you restore differential backup in SSMS
Management Tools

VSTSBUG: 4743330 | 3068351
FIX: ROLAP Distinct Count on data source is not started when it is running from SSAS 2012 to back-end SQL Server 2014
Analysis Services

VSTSBUG: 4662556 | 3068359
FIX: “Cannot find the OLAP cube Model” error when you update the pivot table in a SharePoint document in SQL Server 2012
Analysis Services

VSTSBUG: 4520905 | 3068439
FIX: Indexed view returns incorrect result after insert or delete operation on the base table in SQL Server 2012
SQL performance

VSTSBUG: 4694348 | 3068468
FIX: Schema lock occurs on a table with large data when you delete lots of rows from it in SQL Server 2012
SQL service

VSTSBUG: 4804569 | 3068703
FIX: Error messages are logged when you execute a non-cacheable auto-parameterized query in SQL Server 2012
SQL service

VSTSBUG: 4805976 | 3068776
FIX: Incorrect results occur in a rare scenario when you run a query that contains a nested loop join and performance spool in its inner side in SQL Server 2012
SQL performance

VSTSBUG: 4524535 | 3069154
FIX: You cannot set a SQL Server 2012 database to single-user mode if the database is read-only
SQL service

VSTSBUG: 4436770 | 3069155
FIX: A deadlock occurs when you execute the sp_estimate_data_compression_savings stored procedure in SQL Server 2012
SQL service

VSTSBUG: 4925413 | 3073530
FIX: Access Violation error occurs when you run DDL SQL statements on a database in an instance of SQL Server 2012
Analysis Services

VSTSBUG: 4500307 | 3074434
FIX: Out of memory error when the virtual address space of the SQL Server process is very low on available memory
SQL service

VSTSBUG: 4928677 | 3074535
FIX: The Resource database is missing after you install updates or service packs for instances of SQL Server 2012 one after another and then restart the server
SQL service

VSTSBUG: 4683321 | 3074922
FIX: Incorrect result when you execute a full-text query with parallel execution plan in SQL Server 2012
SQL performance

VSTSBUG: 4547054 | 3074425
FIX: Contention occurs when you execute many ad-hoc queries in SQL Server 2012
SQL service

VSTSBUG: 5013383 | 3075176
FIX: You receive incorrect results from the CHANGETABLE() function when snapshot isolation for a SQL Server 2012 database
SQL service

VSTSBUG: 4495133 | 3076444
FIX: Cannot enable “change data capture” on a table that uses a unique index with more than one included column in SQL Server 2012 SP2
SQL service

VSTSBUG: 4781857 | 3075669
FIX: SSAS 2012 crashes when you execute an MDX query that contains a large number of Union expressions
Analysis Services

VSTSBUG: 4376957 | 3051243
FIX: Error 2601 occurs when you add new articles with join filters to existing merge publications
SQL service

VSTSBUG: 4705249 | 3064292
FIX: SHARING VIOLATION error 6518 occurs on CLR assembly microsoft.sqlserver.types.dll in SQL Server 2012
SQL service

VSTSBUG: 4933896 | 3073907
FIX: A SQL Server 2012 SSIS package with an Execute SQL Task may stop responding
Analysis Services

VSTSBUG: 4691746 | 3063438
FIX: XSLT transformation fails when you run an SSIS 2014 package that contains multiple concurrent XML tasks
Integration Services

VSTSBUG: 4716342 | 2965654
FIX: SSRS 2012 or SSRS 2014 report is cut horizontally after you apply Update MS13-088 on a computer
Reporting Services

VSTSBUG: 4939175 | 3070147
FIX: Incorrect values are included in the description column when you query the sys.dm_xe_objects dynamic management object in SQL Server
SQL service

VSTSBUG: 4759209 | 3066299
FIX: Non-English items display in the MDS Explorer when you set language settings for web browser to English
Master Data Services (MDS)

VSTSBUG: 4817323 | 3069320
FIX: A workflow never starts in a SharePoint 2013 environment when you use SQL Server 2012 Master Data Services to start the workflow
MDS

VSTSBUG: 4465179 | 972759
FIX: Installing SQL Server 2008 Service Pack 1 may reset the “max_worker_threads” column value for a SQL Server Agent subsystem
Management Tools

VSTSBUG: 4834221 | 2820068
FIX: Can’t install SQL Server by using InstallShield or bootstrapping application
Setup & Install

 

This update could be applied Microsoft SQL Server 2014, when used applies to

  • Microsoft SQL Server 2012 Service Pack 2 ONLY

 

Microsoft has recommended to test all hotfixes before updating in production environment. More information about this release You can find with the article: Cumulative Update 7

You may have to restart the computer after you apply this cumulative update. If you use any hybrid technology, such a Replication, AlwaysOn, Clustering:  If you do not want to use the rolling update process, follow these steps in order to apply a CU or SP: step1: Install the service pack on the passive node / step2: Install the service pack on the active node (This step requests servicerestart).

If You want to go directly to download section jump to Request Hotfix Download Site where You can choose version (for both platforms: 64-bit and 32-bit) . This release is prepared as multilingual package. And here is the full list af all packages:

    SQLServer2012_SP2_CU7_3072100_11_0_5623_x64
    2012_SP2_AOrcleDes_CU7_3072100_11_0_5623_x64
    2012_SP2_AOrcleSer_CU7_3072100_11_0_5623_x64
    2012_SP2_AOrcleDes_CU7_3072100_11_0_5623_x86
    SQLServer2012_SP2_CU7_3072100_11_0_5623_x86
    2012_SP2_AOrcleSer_CU7_3072100_11_0_5623_x86

     

Enjoy!

[EN] Sunday Market–SSMS Spotlight Plugin by Dell (part one)

It Sunday again. And some shops are closed. If Yes, it’s time to get next useful tool for free. It’s my episode #07 in non-frequently series: Sunday Market. This time we will look for Dell. Inspired by online session of my good friend from England: Richard Douglas {TWITTER|BLOG|LINKEDIN} I want look for Spotlight Essentials Plugin by Dell. Richard delivered session: Wait Watchers – No pain, all gain exercises to increase database performance at PASS Database Administration Virtual Chapter and it was very interesting. Interesting enough to look for this plugin. It is free, so we can get in on Sunday, just like today. This post will be divided in two parts: one for basic installation – till the moment “"’I got it!’ and second with more configuration options. So let’s install this plugin.

Phase One. Getting the plugin.

You should go to Spotlight Plugin site: https://www.spotlightessentials.com/sql-server/instances 

Captura_Spotlight01

Pict. 01 | Intoructionary picture of plugin

Captura_Spotlight02

Pict. 02 | Registration form for account using Spotlight in future

Captura_Spotlight03

Pict. 03 | Downloading plugin (ca 70 MB)

Captura_Spotlight04

Pict. 04 | Registration for Spotlight Essential Web Account (if you registered before new geristration is not necessary)

 

Phase Two. Installation of plugin.

Captura_Spotlight05

Pict. 05 | Welcome screen (same type like Visual Studio 2013 isn’t?)

Captura_Spotlight06

Pict. 06 | Transaction Product Agreement – in normal words license.

Captura_Spotlight07

Pict. 07 | Installation of Diagnostic Server with specify Diagnostic Server Service Account

Captura_Spotlight08

Pict. 08 | Using special dedicated account SQL Spotlight will bring errors with permissions, but it work and is best way in my opinion.

Captura_Spotlight09

Pict. 09 | Configuration os specific Payback Database which will store recent and current data (we can choose special instance or the same instance as production server)

Captura_Spotlight10

Pict. 10 | If desired instance are not connected or not running, we will get error.

Captura_Spotlight11

Pict. 11 | In the case of clicking NEXT at previous screen we will get error that Specific Database not exist

Captura_Spotlight12

Pict. 12 | When we click CREATE on screen (at picture nine) we can creat Specific Database

Captura_Spotlight13

Pict. 13 | Specific Database created succesfully

Captura_Spotlight14

Pict. 14 | Installaton started…

Captura_Spotlight15

Pict. 15 | …and finished. Aproximately time is less than five minutes.

 

Phase Three. First view in SQL Server Management Studio.

Captura_Spotlight16

Pict. 16 | Specified Database appears in databases tree: SpotlightPluginPlayBackDatabase

Captura_Spotlight17

Pict. 17 | We can docking Spotlight Plugin in a tab. But we can’t connect to Diagnostic Server if our Spotlight plug-in Diagnostic Server Service didn’t run.

Captura_Spotlight18

Pict. 18 | We can check Services and even we see that service is running, we can STOP and START again it.

Captura_Spotlight19

Pict. 19 | First view for our plugin in General Tab

Captura_Spotlight20

Pict. 20 | Next step is connecting instance to Spotlight Essential Plug-In Web Account (I will write more about in in next post)

Captura_Spotlight21

Pict. 21 | Time for connect first instance. In my case is the instance with Specified Database

Captura_Spotlight22

Pict. 22 | And here we see status of connections: to Diagnostic Server and to Instance

Captura_Spotlight23

Pict. 23 | First detailed tab: Overview Tab with Waits, Top Databases Files IO, Batch Request, Summary, Connections, CPU Usage and more.

Captura_Spotlight24

Pict. 24 | Second detailed tab: Sessions with sessions status, activity last command and over thirty other details about sessions

Captura_Spotlight25

Pict. 25 | Third detailed tab: Resources Waits with Wait Time Trend ans Waits Details

Captura_Spotlight26

Pict. 26 | Fourth detailed tab: Data File I/OS with general data file infor for each data file and system databases

Captura_Spotlight27

Pict. 27 | Fifth  detailed tab: TOP SQL  with detailed information about last TOP 25 SQL Queries

Captura_Spotlight28

Pict. 28 | After few minutes of work we can see more events on our General Tab where alarms are sorted by Severity

Captura_Spotlight29

Pict. 29 | And more and more alerst after next few minutes.

Screenshot 2015-01-04 18.17.01

Pict. 30 | We can add more instances for Diagnostic Server

Captura_Spotlight30

Pict. 31 | But we can’t connect with SQL Azure Instance if we just click without changing authentication type

image

Pict. 32 | When we use SQL Authentication, connection is succesful

image

Pict. 33 | And we can see all instances in General Tab

Captura_Spotlight31

Pict. 34 | In General Tab we see status of our instances in colors which based on health status

Screenshot 2015-01-04 18.47.05

Pict. 35 | When we move mouse in General Tab over one of many instances we will see pop-up windows with short information of errors

Screenshot 2015-01-04 18.48.51

Pict. 36 | And those errors will be different, they change in time

Screenshot 2015-01-04 19.01.23

Pict. 37 | Same pop-up windows we will see in Overview tab on each instance

 

That’s All Folks. Get the plugin, istall the plugin and configure the plugin. From this point You can test this plugin and start to play with this nice tool. More details about configuration, errors, connections and how useful this plugin could be in next post.

[EN] Improvements of SSMS for Azure Database after CU5 – part one

LOGO__SQL-Azure_rgbApplying latest Cumilative Update {build no 12.0.2456.0} for SQL Server 2014 improving your SQL Server Management Studio functionality. In few next posts I will write about some functionality which are interesting for me, and could be interesting for you.

First of few options which are very interesting for me (in the case of some demo on my sessions at conferences) are tasks SELECT and EDIT. Two fix has been delivered for two not bugs, but missing functionality:

VSTS BUG: 3548910 | KB: 3014156 | LINK: http://bit.ly/13MTeU3 
FIX: An update is available that enables "Edit Top 200 Rows" option for tables and views of SQL Azure database
VSTS BUG: 3548922 | KB: 3014166 | LINK: http://bit.ly/1Db8tCG 
FIX: An update is available that enables "Select Top 1000 Rows" option for tables and views of SQL Azure database

Let’s look how it works. Before installing update I got two databases of SQL Server: one is Adventure Works 2012 on Premise, and one is Adventure Works 2012 in Windows Azure. Using SQL Server Management Studio I trying to check my instances (in all cases I will use same table Person.Address):

Screenshot 2014-12-28 10.50.46

Pict. 01 – With AdventureWorks located in on Premise instance tasks: “Select Top 1000 Rows” and “Edit Top 200 Rows” are available by default.

Screenshot 2014-12-28 10.51.24

Pict. 02 – With AdventureWorks located on Windows Azure Server (as cloud database) bots options are not available.

Problem with missing functionality has been solved after installing Cumulative Update 5.

Screenshot 2014-12-28 19.13.49

Pict. 03 – With With AdventureWorks located on Windows Azure Server both tasks: “Select Top 1000 Rows” and “Edit Top 200 Rows” are available.

 

Second of few really great functionality is focused on deployment database for Windows Azure.

VSTS BUG: 3548883 | KB: 3014164 | http://bit.ly/1xeIWbo
FIX: Cannot use new service tiers Basic, Standard and Premium when you deploy to Windows Azure SQL Database in SSMS 2014

Again let’s look how it works. For this case I use three instances: one with SQL Server 2012 (engine version 11.xx) one with SQL Server 2014 (engine version 12.xx) and one Windows Azure instance. And I use two newly created databases:

  • TestDeploy11 located on SQL Server 2012 instance, compability mode with SQL Server 2012 (110), collation: Chinese_Traditional_Pinyin_100_CI_AS
  • TestDeploy12 located on SQL Server 2014 instance, compability mode with SQL Server 2014 (120), collation: Latin1_General_100_CI_AS

Part one (before updating) >> deploying new database from SQL Server Management Studio 2012 to Windows Azure.

Screenshot 2014-12-31 17.21.35

Pict. 04 – steps: Right Click>Task>Deploy Database to SQL Azure {I starting from SHINGEN instance which is SQL Server 2014 instance}

Screenshot 2014-12-31 17.21.52

Pict. 05 – Welcome screen (we can switch off this screen)

Screenshot 2014-12-31 17.22.07

Pict. 06 – After connecting to Windows Azure SQL Server (where I keep my azure databases) we can choose Web version with size 1 and 5 GB

Screenshot 2014-12-31 17.22.18

Pict. 07 – Or Business Edition with size: 10, 20, 30, 40, 50, 100, 150 GB

Screenshot 2014-12-31 17.22.33

Pict. 08 – On next screen we see short summary of planned deploy

Screenshot 2014-12-31 17.22.49

Pict. 09 – And here we have expected error about Operation Failed. Expected because I tried deploy databese with compability factor 120, even I use SSMS for SQL Server 2012. So, we cannot deploy SQL Server 2014 database from Management Studio 2012 to Windows Azure.

Screenshot 2014-12-31 17.23.03

Pict. 10 – Still on the same Management Studio now I tried to deploy SQL Server 2012 database to Windows Azure {I now working with MIFUNE instance which is SQL Server 2012 instance}

Screenshot 2014-12-31 17.23.21

Pict. 11 – I can choose from the same options for Web Edition and 1 & 5 GB size

Screenshot 2014-12-31 17.23.32

Pict. 12 – And of course Business Edition and 10, 20, 30, 40, 50, 100, 150 GB size

Screenshot 2014-12-31 17.23.43

Pict. 13 – You can see small difference with picture number eight, different name of database

Screenshot 2014-12-31 17.23.56

Pict. 14 – And after clicking on NEXT button deploying started

Screenshot 2014-12-31 17.24.50

Pict. 15 – And finally I have status Operation Completed and my new database is now deployed to Windows Azure.

 

Part Two (after updating) >> deploying new database from SQL Server Management Studio 2014 to Windows Azure.

Screenshot 2014-12-31 17.41.55

Pict. 16 – steps: Right Click>Task>Deploy Database to SQL Azure {I starting from MIFUNE instance which is SQL Server 2012 instance}

Screenshot 2014-12-31 17.42.28

Pict. 17 – I can choose from the same options for Web Edition and 1 & 5 GB size, but here editions are upgraded and Web Edition has status Retired

Screenshot 2014-12-31 17.42.39

Pict. 18 – And of course Business Edition and 10, 20, 30, 40, 50, 100, 150 GB size, and same as above Business Edition has status Retired

Screenshot 2014-12-31 17.43.11

Pict. 19 – And after clicking on NEXT button deploying started (with small change to avoid error: Target Database: TestDeploy11v2)

Screenshot 2014-12-31 17.45.04

Pict. 20 – And finally I have status Operation Completed  and my new database is now deployed to Windows Azure. Now I have two newly deployed databases on my Windows Azure server

Screenshot 2014-12-31 17.48.04

Pict. 21 – Same steps like on pisture number sixteen, and if you have ‘Eagle Eye’ you see that here and on picture sixteen we deploying for Windows Azure SQL Database instead of SQL Azure Database (which happened on SQL Server Management Studio 2012) {I now working with SHINGEN instance which is SQL Server 2014 instance}

Screenshot 2014-12-31 17.48.15

Pict. 22 – Almost the same welcome screen (we can switch off this screen)

Screenshot 2014-12-31 17.48.36

Pict. 23 – Now pure changes starting. First option: Basic Edition with size 1 & 2 GB, with Default Service Objective (Service Tier & Performance Level)

Screenshot 2014-12-31 17.48.55

Pict. 24 – Second option: Standard Edition with 1, 2, 5, 10, 20, 30, 40, 50, 100, 150, 200, 250 GB size

Screenshot 2014-12-31 17.49.01

Pict. 25 – Still second option but with three Service Objective levels: S0, S1, S2

Screenshot 2014-12-31 17.49.10

Pict. 26 – Third option: Premium Edition with 1, 2, 5, 10, 20, 30, 40, 50, 100, 150, 200, 250, 300, 400, 500 GB size

Screenshot 2014-12-31 17.49.15

Pict. 27 – and of course still third option but with three Service Objective levels: P1, P2, P3

Screenshot 2014-12-31 17.49.32

Pict. 28 – Summary with specification same like before

Screenshot 2014-12-31 17.51.34

Pict. 29 – And final result status Operation Completed and my new database is now deployed to Windows Azure. Now I have three newly deployed databases on my Windows Azure server.

 

Part Three >> finally three new deployed databases, I tried to deploy four databases but one was failed because of non-compability expected error.

Screenshot 2014-12-31 17.51.52

Pict. 30 – All three deployed databases are now on my Windows Azure server

image image image

Pict. 31 – Interesting point is at the end, because all my freshly deployed databases have compability level 100 (instead of 110 and 120 as  original “on premise” databases). But this is not subject of this post.