Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing Skills

Coverage Report: SQL Server Bangalore User Group Meeting # 3 – @ 19 January 2013

Posted by blakhani on January 22, 2013


After hearing great feedback about my last UG report, I am thrilled and excited to write report about recent Bangalore SQL Server User Group Meeting on 19th Jan 2013. [apologies for delay as it took time to get the pictures of event). This UG meet was focused on SQL Server Excel based visualizations. Peoples were expected to get some great content that was less known to do inside Excel. It was held at Microsoft Signature building, 4th floor. I came to the event when room was empty and here is what it was looking like when we started rolling our camera…

….and in no time, room was full!

image_thumb13

Excitement was set to start at 10:30 AM and first session was started as per schedule and delivered by Vinod (B|T). His topic was “Tour of PowerPivot with Excel” and he continued with his last session’s excel trick about FlashFill feature of excel 2013.

image

Well received session and ended with claps of 70+ folks.

Next session taken by S Anand (B|T) on “Advanced Excel Visualizations”, who showed is the real power of visualization. He demonstrated analysis done for various classes results, hotel eating pattern, child birth pattern. In below picture, he was explaining why some hotels replaced their 1 liters bottle to 200 milliliters bottle.

image

This session was truly an eye opener and shown that how easy it is to read the data if its presented properly. This was truly one of the mostly discussed session after delivery.

Later, Selva (Blog|FB) from SQL Server Escalation Services team, who was called as hidden Gem by Vinod. Session title for Selva was – Introduction to PowerView with Excel. It was truly amazing session and was a good start point for them who wanted to learn PowerView. As per his demo “we might overtake china in population in next few years”. Sorry Selva, no picture.

Finally, most awaited surprise session from Pinal Dave (b|t) started and in a short session of 15 minutes, people learned about Auto Update Statistics and Auto Create Statistics setting of a database. Pinal was missing the cameraman and suddenly god shown his grace and Manas (b|t), appeared and clicked some pictures.

 

image

In his own unique style of presentation he showed one of the addictive game “Angry Bird” screenshot and asked which level it is. As usual there were many fun elements in the session and no one can forget those 15 minutes. One of the participants , Onkar, who kept him on toes received his famous book SQL Server Interview Questions and Answers (Amazon | Kindle | Flipkart | Indiaplaza) as a reward.

 

Pinal also shared a secret that why he checks in to all new places via foursquare (don’t worry Pinal, I will not write here). Session was full of infotainment followed by closure picture. Below are those folks who stayed late to have a photo session.

Few more folks were nice enough to take a picture with me as well Smile I tried my best to hide my tummy by “cheese and inhale” trick but it didn’t work and got worst of my pic ever.

Below picture of all back-benchers clicked by another back bencher Manas (b|t). Left to Right – Selva (Blog|FB), Govind (B|T) Sudeepta (b|t), Kamlesh (B|T), , Vinod, Balmukund, Deepthi

That’s all for now. I personally had a great time in organizing, executing and becoming reporter of this event along with Vinod.

Feeling free to join the excitement at Facebook.

This is Balmukund, with no cameraman, reporting on wordpress about SQLBangUG#3.

Cheers,
Balmukund

Posted in Uncategorized | 11 Comments »

Coverage Report: SQL Server Bangalore User Group Meeting # 2 – @ 22 December 2012

Posted by blakhani on December 23, 2012


