Advertisements

SQL Skills: SQL Server Wait Types & Latch Classes Library

May 17, 2016 Leave a comment

Microsoft has documented wait types and latches in a minimum fashion on books online; often there are wait types that are just not documented.

Paul Randel (Blog|Twitter) has put together compressive list of these with recommendations and even SQL dump traces.

Check it out here..

 

Advertisements

Troubleshooting Database Mirroring in MS SQL Server

May 13, 2016 5 comments

Database Mirroring ensures availability of SQL Server database in scenarios of data disasters. It is implemented in SQL databases that use Full Recovery model. It maintains two copies of a particular database, which reside in different locations. When the primary server is unavailable due to any reason, the secondary server comes into action and proceeds with the functioning.

In this blog, we will illustrate some of the measures to troubleshoot database mirroring in SQL Server. Below is the list of some factors that need to be considered whilst troubleshooting:

Accounts

  • It is to be noted that the accounts, which are facilitating the SQL server to run, should be correctly configured. In case the accounts run on the same domain accounts, there are less chances of misconfiguration.
  • However, in case the accounts are running on different domains, the login of one of the accounts should be created on the other computer in master. In addition to this, the login should be granted CONNECT permission in the endpoint.
  • In case SQL Server is running as SaaS and is using the local system account, certificates must be used for authentication purposes.

Endpoints

Following key points should be considered to make sure that the endpoints are configured correctly:

  • Make sure that all the server instances- principal, mirror and witness server should possess database mirroring endpoint. Database Mirroring Endpoint can be created either by using Windows Authentication or by using certificates.
  • Make sure that all the port numbers are correct. In order to identify the port number associated with database mirroring endpoint use the following catalog views:
    • sys.database_mirroring_endpoints
    • sys.tcp_endpoints
  • Check whether the status of the endpoints is STARTED. For each server instance, make use of the following Transact-SQL command:
SELECT state_desc
  FROM sys.database_mirroring_endpoints
  • For starting an endpoint use the following command:
ALTER ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = <port_number>)
FOR DATABASE_MIRRORING (ROLE = ALL);
  • In order to check whether the ROLE is correct or not, use the following command:
SELECT role
  FROM sys.database_mirroring_endpoints;

System Address

For naming the server instance in the database mirroring, any name, which identifies the system can be used. If the systems reside in the same domain, the system name can be used as the server name. In addition to this, a static IP address or a fully qualified domain name can also be used as the server names.

Network Access

If the server instances are in different domains, it is necessary for every server instance to access the ports of all the server instances or the instances over the TCP. This puts a stop to the communication between the instances of the server.

Mirror Database Preparation

The most important thing that is to be made sure is that the database is prepared for mirroring, no matter if the mirroring is done again or for the first time. It is to be noted that whilst creating a mirror database on the mirror server, restoration of principal database backup with the same name of the database is to be done. Moreover, all the log backups created prior to backup creation should also be applied. Both the things should be done WITH NORECOVERY.

It is recommended to store the mirror database at the same location as of the principal database. However, if the file paths are different, MOVE option should be included with RESTORE statement.

In case the mirroring was stopped earlier and is being restarted, all the backups taken afterwards should be applied to the mirror database.

Fixing Failed Create-File Process

In order to add a file without having any impact on the mirroring session, the path of the file should exist on both the servers. Due this very fact, if the database files are moved while mirror database creation, the add-file operation might fail and the mirroring can be suspended.

In order to fix the issue, follow the below-mentioned steps:

  1. The complete mirroring session should be removed and the full backup of the file group containing the added file must be restored.
  2. The log containing the add-file operation should be then backed up. Moreover, the log backup should be restored manually by using WITH NORECOVERY and WITH MOVE. This will create a file path on the mirror server and the file will be restored to a new location.

Conclusion

Care should be taken while troubleshooting database mirroring in SQL server irrespective of the fact that the mirroring has been previously done or not. With the help of the above-mentioned measures, the users can easily fix the issues while creating database mirroring.

SQL Server 2016 Release Date

May 3, 2016 Leave a comment

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.

 

Categories: Announcement, SQL 2016 Tags:

SQL Server 2014 & 2016 Developer Edition

April 26, 2016 Leave a comment

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

April 19, 2016 2 comments

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.

Categories: Announcement, SQL 2016 Tags:

SQLPASS Edmonton (Apr. 9, 2016) – Key Note Presentation

April 10, 2016 6 comments

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?

April 4, 2016 2 comments

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.

Categories: Administration, Security Tags:

How get all users and their role mappings from all databases?

April 2, 2016 5 comments
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
Categories: Administration, Security Tags:

Microsoft SQL Server 2016 RC2 Released

April 1, 2016 Leave a comment

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.

https://blogs.technet.microsoft.com/dataplatforminsider/2016/04/01/sql-server-2016-release-candidate-2-now-available/?utm_content=buffer3b3f6&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

Categories: Announcement, SQL 2016

Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group

January 13, 2016 2 comments

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.

image

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.

%d bloggers like this: