Information Technologies: SQL Server

Showing posts with label SQL Server. Show all posts

Saturday, August 18, 2012

SQL Server Splash Screen Timeline

When SQL Server 2005 was released in 2005, Microsoft replaced SQL Server Enterprise Manager with Management Studio and its splash screen. Not a big deal and in fact some people do not like the splash screen because it slows down the launch of Management Studio a bit. To me it serves as a confirmation that I have double clicked the Management Studio properly and it is in the process of launching Management Studio.

I like to look at the design of splash screen (of course when nothing bad is happening). As I was installing SQL Server 2012 recently, I decided to do up a splash screen timeline for SQL Server. Here you go.

Pin It

SQL Server 2012–improved validation on setup account privileges

I was installing few days ago and I have forgotten to grant my account the “Debug programs” and “Generate security audits” rights on the server. All our servers have been harden so even the local administrator group does not have the two rights.

I went ahead to start the installation and it failed at the “Setup Support Rules” step.

Pin It

SQL Server Management Studio Cannot Connect

A developer complained to me that he was not able to connect to a SQL Server 2005 server using SQL Server Management Studio.

The screen shot below is the error he was getting when he tried to connect.

Pin It

Non-SysAdmins have been denied permission to run CmdExec job steps

Someone has asked me to schedule a SQL job on a SQL 2000 Server. The job simple calls an executable program. She has also requested to be able to monitor the job using the “Job Activity Monitor” in the SQL Server 2008 Management Studio installed on her machine. She is using a SQL account which has limited access to the SQL 2000 Server (the account is DBO of a couple of databases but not SysAdmin).

Now according to How to: Open Activity Monitor (SQL Server Management Studio), the SQL account she is using must have SELECT permission to the sysprocesses and syslocks tables in the master database in order for her to use the “Job Activity Monitor” to monitor the job. Permission to view these tables is granted by default to the public database role. So I grant the SQL account the public database role but she can’t see any job in the “Job Activity Monitor”.

Pin It

Licensing Impact On Virtual Machines With SQL 2012

In my earlier post Microsoft SQL Server Per Processor License For VM, I gave a simple example on licensing SQL Server 2008 in virtual environment. However, this is going to change with SQL Server 2012. With SQL Server 2012, the licensing will change from processor based to core based. If you are having Enterprise Edition deployed on a physical server with 2 X 10 cores, you will need to buy 20 cores license!

In the virtual environment, if the physical server has 2 X 10 cores, you have a few options for core based licensing.

License based on the number of virtual core assigned to a virtual machine. If you assign 8 virtual cores to a virtual machine, you will buy 8 cores license.
License all the cores of the physical server which will be 20 cores license. If this is done with SA (Software Assurance), you can have unlimited SQL Server 2012 installed. Without SA, it will be 1 SQL Server 2012 per core license which will be 20 X SQL Server 2012 installation for this case.

There other changes to the SQL Server 2012 licensing model and you can visit http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx to read more about it.

Pin It

Monday, January 09, 2012

SQL Executive Tasks Not Running

Yes, it is SQL Server 6.5 which is really antique. The scheduled tasks in the SQL Executive (now known as jobs and SQL Server Agent) refused to run. Turning on error logging on the SQL Executive which generates the SQLEXEC.OUT file provided useful information to solve the problem. The following error was logged inside SQLEXEC.OUT.

1/5/2012 10:38:44 AM - + DBLib Message Handler: Login failed- User: deInfoTech\sqlsvr Reason: Not defined as a valid user of a trusted SQL Server connection.
1/5/2012 10:38:44 AM - ! DBLib Error Handler: Login incorrect.

I verified that deInfoTech\sqlsvr is a member of the local Administrators group of the server so it should have admin rights.

Then I tried to logon to SQL Security Manager, I received the following error.

This SQL Server does not support Windows NT SQL Server security stored procedures.

A quick search lead me to INF: How to Troubleshoot SQL Server 6.5 SQL Security Manager Messages. For some reasons, the Full Control rights for the local Administrators group was removed from the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer key.

I re-granted the rights and restarted the SQLExecutive service. The SQL Executive is back to normal now.

Pin It

Friday, February 18, 2011

Microsoft SQL Server Per Processor License For VM

Microsoft SQL Server Per Processor Licensing is pretty straightforward when dealing with physical server. Basically the number of processor licenses needed is base on the number of processor socket you have regardless of the number of cores on each processor .

When it comes to VM (virtual machine), it is a little different. Take for example, I have 2 six-cores processor without hyper-threading turn on. In the virtual operating system environment, I will see 12 processors. If I have a VM running SQL Server and plan to use per processor license. I assign 6 vCPUs (virtual CPU) to that VM. How many processors license do I need to buy?

The formula below answer the question.

# of virtual processors supporting the VM

# of cores (hyperthreading off) or threads (if hyperthreading on) per physical processor

# of Processor licenses required

If I use the formula above, I will only need 1 processor license.

6 ÷ 6 = 1

If hyper-threading is turn on, the virtual operating system environment will show 24 processors. If I still assign 6 vCPUs (virtual CPU) to that VM and use the above formula, this is what I will get.

6 ÷ 24 = 0.25

I will need to round up to the next nearest whole number which is 1. So I still need 1 processors license.

Assuming that I am using VMware vSphere Enterprise Plus Edition for my virtualization, I can only assign 8 vCPUs to the VM and I still need to pay for 1 processor license.

Source: Microsoft Server Licensing Guide

Pin It

Tuesday, February 01, 2011

Installing SQL Server Code-Named "Denali"

This is just a really simple installation preview of the new version of SQL Server code-named Denali. You can download the CTP1 copy from http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx.

The first screen you see after you ran setup.exe.

I ran the “System Configuration Checker” and it seems good.

Next, clicked on the “Installation” on the left. Then clicked on the “New SQL Server stand-alone installation or add features to an existing installation”.

It did another round of check. It looked good to go.

I selected “Enterprise Evaluation” and clicked Next.

Accepted the term and clicked Next.

Clicked the Install button to start installing the support files.

After the support files installation completed, it did another round of check.

Selected “SQL Server Feature Installation” for the role and clicked Next.

I selected all the features just for trying out. Clicked the Next button to continue.

Another round of validation. Clicked the Next button to continue.

I choose to install as default instance and set the root directory. Clicked the Next button to continue.

Reviewed the disk space requirements. Clicked the Next button to continue.

Specified the service account for each service. Clicked the Next button to continue.

Selected “Mixed Mode” for the authentication mode. Use the “Add Current User” button to add my account as an administrator of the SQL Server. Clicked the Next button to continue.