After missing first SQL Server Bangalore UG meet due to team outing, I was ready to meet all my old friends at second SQL Server UG meet yesterday. First of all, Thanks to Vinod (B|T) for starting the idea of SQL Server Bangalore User Group and making it popular on social networking (Facebook www.facebook.com/groups/SQLBangalore and Twitter #SQLBangUG) I have been active on facebook page to help someone who is in need. Feel free to join the group.

As expected, I was able to meet Kamlesh (B|T), Manas (b|t), Sahal (b|t), Sandip (b|t), Sudeepta (b|t) (names are in alphabetical order) from the community. We missed Pinal (b|t)this time as he was not available in town.

Here is the highlights of the whole event as tweeted by me and others. Series of back to back session were suppose to start at 10:30 AM IST and room was getting full. Due to change in venue (from Signature building to Embassy building) there were some folks who were little late.

image

Myself and other speakers were sitting at last bench.

image imageimage

First session was by one of our expert, Kamlesh. His session “Better together: Excel + SQL for Database Developers” was full of demo.

imageimage

image

Second session was by Microsoft Premier Field Engineer, Sourabh Agarwal (b|t). His session, Intro to Indexing – Basics rediscovered, was really to cover the basics and show the internals.

  • @napsterreturns presenting on Indexes (11:02 AM)

imageimage

  • @napsterreturns talking about IAM page (11:04 AM)
  • @napsterreturns talks about data page and index page types. If you didnt know IAM is pagetype 10.(11:10 AM)
  • IAM page id is 10, data page id is 1, index page id is 2 (11:10 AM)
  • @napsterreturns showing DBCC IND and DBCC PAGE outputs.#SQLBangUGmeeting … Mixed extends discussed now (11:12 AM)
  • demo on uses of DBCC IND@napsterreturns (11:12 AM)
  • Now Non-Clustered Index definition. How is structure for Clustered Index and Heap rows. @napsterreturns (11:20 AM)
  • what is plan for queries written?@napsterreturns discusses the basics of Clustered, Non-Clustered and Heap using queries … (11:27 AM)
  • SQL is a cost based optimizer @napsterreturns the basics of INDEX simplified with lots of DEMOs. (11:30 AM)
  • What a perfect timing.. at 11:30 – session was ended. (11:30 AM)

Again, a big round of applauses and Vinod introduced our next speaker, Amit Banerjee (b|t) and his session was “Knowing sp_server_diagnostics output” A feature which is not known to mant DBA. He showed some fantastic demos and shared his nice reports to read that information.

imageimage

  • @banerjeeamit started his session at #SQLBangUG"System Health Session” (11:36 AM)
  • @banerjeeamit shwoing the System health check using "Extended Events" (11:37 AM)
  • @banerjeeamit shows the Extended Events for System Health. Explains the details.#SQLBangUG. Blocking using Extended Events … Demo !!!(11:38 AM)
  • @banerjeeamit talks about Logon errors, Netowrk erros, Lock Waits using default System Health session. (11:47 AM)
  • How can we find the CPU and Memory of SQL Server 15 mins back? @banerjeeamit is making #SQLBangUGmembers think !!! It is Ring Buffer DMV. (11:49 AM)
  • @banerjeeamit showing magical report generated with system health session#SQLBangUG learning Free monitoring!! (11:52 AM)
  • Corrupt pages can also be tracked via sp_server_diagnostics.@banerjeeamit in#SQLBangUG. Now showing failed IO sub system. (11:56 AM)
  • When the issue happened, long IO, which file …SQL Server Health Session Dashboard …. #SQLBangUGby @banerjeeamit (11:56 AM)
  • Great session by@banerjeeamit#SystemHealthSessions (12:01 PM)

And then, I was the next one to speak about, Basics of Backups with SQL Server. My topic was not so new to deliver and to make it more interesting I added few interesting slides. I was able to keep audience awake after 3 heavy sessions. Here is the commentary of my session.

imageimageimage

After my session, we had enough time left before close-out so Vinod did a session about Excel feature Flash Fill. All in the room were shocked and surprised with this feature. Here are the highlights of his amazing session.

 

imageimage

 

Here are the tweets which came in after the meeting.

imageimage

Presentation content for the session is here:

Amit – Refer his blog
Balmukund – Basics of Backups with SQL Server
Kamlesh – Better together: Excel + SQL for Database Developers
Sourabh – Intro to Indexing – Basics rediscovered
Vinod – no ppt only live demos.

That’s all from my side. This was Balmukund, reporting on wordpress about SQLBangUG#2. Hope you have enjoyed it!

Cheers,
Balmukund

Posted in backup, download, Events, ppt, presentation, SQL Server, SQL Server User Group, SQLBangUG | 15 Comments »

Help: Getting error “Value cannot be null” while opening management studio. Which value?

Posted by blakhani on July 23, 2012


One of my friend emailed me today to troubleshoot a problem with SQL Server Management Studio. Here is the problem statement in his own words.

Hey Balmukund,
I know you are the one you can save me now.

I have a problem with my production SQL Server. When I am trying to connect via management studio, I am getting below error

clip_image001

Once I hit OK, I can get in to management studio but can’t expand any node completely.

Using Bingoogle, I found that this can be rectified by tweaking some registry entries. I do not want to take that route, instead want to uninstall only SSMS and install it back.

One of your reply on MSDN shows that you were not able to reproduce the problem. Let me know if you want to troubleshoot this live with me.

Friend:waiting your comments,
<Name not displayed>

For better search here is the text of the message

TITLE: Microsoft SQL Server Management Studio
——————————
Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)
——————————
BUTTONS:
OK
——————————

Once I reached office, I pinged him today and started my day! I followed my usual ways of troubleshooting with a series of question to nail down the problem. Here we go.

<<< Start of Chat Session>>>

Balmukund: Hey.. Just came to office and saw your email. Are you ready to work now?
Friend: Hey Balmukund, Thanks for pinging back. I am ready to work now.

