Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing Skills

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 | Leave a Comment »

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 Introduction, Denali, AlwaysOn, SQL Server 2012, Installation, Step by Step, Images, Screenshot, Installation Guide, screen shot | Leave a Comment »

“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 | Leave a 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 | 2 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 | 2 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 | 9 Comments »

Virtual TechDays Slides on AlwaysOn and Good Practices

Posted by blakhani on September 29, 2011


Today I was fortunate enough to deliver two online sessions via virtual techdays. I have been asked to share the slide deck. So here I am uploading the presentation.

1. Tips for Successful SQL Server Deployment in Enterprise Environment.pptx
Abstract : SQL Server performs best when it is setup keeping all the best practices in consideration. In this session we will learn various deployment tricks and tips which will SQL Server to perform at its best all the time. Presenter will demonstrate few tricks which are not common but can improve performance dramatically.

2. High Availability – A Story from Past to Future.pptx

In this session we will go over various aspect of High Availability. This feature has moved a long way and have taken different shape during the years. We will understand the features past capabilities and how it has been enhanced with new versions ‘Denali’. Here, I have covered below topics
a. Log-Shipping
b. Database Mirroring
c. AlwaysOn features like Backup on Secondary, Readable Secondary, Multiple Secondary to name a few.

The recording and ppt would be officially available after some days on www.VirtualTechdays.com

Hope you would enjoy it!

Regards,
Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, SQL Server | Leave a Comment »

Help: AlwaysOn Setup Failing with Error! What should I do?

Posted by blakhani on September 26, 2011


While preparing for the Virtual TechDays Session, I received an email where AlwaysOn Setup was failing on Windows Server 2008 with below error message while attempting to use the create new availability group wizard in SQL Server Management Studio.

image

Here is the text for message for better search.
<Error>

The local node is not part of quorum and is therefore unable to process this operation. This may be due to one of the following reasons:

• The local node is not able to communicate with the WSFC cluster.

• No quorum set across the WSFC cluster.

</Error>

My research of pre-requisites revealed that we need below patch to be available on the operating to make it work.
1. KB 976097 ( Hotfix to add support for asymmetric storages to the Failover Cluster Management MMC snap-in for a failover cluster that is running Windows Server 2008 or Windows Server 2008 R2)

– Supports SQL Server Failover Cluster Instances with AlwaysOn Availability Groups

2. KB 2494036 (A hotfix is available to let you configure a cluster node that does not have quorum votes in Windows Server 2008 and in Windows Server 2008 R2)

– Supports configuring optimal quorum with non-automatic failover targets

This is already documented in http://msdn.microsoft.com/en-us/library/ff878487(SQL.110).aspx

See you at Virtual TechDays!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali | Tagged: , | Leave a Comment »

Did you Know – Size restriction in SQLExpress applies to only data file(s)?

Posted by blakhani on July 19, 2011


I was contacted by colleague of mine and he informed that he has seen 19 GB database size on SQL Express 2008 R2. He read that limit was 10 GB and asked is there any workaround to elevate the limit? 

So, as a follow-up to Guru’s post I did further research and found interesting information.

Size limitation mentioned in his post are actually limit for data file(s) only. Which means I can have a database for total size more than specified in his post’s table. Actually this makes sense to me because log file is for recovery purpose not to store user data.

Also, this limit does not include filestream data (this is tricky to track) because user can store many images, more than 10 GB and still stay within the limit.

Happy learning!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Uncategorized | Leave a Comment »

« Previous Entries
 
Follow

Get every new post delivered to your Inbox.

Join 341 other followers