SQL Server 2016 Release Date
SQL Server 2016 GDR has been set to release on June 1, 2016 (Ref).
SQL 2016 is going to be a major blast in how SQL Server is used for OLTP, OLAP and how it integrates with Microsoft Azure. If you are not already playing with it, take time to install it and explore the gems hidden in multitude of new functionality.
SQL Server 2014 & 2016 Developer Edition
Microsoft SQL Server has been in developer edition as far back as 2005 (if my memory serves me); however each license was $50.00. Now with SQL Server 2014 & 2016, Microsoft has made developer edition free.
There is no better time to get on board with SQL Server 2016, there are lots of new functionality and features available. That can help your organization reach new levels of performance, scalability, and data insight.
Download and start playing with SQL 2014 here.
SQL Server 2016 RC3 Released
SQL Server 2016 RC3 has been released, it is the final release before RTM. In this release no major functionality is introduced.
Download from here.
SQLPASS Edmonton (Apr. 9, 2016) – Key Note Presentation
Friday/Saturday was a great day, got to meet lots of wonderful people from US/Canada at the Alberta’s first SQL Saturday. I delivered they Key Note speech for SQL Server 2016. I would love to hear back from people, so please provide evaluation on SQL Saturday site and you can download the PowerPoint deck here.
How to get orphaned logins for all databases?
Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name. This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
DROP TABLE #Output
GO
CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
GO
sp_msforeachdb '
INSERT INTO #Output
SELECT ''?''
, DP.sid
, SP.name
, DP.name
, DP.type_desc
FROM sys.database_principals DP
LEFT JOIN sys.server_principals SP
ON DP.sid = SP.sid
WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
AND DP.sid IS NOT NULL'
GO
SELECT *
FROM #Output
WHERE ServerLoginName IS NULL
OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName
GO
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
How get all users and their role mappings from all databases?
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
DROP TABLE #Output
GO
CREATE TABLE #Output (DatabaseName VARCHAR(255), RoleName VARCHAR(255), UserName VARCHAR(255))
GO
sp_msforeachdb '
INSERT INTO #Output
SELECT ''?''
, DR.name
, DP.name
FROM [?].sys.database_principals AS DR
JOIN [?].sys.database_role_members AS DRM
ON DR.principal_id = DRM.role_principal_id
JOIN [?].sys.database_principals AS DP
ON DP.principal_id = DRM.member_principal_id
WHERE (DP.name <> ''dbo'')'
GO
SELECT *
FROM #Output
GO
Microsoft SQL Server 2016 RC2 Released
In SQL Server 2016 RC 2, enhancements include:
- R Services setup – the setup process for R Services is much more integrated into SQL Server setup. There is no longer a need to manually download and install Microsoft R open and R Server if the SQL Server is connected to the Internet; it becomes part of the SQL Server install sequence.
- SQL Server Management Studio (SSMS) – This release of SSMS features an update to the Visual Studio 2015 shell bringing enhancements such as the quick launch toolbar and improved theming support.
- Mobile reports – Brand Packages will now be downloaded to the mobile report publisher from a server running RC2 and available for use in report creation. Basic mobile report content migration between servers is now supported.
Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group
One of customers changed the value returned from @@SERVERNAME. SQL Server works no problem, however an unexpected behavior appeared. Changing the value for @@SERVERNAME, caused the backups to fail.
Looking at the maintenance jobs, found all jobs completed successfully and without issues. However, upon looking at the database’s statics it states no backups completed.
Because the database in question is part of AlwaysOn Availability Group (AG); SQL Server executes sys.fn_hadr_backup_is_preferred_replica to determine if the backup should take place on the current node. However, it returns value of 0 for all databases, if the preferred replica is set. Because, the script makes a check that is running on the server that is preferred. It does this by comparing the value to @@SERVERNAME to value of replica_server_name in sys.availability_replicas. Because value will never match, it skips the database on both primary and secondary replica.
I have created a Microsoft Connect article (link); asking this little bit of information to be added to Books Online article (link). There was a request submitted by Ola Hallengren (Blog | Twitter), which was closed as Won’t Fix (link). Please vote!
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
sys.foreign_keys does not have matching row in sys.indexes
Running DBCC CHECKDB you are getting following error message:
Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes
This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK constraint (FK object_id 2040565179). This should not happen, SQL Server will not allow you to drop a constraint that is referenced by FK. If attempted should get following error message:
Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘dbo.a.NonClusteredIndex-20151119-085219’. It is being used for FOREIGN KEY constraint enforcement.
So if we are suppose to get errors? Why do we have corruption; simple answer, someone be making updates to system tables directly, which is not allowed or supported!
Actually we are not able to update system tables in SQL Server 2005+ (ref), however in SQL 2000 days, we had setting called allow updates in sp_configure options. Also supported by the KB2787112.
So question is how do you fix it?
First, identify the child table name from sys.foreign_keys:
SELECT object_name(parent_object_id) AS TableName FROM sys.foreign_keys WHERE name = 'FK_b_a'
Second, script our constraint definition:
- Find the table, we got in SQL Statement above.
- Go to Keys.
- Right click on FK constraint name.
- Script Key As.
- Create To.
- New Query Window.
Third, drop the FK constraint:
ALTER TABLE [schema].[tablename] DROP CONSTRAINT [fk_constraint_name]
Fourth, Re-create the constraint, with script generated in Step 2.
If it was issue of someone playing around in system tables, this should resolve it. However, if you get error similar to below:
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.a’ that match the referencing column list in the foreign key ‘FK_b_a’.
This means, that the key is missing in parent table and appropriate index needs be created before FK constraint can be created. Since SQL doesn’t allow the index to be dropped there most likely are other corruption issues that have gone unnoticed. If that is an issue, you will have to rely on your backups for recovery.
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
Index Creation Date
Often when troubleshooting performance related issues, it is beneficial to know when new indexes were introduced. However currently there is no easy way to keep track of this information or attain this information. That’s why one of my colleagues and a good friend, Mohamed Sharaf (Blog | Twitter), has submitted a request on Microsoft Connect, Expose index creation date in sys.indexes, Link. Please take a few minutes to vote if you agree 
.
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