Balmukund: Are you able to connect the same SQL instance from other machines?
Friend: Never tried, let me try right now! (1 min pause) yes, I can.

Balmukund: Could you please share your desktop and let’s see what’s going on?
Friend: Sure!

Balmukund: Okay, I can see your desktop now. Show me the error again.
Friend: There you go! As you can see, I am unable to expand any node completely. It ends up in same error.

Balmukund: Could you please open “New Query” window?
Friend: Let me try as I never did that after seeing this error. (5 second pause). Ouch, another error!

clip_image002

Balmukund: Hmm.. Please highlight the windows and hit Ctrl + C on your keyboard and then paste in chat windows.
Friend: What that would do? Copy image?

Balmukund: No, copy the text of the message.
Friend: Wow. that’s interesting. Here it is

—————————
Microsoft SQL Server Management Studio
—————————
The filename, directory name, or volume label syntax is incorrect. (Exception from HRESULT: 0x8007007B)
—————————
OK
—————————

Balmukund: Thanks. This sounds interesting.
Friend: Really! What’s next?

Balmukund: Since you confirmed that the issue is happening with the specific machine and you are able to connect to same SQL instance via SSMS on other machine then the error is specific to this machine settings. Agree?
Friend: Absolutely.

Balmukund: Now, lets download process monitor and run that tool. Here is the link http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx or you can go to http://live.sysinternals.com and search for procmon to download exe directly.
Friend: Done. Let me run the tool now.

Balmukund: Yes please. Start the data capture, reproduce the error and stop data capture.
Friend: Okay. Done. The is a lot of information. How would you find problem with this?

Balmukund: Lets apply the filter for process name = ssms.exe using Filter in menu bar

clip_image003

Friend: Okay. that has bought down the information captured to only ssms.exe.

Balmukund: Do you see any access to path which is not valid? Do you see “Name Invalid” in Results column?
Friend: Nope.

Balmukund: Okay. Click on New query windows and get other error. Do you see it now?
Friend: Yes. Its trying to access the path “C:\Users\demouser\AppData\Local\Temp;x:\SQL”

Balmukund: Okay. Let me take a screenshot.
Friend: Sure. I am sure you are going to write a blog.

Balmukund: Yes, of course. I have found earlier that few have hit the same null error.

 
ProcMon_NameInvalid

Balmukund: It says temp in the path and also X drive. Do you have that drive?
Friend: No, its not there.

Balmukund: Okay. Could you please open command prompt and type “Set T” and show me the output?
Friend: Sure.

image

Balmukund: I think that explains the issue. TEMP path of your environment variable is incorrect. Can you please correct it?
Friend: Can you tell me steps to change?

Balmukund: Sure.  My Computer > Right Click > Properties. And then choose “Advanced”. Then click on “Environment Variables”

image

Balmukund: Now, change TMP and TEMP to valid location. On my machines its “%USERPROFILE%\AppData\Local\Temp”
Friend: Done.

Balmukund: Now, logoff and log back in.
Friend: Sure. Let me Try…

After 2 min….

Friend: You are the man!
Balmukund: I know <smiley>  Is that working now?

Friend: Of course, Yes!!!! Thanks a ton! You saved me from uninstalling SQL Management Studio.
Balmukund: Anytime! Glad I could help.

<<< End of Chat Session>>>

Later, I search on internet and found various peoples getting same error after installing few softwares. Hopefully this blog will help someone in the world! Here are the few items:
http://connect.microsoft.com/SQLServer/feedback/details/573771/value-cannot-be-null
http://social.msdn.microsoft.com/Forums/en/sqltools/thread/7e0287b3-cd37-408e-8145-4b9dba8c7811 
http://connect.microsoft.com/SQLServer/feedback/details/742857/ssms-studio-reports-value-cannot-be-null-when-attempting-to-copy-headers-with-no-rows

Hopefully this would help someone in the world.

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Screenshot, SQL Server, SQL Server Management Studio, SSMS | 16 Comments »

Help: SQL Server Management Studio is allowing me to connect only to Database Engine! What’s wrong?

Posted by blakhani on May 10, 2012


This blog is an outcome of one of internal email thread where SQL Server Management Studio was allowing to connect only “Database Services” and “SQL Azure”. A picture is worth a thousand words, so here it is:

01_Disabled

Analysis Services, Integration Services, Reporting Services are grayed out/disabled. I thought that this might be due to SSMS Express but looking at “Help” > “about” in Management Studio showed that it was full version.

05_HelpAbout

“Copy Info” gives you this information.

