Spatial Data Types

MSSQL 2008 introduced Geography and Geometry data types for storing and manipulating geodetic data.

SQL Server 2008 will be the first version of SQL Server to support spatial data and spatial operations natively. SQL Server 2008 introduces the geometry and the geography data types for storing spatial data.

Geometry data type is used to store flat map data like points, lines, and shapes. Geography data type takes account of the curvature of the Earth in addition. Both data types provide the ability to perform spatial operations on geography instances. Geography and Geometry are likely to be supported in MSSQL 2008 Express versions.

These spatial types enable users to perform SQL queries and operations on spatial data within SQL Server 2008 R2 directly. You can also index those columns that store spatial data types. 

Geography data type has functions which support parsing back from the known geo formats supported by the OGC (Open Geospatial Consortium) like text formats, binary representations, GML (Geography Markup Language - XML for geo data).

The geography data type uses coordinate system known as WGS 84 which is used by most GPS systems. The geometry and geography data types support the following seven instantiable spatial data objects: Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon and GeometryCollection.

Microsoft SQL Server 2008 will introduce approx 70 methods or functions to support operations with these two new data types. Some supported functions include STArea(), STIntersects(), STUnion() for polygons and STDistance() for points.

The benefits of these new spatial data types are:

* Spatial data types will allow to build location-enabled applications and services. So expect to see many interesting location-aware products in the near future using SQL Server spatial data types.

* Both spatial data types will benefit from the new spatial indexes providing high-performance queries.

* Extensibility through geospatial services such as Microsoft Virtual Earth.

Spatial datatype, methods and indexes will be supported equally on all SQL Server editions (Express, Workgroup, Standard and Enterprise) at no extra charge. That means anyone who wants to use the SQL Server 2008 Spatial can download the free Express version and start working with spatial databases. Express will still have its limitations as it does now but you have to believe that this puts a huge damper on middleware producers that are targeting .NET developers

What is cloud Computing

Cloud computing is computation, software, data access, and storage services that do not require end-user knowledge of the physical location and configuration of the system that delivers the services. 
Parallels to this concept can be drawn with the electricity grid where end-users consume power resources without any necessary understanding of the component devices in the grid required to provide the service. Cloud computing is a natural evolution of the widespread adoption of virtualization, service-oriented architecture, autonomic and utility computing. Details are abstracted from end-users, who no longer have need for expertise in, or control over, the technology infrastructure "in the cloud" that supports them. Cloud computing describes a new supplement, consumption, and delivery model for IT services based on Internet protocols, and it typically involves provisioning of dynamically scalable and often virtualized resources. It is a byproduct and consequence of the ease-of-access to remote computing sites provided by the Internet. This frequently takes the form of web-based tools or applications that users can access and use through a web browser as if it were a program installed locally on their own computer.

SQL Schema Comparison with Visual Studio 2010

There are many tools available for schema comparison. I am going to discuss the Microsoft Visual Studio Database edition for schema comparison. Microsoft Visual Studio Database edition offers several features for database development, for example you can create an offline representation of a database for database development and version control, Database Unit Testing, Code Analysis, Schema Comparison, Data Comparison etc.


Here I am going to show you how Schema Comparison works on Visual Studio 2010. Ultimate edition although you can do the same with Visual Studio 2005/2008 Database edition too.
Open the Microsoft Visual Studio IDE (Integrated Development Studio) and you will see a "Data" menu in the menu bar depending on the Microsoft Visual Studio edition you have installed. Select Schema Compare under Data menu and then New Schema Comparison as shown below.

You will see a dialog box like this, here you need to specify your source schema and target schema. The source or target could be either a database project (offline representation of your database) or the database itself (for which you need to set up a connection to your SQL Server instance) or a *.dbschema file. You can even save your selection as *.scmp file to use later on.

The moment you click on the OK button, it will start doing the comparison. You will notice a new tool bar, some of the options of this new tool bar are: you can filter out the comparison result as you can see in the image below, you can synchronize your target by writing updates to it (have a look on the note below before doing this), you can export your synchronization/incremental update script to a file or to the editor.

