Tuesday 16 September 2014

SQL Server: SQL server 2012 - Unable to open SSMS after installing Visual Studio 11 Developer


Issue: SQL server 2012 - The application cannot start

Last Thursday I installed SQL Server 2012 client tools on the Windows 8 Dev and everything was working fine. Then after few days, I installed the Visual Studio 11 Developer Preview.

The next morning after I booted up, I have not been able to launch SQL Server 2012.
Solution:

This happens after installing SQL Server 2012 (Code named "Denali CTP3") or later, running SSMS successfully, then (later) install VS11 Dev Preview, and then run SSMS.

Dev11 Preview improperly recognize SQL Server Denali SSMS as a version 11 application and it updates the current user’s setting in SSMS registry key during the setup. This issue occurs only when Dev11 Preview is installed after the installation of SSMS.

SQL Server 2012 SSMS has a built-in security and resiliency feature for any corrupted configuration in HKCU registry key. The corrupted registry keys can be simply deleted then SSMS automatically reconstructs it with correct values during the next startup procedure.

• Start regedit.exe

• Go to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio

• Delete 11.0_Config

• Restart ssms.exe

Friday 20 June 2014

SQL Server: Code Name along with year of release


You'll always feel good when you learn/know new things and it is very much true with people who want to learn more and more.

I already knew couple of code name for recent releases so thought to list down all including SQL Server 2014 and share with all of you.

Please see the below list to get code name along with year of release.

SQL Server Release
Project Code Name
 Year of Release
SQL Server 4.21
SQLNT
 1993
SQL Server 6.0
SQL95
 1995
SQL Server 6.5
Hydra
 1996
SQL Server 7.0
Sphinx
 1999
SQL Server 2000 (32-bit)
Shiloh
 2000
SQL Server 2000 (64-bit)
Liberty
 2003
SQL Server 2005
Yukon
 2005
SQL Server 2008
Katmai / Akadia
 2008
SQL Server 2008R2
Killimanjaro
 2010
SQL Server 2012
Denali
 2012
SQL Server 2014
Hekaton
 2014

Hope it will help you at some point

Warm Regards,
Chhavinath Mishra
 
Sr. Specialist Database Administrator
MCITP

Wednesday 28 May 2014

SQL Server Configuration Manager – Cannot connect to WMI provider – Invalid class [0x80041010]


Problem:
I am getting this error message - "Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with the SQL Server Configuration Manager. Invalid class [0x80041010]" when trying to launch SQL Server Configuration Manager.



Had an instance been uninstalled? (Not necessarily the one you are connecting too)? If so, the workaround is mentioned below.

Workaround:
To work around this problem, open a command prompt, type the following command, and then press ENTER:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"

Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

For executing the command make sure that you have to log in as an administrator.

The value of number depends on the version of SQL Server: nnn

Microsoft SQL Server 2012
110
Microsoft SQL Server 2008 R2
100
Microsoft SQL Server 2008
100
Microsoft SQL Server 2005
90

After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation.



Warm Regards,

Chhavinath Mishra
Sr. Specialist Database Administrator
MCITP

Monday 31 March 2014

SQL Server : Logical CPU Count

Scheduler is a component of SQLOS which evenly distributes load amongst CPUs.

scheduler_id – ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 1048576. Those schedulers that have IDs greater than or equal to 1048576 are used internally by SQL Server, such as the dedicated administrator connection scheduler.

SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM    sys.dm_os_sys_info ;

SELECT  cpu_count FROM    sys.dm_os_sys_info ;

SELECT  hyperthread_ratio FROM    sys.dm_os_sys_info ;

SELECT  * FROM    sys.dm_os_sys_info ;


Brgds,

Chhavinath Mishra
Sr. Database Administrator
Microsoft Certified IT Professional (MCITP)


Monday 3 March 2014

SQL Server : Change the startup accounts (Service account) for SQL Server services in cluster

While working on a project to build SQL Server 2 node cluster, I have seen that few times we have requirement to change the service account post installation of SQL Server 2008/R2 or 2012 due to any reason like same account is used with other instance and now business need to have separate account which should be dedicated to that particular instance only.

In case of Standalone instance, this task is quite simple as we need to change the account from SQL Server configuration and restart the underlying services to ensure that change is effective from that point of time.

In case of cluster, We have 2 sets of services ( if we are having 2 node cluster) or more than that depending upon how many nodes cluster we are running with.

We need to make changes only on Active node and then this change will be effective automatically on all passive nodes.

Step1:  On active node, GO to Configuration Tools, and then click SQL Server Configuration Manager
Step2:  In SQL Server Configuration Manager, click SQL Server Services
Step3:  Click Properties then click the Log On tab and select a Log on as account type
Step4:  After selecting the new service startup account, click OK
Step5:  Now, either take the SQL Server resource group offline and then bring it online or restart the server if you are running only single instance

Hope it will help you.