Microsoft SQL Server Management Studio                        11.0.2100.60
Microsoft Analysis Services Client Tools                        11.0.2100.60
Microsoft Data Access Components (MDAC)                        6.1.7601.17514
Microsoft MSXML                        3.0 6.0
Microsoft Internet Explorer                        8.0.7601.17514
Microsoft .NET Framework                        4.0.30319.237
Operating System                        6.1.7601

 

While doing some more research, I remember setup screen had option for Management Tools – Basic and Complete. here is the screenshot of setup screen with both features highlighted.

02_Basic

03_Complete

I guess the feature description explains the behavior. If you want to find what was the option you selected, then SQL setup logs are your friend! [One more trick at the end of the blog]

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\DateTimeStamp\ConfigurationFile.ini

To repro the issue, I selected only Basic and this is what you would see in ConfigurationFile

FEATURES=SQLENGINE,REPLICATION,SSMS

Once setup completes, you would see same behavior which for which I am writing the blog. Now, to fix the issue we need to add “Management Tools – Complete”. Launch “SQL Server Installation Center” > Installation > “New SQL Server stand-alone installation or add features to an existing installation” . Go through the Wizard and choose as below:

06_AddFeature

and then

07_ChooseComplete

Now you should see FEATURES=ADV_SSMS in ConfigurationFile.ini and once you finish the setup, you should be able to select any option.

04_Enabled

If you are not comfortable looking at Setup Logs, you can also use “Installed SQL Server features discovery Report” from “SQL Server Installation Center” > “Tools” Menu

Once you click on hyperlink and launch the report, it would show something like below.

08_Discovery Report

So after Adding Management Tools Complete, I could see that in report.

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Denali, disabled, grayed out, Images, Management Tools Basic, Management Tools Complete, Screenshot, SQL Server 2012, SQL Server Management Studio, SSMS | 2 Comments »

Step by Step – Configuring SQL Server 2012 AlwaysOn in Windows 7 Virtual Environment (Part 1)

Posted by blakhani on May 3, 2012


I have been demonstrating AlwaysOn feature to various audiences on various forums (TechEd, SQL Saturday, Virtual TechDays etc.). I have been using Windows Server 2008 R2 with Hyper-V to create multiple machine and demonstrate the feature. Someone asked me if he can do the same thing using his Windows 7 laptop? I tried looking at virtualization techniques available on Windows 7 and I found that it should be possible. I have tried to make it as descriptive as possible with step by step screenshots to configure whole setup.This topic would be a multipart series.

  1. Configure Windows 7 to use Virtualization and Configure three machines (One domain controller, two member servers). Also configure networking between them.
  2. Create Domain and configure the machines to make sure they are able to talk to each other.
  3. Install SQL Server 2012 on the servers.
  4. Configure AlwaysOn.

First download Windows Virtual PC on Windows 7 machine. Go to http://www.microsoft.com/windows/virtual-pc/download.aspx and choose below option. I am not going to Install Windows XP mode.

01_VPC

One clicked, choose your platform and language and click on download button.

02_VPC

This would install Windows Update to enable Virtual PC

03_Windows Installer

 

Restart the machine and you should be able to see below under "Start” > “Programs” > “Windows Virtual PC”

04_Windows Virual PC Shortcut

Next step would be to download the Virtual Hard Disk (a.k.a. VHD) file which I can use to configure my machine. Note that You need to use 32 bit images to make this work. Here is the link to download Windows Server 2008 images http://www.microsoft.com/en-us/download/details.aspx?id=14527 (Windows Server 2008 Enterprise Edition x86 (Full Install) VHD)

There are three parts which you need to download and then extract to user by Virtual PC. It would look like below.

05_Downloaded VHD

Double Click on the File and Extract the data into “C:\VHDs\DC” folder as shown below:

06_Extract

Once extraction is complete, make three copies of the vhd (Windows2008Fullx86Ent) file and keep them in separate folders as shown below. [I am showing only two, same exists for Srv2 also]

07_Copies

Lets add the machine to Virtual PC. Open the UI from "Start” > “Programs” > “Windows Virtual PC” > “Windows Virtual PC” and choose “Create virtual machine”

08_Create Machine 

Follow the wizard and create DC

09_Create Machine1 

10_Create Machine_Mem

11_Create Machine_Disk

One done, lets go to network setting and choose “Internal Network” This is because I don’t want to create domain controller in my office domain network.

Right Click on DC and choose “Settings”

12_Setting

Go to Networking option and choose “Internal Network”

13_Setting_Network

Now start the first machine and make sure we are able to log in.

14_DC_Startup

You need to follow few steps which are pretty self-explanatory. It would also as you to set machine name (I have given name as DC) new password for Administrator. Once you are done with that, this is what you should see :)

