Microsoft releases CU #1 for SQL Server 2016

Microsoft released their latest flagship database product, Microsoft SQL Server 2016 on June 1st 2016. After 2 months, now Microsoft announced the release of CU #1 which contains fixes for 146 issues across different categories of the product.

The CU1 has 146 hotfixes in 10 different categories as stated above. The highest no.of hotfixes are for the SQL Service.

You can download the CU1 by using the below link.

Cumulative Update 1 for SQL Server 2016

Cheers.

Denver SSUG Presentation About Latch Behavior

Yesterday I did a presentation about SQL Server Latch behavior at SQL Server User Group at Denver.

Hope everyone has learnt something and got some exposure to SQL Server latches which is internal to SQL Server and some time create issues. So that knowing about what they are and how they behaves is important in my opinion.

Below link contains the ppt and the demo scripts I used. The demo scripts are compatible with SQL Server 2014 or later.

Presentation and Demo scripts

Cheers!

How do I get access to SQL Server instance when no other access is possible

Recently I had a situation where no one knows any level of credentials to SQL a Server instance. The instance I tried was a SQL Server 2008. By default SQL Server 2008 does not provide admin access to built in Windows local admin group.

I was able to get access to it by following below steps;

1. Stop SQL Server service using SQL Server configuration Manager.
2. Right click on SQL Server service and get properties.
3. In Log On tab, use This account option to provide a windows domain account credentials which you already know. I my case I provided my credentials for the specific domain.
4. Use Advanced tab to add “-m;” startup parameter to the beginning of the parameter list. The –m startup parameter is use to start SQL Server service in single user mode.
5. Click on Apply and then Ok.
6. Start SQL Server service.
7. Open CMD shell in administrator mode and type the following sqlcmd statement; This will test the access to the server and if it successful, it returns the SQL Server name. This is just a verification.
1. sqlcmd -E -S <server name> -q "select @@servername"
8. Use the below sqlcmd statement to create a new user called “recovery”.
1. CREATE LOGIN recovery WITH PASSWORD = '1qaz2wsx@'
9. Use the below sqlcmd statement to grant admin privilege to the user we just created.
1. SP_ADDSRVROLEMEMBER 'recovery',SYSADMIN
10. Stop the SQL Server service using Configuration Manager.
11. Get SQL Server service properties and remove the startup parameter “-m” and click Ok.
12. Start the SQL Server service.
13. Open SSMS and try to connect to the server using the user name and password we created in above steps.

Hope this helps. Cheers!

Reference:

http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx

Another process has taken SQL Server assigned port

I received the below error while I was doing failover testing on a newly built SQL Server 2012 cluster.
The SQL Server (MSSQLSERVER) service terminated with service-specific error An attempt was made to access a socket in a way forbidden by its access permissions..
After few troubleshooting I found this was due to the port ​xxxxx was taken control by another process. Apparently the process was Cluster Manager. :) So I closed the Cluster Manager and re-opened and then failover was successful. This seems to be very rare occurrence but it is possible.
Refer the below link for more details;

Server TCP provider failed to listen on...

SQL Server Change Tracking Performance Troubleshooting

https://www.mssqltips.com/sqlservertip/4035/sql-server-change-tracking-performance-troubleshooting/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20150929

Essential Tips for Virtualizing SQL Server

Very good article on Vitalizing SQL Server. Read Essential Tips for Virtualizing SQL Server

ALTER PARTITION FUNCTION Causes Blocking?

Recently I’ve experienced a situation where one of the partition maintenance jobs created a blocking. At this point the database had high t-log usage and backup log was also executing. It was clear some kind of large transaction has occurred during a partition maintenance operation. All inserts into the table which was being partitioning, got blocked too. 

MSDN states:

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

ALTER PARTITION FUNCTION (Transact-SQL)

So it is important to select a proper partition key so that when achieving using sliding window concept, it works without impacting users.

Cheers.