Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for June, 2011

Help : How can I move Storage in Windows Cluster 2008

Posted by blakhani on June 11, 2011


While working on a case few days ago, I faced this question by customer and i found it interesting to blog about. (Today I found this in forum so finalizing and posting it). Here is the question.

I have created two node windows cluster. All disk are available in “storage” but I don’t see any option to do manual failover unless they are part of some group. How can I test if failover is working fine for disks without shutting down the node or adding them to a group.

Here is the screenshot to explain the problem better. As you can see that U and W are not part of any group defined under “Services and Applications”. So by default they are the part of “Available Storage” group.

Storage

And indeed there is no option in UI to do failover of those disks.

No Option

I spend sometime with windows team and I was able to find the answer. so here it goes.
This can only be done by command line. We need to use cluster.exe command to achieve this.

Cluster Group “Available Storage” /Move:NodeName

In above command NodeName is optional. If you have two nodes, it would move storage automatically to other node.

Move Using Command

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Uncategorized | 7 Comments »

Help : Who dropped objects from database?

Posted by blakhani on June 8, 2011


Sometime I have been asked that how to find out who dropped/altered/created table/stored procedure. Well, SQL 2005 has out-of-the-box solution to this problem. This works in SQL 2008 as well.

Schema Change History is the report which would give details. This report takes data from default trace which is enabled by default in SQL Server 2005 default installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure ‘show advanced option’, ‘1’;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ‘default trace’;
GO

If its enabled you would see run_value as 1.

More about default trace can be read here

Schema Change History

If you want to see who dropped a database, you can right click on Server Icon and go to Reports > Standard Reports > Schema Changed History. This Reports output would changed based on where you right clicked it (on server icon or database name)

Since default trace will be maximum 100 MB in size you may not get the data which is pretty old. Every restart also creates new file. So, if your SQL Server has restarted five times after someone dropped the object, you will not be able to see it here.

SQL Server 2005 has many other “Standard Reports” which can be found in SQL Server Management Studio.

Enjoy!!!

<Tags> Who dropped my database, when database was dropped, someone deleted database objects, tables, procedures, list of dropped objects in SQL Server 2005, Schema Change history Report, see who dropped databases, how to find who dropped the database in SQL server 2005 objects dropped</Tags>

Cheers!
Balmukund

Posted in Uncategorized | 16 Comments »

Help : Can I Upgrade SQL Edition without reinstall?

Posted by blakhani on June 3, 2011


And answer is “it depends”. Well, I have seen posts in forum asking steps to upgrade SQL Standard to SQL Enterprise OR SQL Developer to SQL Standard etc. So I am writing this blog to bring some clarity in steps to be performed.
First of all, refer below matrix to know if in place edition upgrade is possible or not.

http://msdn.microsoft.com/en-us/library/ms143393.aspx (SQL Server 2008 R2)
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.100).aspx (SQL Server 2008)
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.90).aspx (SQL Server 2005)

Above links are for version upgrade as well as edition upgrade. Now, if matrix says you can upgrade from edition X to edition Y then steps are pretty simple.
For SQL 2008 and SQL 2008 R2:

  • Run setup.exe from media.
    • If you have SQL already installed then you can also launch “Installation Center” by going to Start->Programs ->Microsoft SQL Server 2008[R2]->Configuration Tools-> SQL Server Installation Center[(64-bit)].
  • You would get below screen titled “SQL Server Installation Center”
  • Go to “Maintenance” and click on “Edition Upgrade”

  • Go through the Wizard and choose the Instance you want to upgrade.

It would hardly take few minutes to upgrade and I have done with many customer.

One question which was asked many times is where the hell is the key which i should enter. If you have media, look for DefaultSetup.ini file and you would find PID there.

For SQL 2005:
I would update this post once I take screenshot from SQL 2005 setup.

Cheers,
Balmukund Lakhani

Posted in Edition Upgrade, SQL Server, Upgrade | 5 Comments »