15_DC_LoggedIn

As homework, before moving to second part, you need to do the same thing with two more machines and here is what you should see in Windows Virtual PC.

16_Three Machines

See you at next part of Series!

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, Images, Installation, Installation Guide, Introduction, screen shot, Screenshot, SQL Server 2012, Step by Step | 4 Comments »

“Keep Databases Always Available–AlwaysOn”–TechEd 2012 India

Posted by blakhani on March 20, 2012


image

Title: Keep Databases Always Available–AlwaysOn

Date and Time: 23 March 2012, 14:15 to 15:15

Track – Know Your Data – A Day in Life of a DBA

If you see someone with black & red bag with SQL Server 2012 written on it at TechEd 2012, stop him and say “Hello” because mostly it would be me (If it’s someone else, he would know me). Yes! Once again I got chance to speak at TechEd India 2012 which is being held at Bangalore from March 21-23, 2012. I am getting ready with RTM build of SQL Server 2012 to demonstrate AlwaysOn feature of SQL Server 2012. While this is not the first time I am presenting AlwaysOn but I always enhance it and keep it up-to-date with the current release of SQL Server. I am super excited to be there along with Pinal Dave [B | T], Vinod Kumar [B | T], Amit Banerjee [B | T] and Jacob Sebastian [B | T]

Abstract: This session will describe how to build practical end-to-end HA and DR solutions for mission critical applications using SQL Server 2012 AlwaysOn technology. For example, how to combine Failover Cluster Instances with Availability Groups to provide a complete HA and DR solution, how to use Availability Groups Multiple Secondaries to replace existing database mirroring + log shipping solutions. Readable Secondaries, backup on secondaries are some of the cool features which will be covered.

Location: Hotel Lalit Ashok – Kumara Krupa High Grounds, Bengaluru – 560001, Karnataka, India.

There are various other posts about TechEd 2012. Pinal and Vinod posted summary of about various sessions [here and here].  Vinod and Amit also posted about their own sessions. I liked Manas post which has link to various blogs

Since myself, Amit, Pinal and Vinod belong to different workgroups, this is a chance to meet all of us at the same place. This is the last and final call for all folks who don’t want to miss the fun and want to “Go Big”!

Come and Join us!

Regards,
Balmukund

Posted in TechEd | 4 Comments »

Help : I purchased proper media to upgrade SQL version, still setup thinks that its Evaluation. What’s wrong?

Posted by blakhani on February 12, 2012


Have you came across the scenario where you are performing a version upgrade of SQL Server as per supported path described here but still you are facing below error message during rules check?

<Error>
Error:
========
Rule “SQL Server 2008 R2 Feature” failed.

The specified edition upgrade is not supported. For information about  supported upgrade paths, see the SQL Server 2008 R2 version  and edition upgrade matrix in books online.
</Error>

If you look at Detail.txt file created by setup, search for “FeatureUpgradeMatrixCheck” and have a closer look at the log. From the highlighted you can easily understand the source and destination version+edition. So as per below log, setup is trying to upgrade from SQL 2000 Standard to Evaluation Edition (which is not in the link I provided earlier)

2011-01-27 22:53:34 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.SkuUpgradeRule
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ looking for previous version upgrade data for feature package ‘sql_as_Cpu32′.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ feature package ‘sql_as_Cpu32′ found no upgrade features.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ looking for previous version upgrade data for feature package ‘sql_engine_core_inst_Cpu32′.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Found feature package  ‘Sql2000Ent_Cpu32′ with SkuValue=STANDARD ProductVersion=8.00.2039
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ found sourceVersion 8.0 and edition STANDARD for feature package ‘sql_engine_core_inst_Cpu32′.
2011-01-27 22:53:34 Slp: — SkuPublicConfigObject : ValidateSkuMatrix checking sku matrix for sourceVersion=8.0 sourceEdition=STANDARD sourceArchitecture=X86 targetEdition=EVAL targetArchitecture=X86
2011-01-27 22:53:34 Slp: — SkuPublicConfigObject : ValidateSkuMatrix source and target architecture match.
2011-01-27 22:53:34 Slp: — SkuPublicConfigObject : ValidateSkuMatrix did not find a match in sku matrix .
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ feature package ‘sql_engine_core_inst_Cpu32′ is blocking upgrade.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ detection result: IsValidFeatureUpgrade=False
2011-01-27 22:53:34 Slp: Evaluating rule        : FeatureUpgradeMatrixCheck
2011-01-27 22:53:34 Slp: Rule running on machine: SQLSERVER
2011-01-27 22:53:34 Slp: Rule evaluation done   : Failed
2011-01-27 22:53:34 Slp: Rule evaluation message: The specified edition upgrade is not supported. For information about supported upgrade paths, see the SQL Server 2008 R2 version and edition upgrade matrix in Books Online.
2011-01-27 22:53:34 Slp: Send result to channel : RulesEngineNotificationChannel
2011-01-27 22:53:34 Slp: Initializing rule      : Incomplete upgrade
2011-01-27 22:53:34 Slp: Rule applied features  : SQL;SQLEng

