Differences between SQL Server and SQL Azure

I captured this screenshot from MS Azure Webinar and thought of sharing it since it gives a high level overview of supported features in SQL Server Azure. 

For more details: https://www.concurrency.com/blog/august-2016/resources-for-sql-server-in-azure-essentials-for

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