P.S: While changing service account to a new domain user by SQL Server Configuration Manager in a clustered SQL Server instance, the server threw exception 0x80010105” may arise. Please follow below article which I found very helpful and hope it would be helpful to you as well if you face same issue.



Brgds,

Chhavinath Mishra
Sr. Database Administrator
Microsoft Certified IT Professional (MCITP)


Tuesday 18 February 2014

SQL Server : To ensure efficient tempdb operation (Microsoft Recommendations)

While working on a performance issue, I have seen that many a times we have Long IO Errors on Tempdb database, which indicates that we have scope of optimization for our tempdb Database.

As per Microsoft, there are a few Recommendation which we should Test on Benchmark Servers for sure before making any changes in production environment.

We could see the below options for Tempdb Optimization. These are always the best practices which often increase performance. As we know the Role of Tempdb has increased Drastically after the introduction of Sql Server 2005 so , why not Try them on Bench and implement on production.

To ensure efficient tempdb operation:

·         Create one tempdb file per physical CPU core. (But should ideally be used when we have data files on separate disk spindles).
  This reduces page free space (PFS) contention.
·         Pre-size the tempdb files, and make the files equal in size.
·         Do not rely on autogrow.
·         Use startup trace flag 1118.

For more information about this SQL Server trace flag, pls. go through this link  http://support.microsoft.com/kb/328551

Note: There’s only one tempdb per instance, and lots of things use it, so it’s often a performance bottleneck. You guys know that already. But when does a performance problem merit creating extra tempdb data files?

There should be only 8 data files even we have more than 8 processor as per Microsoft recommendation unless there is  in-memory contention

Hope it will help you.

Brgds,

Chhavinath Mishra
Sr. Database Administrator
Microsoft Certified IT Professional (MCITP)





Sunday 16 February 2014

SQL Server : Copy backup files from one SQL Box to Another SQL Box using FTP

While working on a database migration project , we would require to transfer a huge backup file to new server which is running SQL Server 2012 but there was no trust enable between 2 different domain so normal copy operation was not allowed hence we thought to use FTP . I feel that there is always a need to FTP files to and from an SQL Server so I thought to make note of simple commands which will be useful in future.

FTP is an acronym for File Transfer Protocol. As the name suggests, FTP is used to transfer files between Servers/computers on a network.

Requirements:

1. FTP location name or IP @
2. User name & password ( In my case, I have used Anonymous FTP)

Anonymous FTP :

At times you may wish to copy files from a remote machine on which you do not have a loginname. This can be done using anonymous FTP. When the remote machine asks for your loginname, you should type in the word anonymous. Instead of a password, you should enter your own e-mail address. This allows the remote site to keep records of the anonymous FTP requests.

Note: However, you are only able to copy the files from the remote machine to your own local machine; you are not able to write on the remote machine or to delete any files there.

Step1:  Connect to server in which you want to copy the backup file ( Target server which is running SQL Server 2012)

Step2: Go to command prompt ( Run > cmd)

Step3 : C:\Users\Admin>FTP AB0010-SQL-FT
Connected to XXXXXXXXXXXXXXXXXXXXXXX
220 Microsoft FTP Service
User (AB0010-SQL-FT:(none)): anonymous
331 Anonymous access allowed, send identity (e-mail name) as password.
Password:
230 Anonymous user logged in.
ftp>

ftp> cd backup -- cd for change directory in remote server
250 CWD command successful.
ftp> pwd -- pwd means present working directory
257 "/backup" is current directory.
ftp> ls -- list all file and folder

ftp> lcd F:\  -- lcd to change directory in local server
Local directory now F:\.
ftp> lcd db_backup
Local directory now F:\DB_Backup.
ftp>

ftp> binary
200 Type set to I.
ftp> hash
Hash mark printing On  ftp: (2048 bytes/hash mark) .

ftp> get DB_Full_backup.bak
200 PORT command successful.
150 Opening BINARY mode data connection for DB_Full_backup.bak

ftp> bye
421 Timeout (120 seconds): closing control connection.

Last but not least, we can use put command instead get command to place backup file to source server.

Note: C:\Users\Admin>FTP -A AB0010-SQL-FT ( -A means Anonymous FTP)

Hope it will help you.

Brgds,
Chhavinath Mishra
Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

Thursday 6 February 2014

SQL Server : Drop database in emergency mode

You'll always feel good when you learn/know new things and it is very much true with people who want to learn more and more.

Today, I was working with one corrupted database on SQL Server 2012 to recover it and I came to know one interesting thing (at least for me) that when you drop one database which is set in emergency mode then it only delete data file . Yes, It does not delete log file.

To validate the above fact, I have dropped corrupted database and tried to create same database again with old DB creation script it ended up with error that log file exist.

Second, I have created another database ( To reconfirm that it is not something related with corruption) and set to emergency mode and then dropped it but again it didn't deleted log file.

I'll share the more details when I'll complete assigned task.

Thanks/

Brgds,

Chhavinath Mishra
Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)