On a side note, Do you know the media difference for Standard and Evaluation edition? Well, there is no difference! Its the PID (Product ID) in below screen which decides the edition to be installed. The value of PID is picked from DefaultSetup.Ini file located on media. If there is no such file on Media then it would be enterprise evaluation edition of SQL Server.

Here is the little test which would prove the theory. I swear to god that I have not hit cancel while doing the test. Its the same media used for all the screen (Evaluation Media) All I did was change value in Enter the Product Key and hit next> or previous<.

VersionUpgrade_01imageVersionUpgrade_02

VersionUpgrade_03imageVersionUpgrade_04

VersionUpgrade_05imageVersionUpgrade_06

 

Since I had media for various editions (How? Did I tell you that I work with Microsoft SQL Support team.. lol!), I copied/pasted the PID from DefaultSetup.ini file and rest can be seen above.

You might say, Nice to know what’s happening behind the scene but what you need to do if you run into such situation. In my scenario customer informed that they purchased Standard edition of SQL Server 2008 R2 still its taking EVAL in targetEdition and there has to be flaw somewhere. Later we found that screen was selecting Enterprise Evaluation by default (not sure why) So once he entered the Product Key, it worked like a charm!

Now, go ahead with the setup and post a comment here if this helped.

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Edition Upgrade, Error, Evaluation, Images, PID, Product Key, Screenshot, SQL Server, Step by Step, Version Upgrade | 1 Comment »

Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do?

Posted by blakhani on February 8, 2012


If you are thinking that I am going to show you black magic to recover sa password or other login’s password then you have hit the wrong blog post. go back to search engine and search for better tool/utility. Smile

After spending many years with SQL Server product, I have seen situations where someone wants to get in to SQL Server as system administrator as someone recently left company who had System Administrator permission (or hundred other reasons). Till SQL 2000 days, it was impossible to solve such problem other than reinstalling SQL Server. Here are the typical questions I saw in forum:

  • Only sysadmin user is SA and I Lost SA password.
  • I am locked out of SQL server i.e. that no windows users are added (or removed them) as sysadmin and I forgot the password for sa
  • I am windows admin. How can I get sys admin privileges on SQL server express as I removed all sysadmin accounts from SQL.

Here are the various error you might see

  • Unable to create new database. This is generic error which means that you are not having permission.

SysAdminLost_1

TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Database ‘SQLServer-Help’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476
——————————
BUTTONS:
OK
——————————

  • When you login to Management Studio, you would see only your own account and ‘sa’ as shown below

SysAdminLost_2

  • When you attempt to change password of ‘sa’ you might see below error.

SysAdminLost_3

TITLE: Microsoft SQL Server Management Studio
——————————
Change password failed for Login ‘sa’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Change+password+Login&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot alter the login ‘sa’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15151&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

All error messages appear because your account is not a System Administrator of SQL Server Instance.

So, what should you do now? Answer is simple, get yourself added as System Administrator. I know, you would say “don’t you think I have tried that as I got this error!”

SysAdminLost_4

TITLE: Microsoft SQL Server Management Studio
——————————
Add member failed for ServerRole ‘sysadmin’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+ServerRole&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15247&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

Okay, here is the step by step guide to add any account as System Administrator of SQL Server. This is documented and completely supported way to gain back the rights.  To log into SQL Server as SysAdmin, you need to have Local Administrator permission on the windows which is hosting SQL Server. If you don’t have that also then you may want to check with your windows team to get access (I am not a windows guy)

Steps to login to SQL Server as System Administrator. [Provided you are having windows local administrator permissions]

  • Stop the SQL Server Service using ANY of below command.
    • Net Stop MSSQLServer (for default instance) / Net Step MSSQL$<InstanceName> If you want to know instance name, refer my earlier blog
    • Use SQL Server Configuration manager and stop the SQL service. [Start>Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager]
    • Use Services console [ Start > Run > Services.msc] and locate the SQL instance you want to stop.
  • Start SQL Server in Single User mode. You need to use start-up parameter m to start SQL Service in single user mode. I prefer command line but its your choice.
    • Using command line
      • net start MSSQLServer /m SQLCMD        [For default instance]
      • net start MSSQL$<InstanceName> /m SQLCMD     [For named instance]
    • Using configuration Manager
      • Locate the service which you have stopped earlier. Go to its properties, “Advanced”, click on drop down at “Startup Parameters”  and add ;-mSQLCMD as shown below