The schema comparison result screen will look like the image shown below. On the top pane there are four columns; Status tells the type of change of each object i.e.. if the object is New, Missing, Equal or has a different definition. The next column shows the name of the object at the source. Third column allows you to specify the kind of action which you want to take during synchronization for example if you want to Skip updating target, update target, drop from target if the object is missing in source, create on target if the object does not exist on the target. And finally the fourth column shows the name of the object at the target.
The next pane shows the object definition of the selected object. It marks the changes in different colors, the meaning of the color coding is available on the bottom of this pane as you can notice in the below screenshot.
The bottom pane shows the target schema update script depending on the selection of "Update Action" of each object. If you are not able to see it or want to refresh, click on "Refresh Update Script" icon on the toolbar. 

The Schema Comparison tool allows you to specify the different schema comparison options as well as specify which types of objects are to be compared as shown; make note here by default the Schema Comparison tool ignores extended properties, you can change this default behavior as well in the comparison options:

Note: As long as your source has new objects there is nothing to worried about, but if you have renamed your objects or columns at the source you might incur data loss while updating the target. For example in the above schema comparison result image you can see the EmployeeID column has been renamed to BusinessEntityID, so while updating the target it will drop the EmployeeID column and add the BusinessEntityID column which will have no data even though the EmployeeID column has data in it. So it is recommended to review your deployment scripts and take a backup of your target database before updating the target database. You can change this default behavior of data loss in the Schema Compare Options, as discussed above, and check the "Block schema updates if data loss might occur" under Script generation options and then the generated script will look like this if it would cause any data loss.