SysAdminLost_5

You might notice that I have use SQLCMD after m. That’s not a typo. Many times, when you start SQL Server in single user mode, application grabs connection before you could. SQLCMD ensures that only SQLCMD program can connect to SQL Server when its running in single use mode. Here is the error you might see if above happens. SQLCMD should be in UPPERCASE. else that would also show same error. Please make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

TITLE: Connect to Server
——————————
Cannot connect to (local)\SQL2k8R2.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS:
OK
——————————

  • Connect to SQL Server and add desired account in SysAdmin role.
    I normally prefer to do it from SQLCMD (that’s why I added SQLCMD after m)
    • Open administrator command prompt. (i.e. right click on command prompt shortcut and choose “Run As Administrator”
    • Type sqlcmd –S <complete instance name> For example
      • sqlcmd –S. (for default instance)
      • sqlcmd –S.\MyInstance
        For getting exact name, your my earlier blog
    • You are connected as System Administrator, because you are part of local administrator group in windows.
    • At this point you can add any account to sysadmin because you are connect as sysadmin. Here is the script I normally use to add local administrator group as a part of SysAdmin group in SQL Server Instance. You may want to tweak this as per your needs because I am adding all local admin as sysadmin which is not a good practise.

USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO

  • Here is how it would look on sqlcmd command prompt

SysAdminLost_6

  • Stop SQL Server Service. You can use any method as described in first step
  • Start SQL Service normally. This means that you need to remove startup parameter

Here is the MSDN reference for above, just to show that its documented.

So, to conclude, I have not shown any trick to recover any password. Just showed you detailed steps to gain sysadmin access provided you have windows admin rights.

DISCLAIMER: Use the method that is described in this article only as a failure recovery mechanism

Posted in Configuration Manager, ERRORLOG, forgot sa password, Forgot the SA password in Sql Server 2005, sa password lost, screen shot, Screenshot, Security, SQL Server, Step by Step | 9 Comments »

Help: Getting Visual Studio Service Pack Error while installing SQL Server 2012 Release Candidate 0 (RC0)

Posted by blakhani on December 8, 2011


To make my AlwaysOn demo up-to-date with latest release, I uninstalled SQL Server 2012 (a.k.a. Denali) CTP3 from my machine(s) and and started to install SQL Server 2012 RC0.

Setup started fine and later after doing feature selection and then I received this error. Interestingly I didn’t receive error another machine both were made from same image.

01_VSErrorBlog

Here is the text of the error message for better search.

—————————
Rule Check Result
—————————
Rule "Prior Visual Studio 2010 instances requiring update." failed.

This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection. To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649.
—————————
OK  
—————————

one might ask, I don’t have Visual Studio product installed, why do I get this error. Well, SQL Server Tools (SSMS, BIDS) uses Visual Studio Shell. But, I thought that why setup didn’t do this check on earlier screens. Later, looking at the code I realized that we do this check if we install components which need Visual Studio Shell (like SQL Server Management Studio)

Here is the public proof of my theory from setup logs. You will also understand what all we check over here.

<Detail.txt>
(07) 2011-12-07 22:46:51 Slp: Loading rule: DEV10RTMDetected
(07) 2011-12-07 22:46:51 Slp: Creating rule target object: Microsoft.SqlServer.Configuration.SetupExtension.DetectInstalledProductsFacet
(07) 2011-12-07 22:46:51 Slp: Rule applied features  : BIDS;SSMS
…
…

(12) 2011-12-07 22:46:51 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.DetectInstalledProductsFacet
(12) 2011-12-07 22:46:51 Slp: Package ID VSIntShellRTM_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSIsoShellRTM_Cpu32: Installed – Version: 10.0.30319
(12) 2011-12-07 22:46:51 Slp: Package ID VBExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VCSExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VCExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID WPExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSPremium_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSPro_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSTE_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSUltimate_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSLabAgent_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSTestAgent_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSTestController_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VWDExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: The package ‘VSIsoShellRTM_Cpu32′ is installed.
(12) 2011-12-07 22:46:51 Slp: Rule ‘DEV10RTMDetected’ detection result: AreAnyProductsInstalled= True
(12) 2011-12-07 22:46:51 Slp: Evaluating rule        : DEV10RTMDetected
(12) 2011-12-07 22:46:51 Slp: Rule running on machine: ALWAYSON-SRV4
(12) 2011-12-07 22:46:51 Slp: Rule evaluation done   : Failed
(12) 2011-12-07 22:46:51 Slp: Rule evaluation message: This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection. To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649.
</Detail.txt>

DEV10RTMDetected is the rule name and it would fire only if you select BIDS and/or SSMS, we would fire the rule and block installation on failure. Now why it did not come on another machine because I didn’t select tools there.

Okay, now you may ask – what I need to do now? Well, have a look at error message. It tells you the action needed. So here are the solutions.

  • Go to http://go.microsoft.com/fwlink/?LinkID=220649 and download SP1. As per note on the link

    ”This web installer downloads and installs Visual Studio 2010 Service Pack 1. An Internet connection is required during installation”

  • Other better way is to use the setup media. Yes, you read is correctly, SQL Server 2012 RC0 setup media. Below is the location of the package to install Visual Studio 2010 SP1

 
02_VSErrorBlog

Once you start installing this patch, below is what you would see.

03_VSErrorBlog

If you have not cancelled the setup after seeing the message, you can use “Re-run” button to run the check again

04_VSErrorBlog 

And you are green!!

05_VSErrorBlog

Now, go ahead with the setup and post a comment here if this helped.

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Denali, Error, Installation, RC0, screen shot, Screenshot, SQL Server 2012, SQL Server RC0, Visual Studio | 14 Comments »

Step by Step: Installing SQL Server 2012 RC 0

Posted by blakhani on November 30, 2011


For my upcoming internal session on contained database, I decided to install latest version of SQL Server 2012 (earlier known as SQL Server “Denali”) to show the demo. Here are the steps with screenshots to help someone who is doing the same. So, here we go!

I have done this on my Hyper-V machine so that I can always rollback. I have used SQL Server 2012 RC0 (Release Candidate 0) build. While writing this post, RC0 was the latest version available and can be downloaded from here

  • Copied the media to local machine, I prefer this to avoid any network hiccups which might cause my setup to fail. Now run Setup.exe

Choose Setup.exe

  • After clicking you should see “SQL Server Installation Center”. On left menu, click on “Installation” and then click on “New SQL Server stand-alone installation or add features to an existing installation”

02_Setup

  • Once you click, you should see “Setup Support Rules” screen.

03_Setup

  • Hit OK and you should get “Product Key” Screen. Since I launched setup from Evaluation Media, it doesn’t populate Product Key. Hit Next.

04_Setup 

  • Next would be License Terms screen. Check the box “I accept the license terms.” and Next.

05_Setup

  • Next Screen is about auto patching of setup. Since I am using version which is not having any further updates, so I would uncheck the option and move forward. I would cover more about this feature later.

06_Setup

  • Hitting Next would install setup support files as shown below.

07_Setup

  • Next screen would just show the rules. Hit Next.

08_Setup

  • I have selected first option as I don’t play with SharePoint.

09_Setup

  • Normally I don’t click “Select All” but to get all screens, i have selected this. Notice the left menu getting changed based on your selection. Hit Next.

10_Setup

  • Hit Next

11_Setup 

12_Setup

  • Summary of disk space. Hit Next.

13_Setup

  • If you want to change service account, you can provide details and password. You can also specify “Startup Type”. Hit Next.

14_Setup

  • In this screen I am going with Mixed mode and given password. Also clicked on “Add Current User” to add logged on user as Sysadmin in SQL Server.

15_Setup

  • Please don’t ask question about SSAS. I have just hit next clicking on “Add Current User”.

16_Setup

  • In “Reporting Services Configuration” screen, just hit next.

17_Setup

  • Next screen should be “Distributed Replay Controller”. I have just hit next clicking on “Add Current User”. I would cover this feature in detail in some later blog.

18_Setup

  • In “Distributed Replay Client”. I have entered some name as I don’t know what to enter here. I should be able to change this later (I think so). At this point I am yet to read more about this feature.

19_Setup

  • If you want us to provide feedback about setup and some watson failures, you can check the box and in “Error Reporting” screen and then hit next. I always check this because I know this is really useful to make product better.

20_Setup

  • Next screen would do validation about installation/system and may block you if one or more rules are failed.

21_Setup

  • Finally, the summary of all options selected so far. Very important information is Configuration File Path which can be used if you want to have automation. Remember that we don’t store any password in that file so you are safe!

22_Setup

  • “Installation Progress” screen would show the actions being performed.

23_Setup

  • And here is the screen which you would all love to see.

25_Setup

That’s all for today. I would love to hear feedback from reader(s).

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Denali, Images, Installation, Installation Guide, screen shot, Screenshot, SQL Server 2012, Step by Step | 12 Comments »

« Previous Entries
 
Follow

Get every new post delivered to your Inbox.

Join 858 other followers