/*
The column [HumanResources].[Employee].[ContactID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[EmployeeID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[ManagerID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[Title] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[BusinessEntityID] on table [HumanResources].[Employee] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must add a default value to the column or mark it as allowing NULL values.
The column [HumanResources].[Employee].[JobTitle] on table [HumanResources].[Employee] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must add a default value to the column or mark it as allowing NULL values.
*/IF EXISTS (select top 1 1 from [HumanResources].[Employee]) RAISERROR ('Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO


Ref: http://www.mssqltips.com

When a SQL file will not open in SSMS

I am running SQL Server Management Studio 2005 on a Windows 7 machine here at work. When I double-click a .sql file in Explorer, SQL Server Management Studio (SSMS) would open, but would not load the file in question. It would also not bring up a logon screen to connect to an instance. I tried unassociating the .SQL extension and then re-associating it using "Open With" SQL Server Management Studio, but it never worked. I made suggested registry modifications; uninstalled and reinstalled SSMS, but nothing would resolve the issue. Then "duh"....I looked at the SSMS Tools menu. To my surprise, the "File Extension" window was empty!

Surprisingly, I haven't been able to find this solution ANYWHERE, even in Microsoft SQL Help Solutions. Microsoft, and almost every other site, refers to modifying the "HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\Command" to fix this issue. Even after uninstalling/installing SSMS, I was never able to find this Registry Key.

I hope this solution helps, and here is what I did.

1. Open SQL Server Management Studio
2. Select Tools > Options
3. Expand Text Editor > Select File Extension
4. Type "sql" in the "Extension" box
5. Select "Editor" Drop-Down and choose "SQL Query Editor"
6. Click "Apply" and "OK"
7. Repeat Steps 1-6 to allow "txt" files to open in SSMS (by Right-Clicking on Text File and selecting "Open With "SQL Server Management Studio"

Now close SSMS and double-click on a .sql file. Your script should now open in SSMS Query Window.

Ref:http://www.sqlservercentral.com

Calculate Percentile

Calculating Percentiles with SQL Server CTE
Here percentiles calculations are based on this fictitious table of employee salaries:

CREATE TABLE Employee
(EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
 GroupName VARCHAR(30),
 Emp_Name VARCHAR(80),
 Salary FLOAT)

INSERT INTO Employee(GroupName, Emp_Name, Salary) values('PRODUCTION', 'P1', 12000)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('PRODUCTION', 'P2', 13500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('PRODUCTION', 'P3', 13500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('PRODUCTION', 'P4', 11500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('SALES', 'S1', 12500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('SALES', 'S2', 15500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('SALES', 'S3', 14500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('SALES', 'S4', 12500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('SALES', 'S5', 11000)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('SALES', 'S6', 14000)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('MARKETING', 'M1', 11500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('MARKETING', 'M2', 11000)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('MARKETING', 'M3', 16500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('MARKETING', 'M4', 15500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('MARKETING', 'M5', 12250)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('MARKETING', 'M6', 11500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('ACCOUNTING', 'A1', 10500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('ACCOUNTING', 'A2', 13500)
INSERT INTO Employee(GroupName, Emp_Name, Salary) values('ACCOUNTING', 'A3', 15000)
GO

Here is the code to calculate the 75th percentile of employee salaries using the above table data:

DECLARE @Percentile FLOAT
SELECT @Percentile = .95;

WITH Emp_Sal(Salary, Prev_Rank, Curr_Rank, Next_Rank) AS
(
   SELECT Salary, 
         (ROW_NUMBER() OVER ( ORDER BY GroupName, Salary ) - 2.0) / ((SELECT COUNT(*) FROM Employee) - 1)  [Prev_Rank],
         (ROW_NUMBER() OVER ( ORDER BY GroupName, Salary ) - 1.0) / ((SELECT COUNT(*) FROM Employee) - 1)  [Curr_Rank],
         (ROW_NUMBER() OVER ( ORDER BY GroupName, Salary ) + 0.0) / ((SELECT COUNT(*) FROM Employee) - 1)  [Next_Rank]
   FROM Employee
)
SELECT 
   CASE 
   WHEN T1.Salary = T2.Salary THEN T1.Salary
   ELSE T1.Salary + (T2.Salary - T1.Salary) * ((@Percentile - T1.Curr_Rank) /  (T2.Curr_Rank - T1.Curr_Rank)) 
   END
FROM Emp_Sal T1, Emp_Sal T2
WHERE (T1.Curr_Rank = @Percentile OR (T1.Curr_Rank < @Percentile AND T1.Next_Rank > @Percentile))
  AND (T2.Curr_Rank = @Percentile OR (T2.Curr_Rank > @Percentile AND T2.Prev_Rank < @Percentile))

This query uses SQL Server CTE along with the new ROW_NUMBER() function. The Emp_Sal CTE at the top produces a table that ranks each row. The Prev_Rank and Next_Rank columns help locate a row for interpolation when the desired percentile does not fall onto one of the rows. 


Calculating Percentiles with Grouping

The query in the previous section evaluates only the overall percentile. If you want to calculate the 95th percentile for each Group than use the below query: It contains two CTEs:

DECLARE @Percentile FLOAT
SELECT @Percentile = .95;

WITH Group_Row_Count(GroupName, Row_Count) AS
(
   SELECT GroupName, COUNT(*)
   FROM Employee
   GROUP BY GroupName
   HAVING COUNT(*) > 1
),
Emp_Sal(GroupName, Salary, Prev_Rank, Curr_Rank, Next_Rank) AS
(
   SELECT E.GroupName, E.Salary, 
         (ROW_NUMBER() OVER ( PARTITION BY E.GroupName ORDER BY E.Salary) - 2.0) / (C.Row_Count - 1), 
         (ROW_NUMBER() OVER ( PARTITION BY E.GroupName ORDER BY E.Salary) - 1.0) / (C.Row_Count - 1), 
         (ROW_NUMBER() OVER ( PARTITION BY E.GroupName ORDER BY E.Salary) + 0.0) / (C.Row_Count - 1)
   FROM Employee E 
   JOIN Group_Row_Count C ON C.GroupName = E.GroupName
)
SELECT T1.GroupName, 
   CASE 
   WHEN T1.Salary = T2.Salary THEN T1.Salary
   ELSE T1.Salary + (T2.Salary - T1.Salary) * ((@Percentile - T1.Curr_Rank) / (T2.Curr_Rank - T1.Curr_Rank)) 
   END
FROM Emp_Sal T1 
JOIN Emp_Sal T2 ON T1.GroupName = T2.GroupName
WHERE (T1.Curr_Rank = @Percentile OR (T1.Curr_Rank < @Percentile AND T1.Next_Rank > @Percentile))
  AND (T2.Curr_Rank = @Percentile OR (T2.Curr_Rank > @Percentile AND T2.Prev_Rank < @Percentile))
Go

Result:

Calculate Running Totals Using SQL Server CROSS JOIN

Excel is generally used to make some type of calculations on given data. It could be done by using arithmetic or any other sort of formula available in Excel.
My problem was that I have an Excel file with three columns called ID, Date and Balance. 
ID is an Identity column and Balance is the current balance on a given Date. I had to calculate the sum of the last five transactions in an iterative way, so that the computed column will give me the Running Total for the last five transactions.
As far as Excel is concerned I have just written a formula as simple as sum(C1:C5) where C1 to C5 is the balance of the last five Transactions. For the next row the formula will be sum(C2:C6)… and this will continue for all rows.
Looking at the images below you will have a better idea.
Here is the raw data and the first calculation being entered in cell D5.



Here is the output with the formulas entered for all cells in column D.




Here the RunningTotal (column D) is the computed column giving the sum of the last five transactions on an Iterative level. The sum of Transaction ID’s 1 to 5 is 510, the sum of transaction ID’s 2 to 6 is 515, and so on.
But in SQL Server you might be aware it's very difficult to add such computed column which computes data on an iterative level. I had one last option of using Cursors or Loops, but as you all know it would degrade performance. So, I went for an approach that uses CROSS JOINS which was a better option.

---

First we will create a table name Accounts and insert some data into the table.  By using this script a table named Accounts will be created and 20 rows will be inserted.

CREATE TABLE Accounts ( ID int IDENTITY(1,1), TransactionDate datetime, Balance float ) GO insert into Accounts(TransactionDate,Balance) values ('1/1/2000',100) insert into Accounts(TransactionDate,Balance) values ('1/2/2000',101) insert into Accounts(TransactionDate,Balance) values ('1/3/2000',102) insert into Accounts(TransactionDate,Balance) values ('1/4/2000',103) insert into Accounts(TransactionDate,Balance) values ('1/5/2000',104) insert into Accounts(TransactionDate,Balance) values ('1/6/2000',105) insert into Accounts(TransactionDate,Balance) values ('1/7/2000',106) insert into Accounts(TransactionDate,Balance) values ('1/8/2000',107) insert into Accounts(TransactionDate,Balance) values ('1/9/2000',108) insert into Accounts(TransactionDate,Balance) values ('1/10/2000',109) insert into Accounts(TransactionDate,Balance) values ('1/11/2000',200) insert into Accounts(TransactionDate,Balance) values ('1/12/2000',201) insert into Accounts(TransactionDate,Balance) values ('1/13/2000',202) insert into Accounts(TransactionDate,Balance) values ('1/14/2000',203) insert into Accounts(TransactionDate,Balance) values ('1/15/2000',204) insert into Accounts(TransactionDate,Balance) values ('1/16/2000',205) insert into Accounts(TransactionDate,Balance) values ('1/17/2000',206) insert into Accounts(TransactionDate,Balance) values ('1/18/2000',207) insert into Accounts(TransactionDate,Balance) values ('1/19/2000',208) insert into Accounts(TransactionDate,Balance) values ('1/20/2000',209) GO

Here is what the raw data looks like.





To get the running balance after every five transactions I have used a CROSS JOIN query as shown below

SELECT A.ID AS ID, B.ID AS BID, B.Balance FROM Accounts A CROSS JOIN Accounts B WHERE B.ID BETWEEN A.ID-4 AND A.ID AND A.ID>4

In the result set below the ID column is the first grouping after we have got our first 5 rows and the BID column is the actual row that will be used for the balance. 
So for the first balance it would end on ID = 5 (since the IDs are number 1-20), the five rows that we would use for the balance are IDs (1,2,3,4,5).  For the next balance it would end with ID=6, so we would use records (2,3,4,5,6) and for ID=7 we would use records (3,4,5,6,7), etc....





The above query is the innermost query which will fetch the balance for every five Transaction ID’s from table B for a given ID of table A on an iterative level where alias names A and B are used, so we can use data from the same table.

SELECT ID, SUM(Balance) AS RunningTotal FROM (SELECT A.ID AS ID, B.ID AS BID, B.Balance FROM Accounts A CROSS JOIN Accounts B WHERE B.ID BETWEEN A.ID-4 AND A.ID AND A.ID>4 ) T GROUP BY ID

So here we can see that the output starts with ID = 5 and if we add up the values from ID=1-5 (100+101+102+103+104+105) we get 510.  Or if we look at ID=16 and up the values from ID=12-16 (201+202+203+204+205) we get 1015.





The above query would Group all the ID’s and get the sum of the five transactions on an iterative level
The following query is the final product.

SELECT Acc.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance, isnull(RunningTotal,'') AS RunningTotal FROM Accounts Acc LEFT OUTER JOIN (SELECT ID, SUM(Balance) AS RunningTotal FROM (SELECT A.ID AS ID, B.ID AS BID, B.Balance FROM Accounts A CROSS JOIN Accounts B WHERE B.ID BETWEEN A.ID-4 AND A.ID AND A.ID>4 ) T GROUP BY ID ) Bal ON Acc.ID=Bal.ID

The outer join will give all the details of the table. By executing the query above you will get the following output.  So you can see that the totals do not start until ID = 5 and from that point forward the RunningTotal is the sum of the balance for the current and previous four records.






This can be changed to do the sum after any level, by changing the fours to another number such as the following:

SELECT Acc.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance, isnull(RunningTotal,'') AS RunningTotal FROM Accounts Acc LEFT OUTER JOIN (SELECT ID, SUM(Balance) AS RunningTotal FROM (SELECT A.ID AS ID, B.ID AS BID, B.Balance FROM Accounts A CROSS JOIN Accounts B WHERE B.ID BETWEEN A.ID-2 AND A.ID AND A.ID>2 ) T GROUP BY ID ) Bal ON Acc.ID=Bal.ID

The one downside to this approach is that it is assumes there is a sequential ID value and there are no gaps in the IDs.  This could be changed to a ROW_NUMBER() function, so you are always working with a sequential number.
I hope this gives you some ideas of what other things you may be able to do with CROSS JOINs.

Customize SSMS query window for standard comment block and frequently used commands

You should create a standard comment format at the beginning of your T-SQL codes such as the below example.  You can automate adding a standard comment header when you open a new Query window.

--Sample Comment format
/*
*************************************************************************************
 [OBJECT NAME]: Stored Procedure Name
 [DESCRIPTION]: SP Description
 Copyright by Company Name
 [NOTES]: 
 
 [PARAMETER BREAKDOWN]:
  @parameter name - Parameter description
  @parameter name - Parameter description
 [SAMPLE CALL]:
 
 exec spName @parameter1 = 'MSSQLTips', 
 @parameter2 = 'Jugal', 
 
 [SQL Version Support]: SQL Server 2000, 2005, 2008
 
 [MODIFICATION HISTORY]:
 Date               Author            Comment
 ------------------ -----------------  -----------------------------------------
 26 May 2011 Arun Ladha    Inception
 26 May 2011 Arun Ladha   add dbmail feature
*************************************************************************************
*/

If you don’t want a standard comment block another option it so include frequently used T-SQL statements such as the below commands.

--Sample T-SQL Code
select @@servername
select @@version
select * from sys.sysprocesses where blocked <> 0
sp_who2 active
sp_heldb
xp_fixeddrives

So if I use the second example, when I open a new query window in SSMS I get these commands every time as shown below.




To automate the comment/T-SQL in all new query windows, you have to modify the SQLFile.sql file which is located in:

• On 32-bit machine "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql" folder.
• On 64-bit machine "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql" folder.

 You could also do a search for SQLFile.sql as well.  The below image shows the file.

Once you find the file, you can edit the SQLFile.sql with any text editor (i.e. notepad) and add your own standard comment format or T-SQL code and save the file. Then whenever you open a new Query window you will see the comments/T-SQL that you entered into the SQLFile.sql file.
Note that you must have the proper permission to edit SQLFile.sql.  I used Notepad and used the "Run as administrator" option to edit and save the file.  This just depends on the security that was setup on your desktop.  Also, since this file is saved locally each person that wants this has to do this on thier machine.

Ref: http://www.mssqltips.com

Mirroring States

The mirrored database is always in a specific mirroring state  during a session. The DB State reflects the communication status, the difference in data between the partners and data flow. 

The Server instance monitor each other during the mirroring session. The principal and the mirror database uses the same mirroring state except the Pending_Failover mirroring state. 

These are the possible mirroring states of the database:

0 = Suspensed

1 = Disconnected from other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not possible now.

6 = The partners are synchronized. Failover is potentially possible. 

NULL = Database is inaccessible or is not mirrored.

Mirroring state	Description
Suspensed	The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover. A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session. SUSPENDED is a persistent state that survives partner shutdowns and startups.
Disconnected	The partner has lost communication with the other partner.
Synchronizing	The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
Pending Failover	This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
Synchronized	When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database. If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover. If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.

Ref: MSDN 

Dismantling a SQL Server Cluster After a P2V (Physical to Virtual) Migration

There is a way to reconfigure a SQL Server cluster after a P2V (Physical to Virtual) without having to reinstall SQL. I want to warn you though that the method probably wouldn't be sanctioned by the Microsoft powers and I can only recommended it to those who are comfortable with living on the edge. I will say that I've performed this process on over a dozen production clusters. They have been running without error for over 6 months. The process really was one of necessity based on having an exceedingly aggressive P2V schedule. Are there risks - certainly, but then again isn't this why we became DBA's?

First Steps

In a large organization you'll need to work closely with the team responsible for converting the server to a virtual server. If you are the DBA and you happen to also be the one responsible for the conversion then these steps still apply and you're lucky enough to not have to schedule as many meetings.

Assuming we are only dealing with a 2-node cluster your configuration consists of 2 physical node names and 2 virtual names - one for SQL and one for the server. The only name you'll want to retain post P2V is the SQL Network Name. This is the name your application(s) should be connecting to and is the same as the default instance. You could choose a different name, but you'll be causing yourself much more trouble than it's worth.

In the image below the SQL Network Name is called STLPDBCCMSCLUS and it contains nodes STLPDBCCMSP01 and STLPDBCCMSP02. Both of the physical node names will be removed after the P2V and the only name you'll use is the original SQL Network Name.

Finally, you'll need to ensure the same drive letters are used. For example, if the cluster has LUNs F and G than the new VM should also have LUNs F and G. Again, you could change this, but it wouldn't be worth the effort. What will be removed is the quorum drive, usually Q. This will cause you problems with MSDTC, but we'll discuss this later in the tip.

Now for the Tricky Part

Everything you do during this tip will need to be completed during the P2V downtime. You or another team will shutdown production and begin the conversion. Once the conversion is completed SQL will try to start but fail. This is where you come in to save the day.

The first order of business is to remove all the unused IP addresses. If you remember the cluster had 4 server names (2-nodes, SQL name, server name). It now only has one. This means there is only one IP address being used, but SQL still thinks there are many. If you had looked in the Network settings on the cluster you would have seen something similar to this:

This screenshot shows 3 out of the 4 entries. There is also a loopback entry of 127.0.0.1. These TCP\IP entries simply refer to values in the registry and this is where we will make the changes. For SQL 2005 you should be able to find them at HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib. Determine the IP address of the new server and then remove all of the key entries except for the loopback entry, the server IP, and the IPAll. I make IP1 the IP address of the server and IP2 the loopback IP.

You will also see a key called "Cluster" under HKLM\Software\Microsoft\ Microsoft SQL Server\MSSQL.1\. The entire key can be deleted.

Only one more thing needs to be done before starting SQL Server. If your cluster was correctly configured than MSDTC would have a dependency on SQL Server. This is because you want MSDTC to have started prior to SQL Server starting. The problem now is the MSDTC log file is normally (but not always) stored on the cluster quorum drive. You'll need to change this in order for the MSDTC service to start.

Begin by searching through the registry for "MSDtc". You should find an entry under HKCR\CID\"some large CLSID"\CustomProperties\Log\Path. You'll want to change the path from Q:\ to another location.

All that is left now is to cross your fingers and start both the Distributed Transaction Service and the SQL Server Service. Viola'!

Ref:http://www.mssqltips.com

Get Detailed Table and Column Information in SQL

There is often some basic information that you want to see regarding the tables and columns in your current database.

Select t.TABLE_NAME As [Table], 

c.COLUMN_NAME As [Column],

c.DATA_TYPE As [DataType],
c.CHARACTER_MAXIMUM_LENGTH As [Length],
Case
When c.IS_NULLABLE = 'Yes'
Then 1
Else 0
End As [Nullable],
Left(k.CONSTRAINT_NAME, 2) As [KeyType],
d.Description As [MS_Description]
From INFORMATION_SCHEMA.TABLES t
Inner Join INFORMATION_SCHEMA.COLUMNS c
On t.TABLE_NAME = c.TABLE_NAME
Left Outer Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
On t.TABLE_NAME = k.TABLE_NAME
And c.COLUMN_NAME = k.COLUMN_NAME
Left Outer Join (Select o.Name As [Table],
c.name As [Column],
ep.value As [Description]
From sys.objects o
Inner Join sys.extended_properties ep
On o.object_id = ep.major_id
LEFT Join syscolumns c
On ep.minor_id = c.colid
And ep.major_id = c.id
Where o.type = 'U') As d
On t.TABLE_NAME = d.[Table]
And c.COLUMN_NAME = d.[Column]
Where t.TABLE_TYPE = 'BASE TABLE'
And t.TABLE_NAME
Not In ('sysdiagrams')
Order By t.TABLE_NAME, c.ORDINAL_POSITION

I hope this saves everyone else as much time as it saves me! It also includes the MS_Description schema property in case you document your databases in hopes that .NET will support a self-documenting database architecture some day.

Create a Stored Procedure Available to All Databases

I needed to create a stored procedure today that could be used on any database within a given SQL Server instance. After some digging I found that there are three requirements for such a stored procedure:

1. The stored procedure must be created in the master database.
2. The name of the stored procedure must start with “sp_“.
3. The stored procedure must be marked as a system object.

The first two are quite easily accomplished but the third requirement requires the use of an undocumented stored procedure named sys.sp_MS_marksystemobject which will mark the created stored procedure as a system object.

01	-- 1. Create the procedure in the master database

02	USE master

03

GO

04

05	-- 2. Create the procedure with the prefix sp_

06	CREATE PROCEDURE sp_[Stored_Procedure_Name]

07

AS

08

BEGIN

09	-- Insert the logic of your stored procedure here

10

END

11

GO

12

13	-- 3. Mark the stored procedure as a system object

14	EXEC sys.sp_MS_marksystemobject sp_[Stored_Procedure_Name]

After you complete the above three steps, you can run the stored procedure on any of the databases found in that instance of SQL Server.

Note: If you need to “unmark” the procedure as a system object just simply drop the procedure and recreate it.