Creating a simple stored procedure

Overview
As mentioned in the tutorial overview a stored procedure is nothing more than stored SQL code that you would like to use over and over again.  In this example we will look at creating a simple stored procedure.

Explanation
Before you create a stored procedure you need to know what your end result is, whether you are selecting data, inserting data, etc..
In this simple example we will just select all data from the Person.Address table that is stored in the AdventureWorks database.

So the simple T-SQL code would be as follows which will return all rows from this table.

SELECT * FROM AdventureWorks.Person.Address

To create a stored procedure to do this the code would look like this:

CREATE PROCEDURE uspGetAddress AS SELECT * FROM AdventureWorks.Person.Address GO

To call the procedure to return the contents from the table specified, the code would be:

EXEC uspGetAddress --or just simply uspGetAddress

---

When creating a stored procedure you can either use CREATE PROCEDURE or CREATE PROC.  After the stored procedure name you need to use the keyword "AS" and then the rest is just the regular SQL code that you would normally execute.
On thing to note is that you cannot use the keyword "GO" in the stored procedure.  Once the SQL Server compiler sees "GO" it assumes it is the end of the batch.
Also, you can not change database context within the stored procedure such as using "USE dbName" the reason for this is because this would be a separate batch and a stored procedure is a collection of only one batch of statements.

Different options for creating SQL Server stored procedures

There are various options that can be used to create stored procedures.  In these next few topics we will discuss creating a simple stored procedure to more advanced options that can be used when creating stored procedures.

Some of the topics we will cover include:

Creating a simple stored procedure

Using input parameters

Using output parameters

Using Try Catch

SQL Server Store Procedure (Introduction)

A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

SQL Tutorial: Query Processor

The query processor performs two basic functions

• Query optimization
• Query execution

Query optimization is one of the most complex tasks that Microsoft SQL Server does. The below definition and most of this information in this article has been minded from Kalen Delany's book.

The query processor is also called the relational engine, it determines exactly what your query needs to do and the best way to do it.

To perform the 2 basic tasks of query optimization and query processor has 3 components

• Command Parser: The command parser performs syntax check on the query being worked on and raises a syntax error if it doesn't recognize the syntax. It also translates the commands into an internal format called the query tree.
• Query Optimizer: The query optimizer works on the query tree produced by the command parser and prepares it for execution.Only DML commands (Select,Update,delete) are passed through as they can be processed in many different ways.The query optimizer compiles the commands, optimizes the parts which are optimizable, checks for security and creates an execution plan.
• Query Executor: The query executor works on the execution plan given by the query optimizer.It steps through command by command and interacts with the storage engine for data manipulation, locking and managing transactions.

The query optimizer is "smart" in the sense that it users heuristics (common sense) to make sure that finding all possible execution plans and then deciding which one is better, should not take more than just picking up any random plan and executing it.

Microsoft SQL Server storage engine

The SQL Server storage engine manages all data access, both through transactions based commands and bulk operations such as backup, bulk insert and certain DBCC commands. It contains the components needed to actually access and modify data on disk.

In SQL Server 2008, storage engine contains three main components:

1. Access methods: They are used to locate data. When a page needs to be retrieved, access methods makes a request to the buffer manager, which brings them in.
2. Locking & transaction services: All SQL Server transactions must adhere to the ACID properties. To achieve this, the locking and transaction services come into play.
3. Utility Commands: This contains components for controlling utilities like bulk-load, DBCC commands, backup-restore operations etc.

The SQL Server storage engine(2008) needs 3 system catalogs to function:

• sys.sysallcounts
• sys.sysrowsets
• sys,sysrcols

These catalogs contain all the metadata that the storage engine needs to find its way around the table and index structure.

To put it in simple terms, think of SQL Server storage as a courier company.It gets the data from relational engine, packages it properly and sends it over to the client.It also  receives requests from the client, converts then into OLE DB rowsets and feeds them into the relational engine.In doing all of these operations, it interacts with many other components of the SQL Server engine, like the buffer manager, transaction services etc.

SQL Server architecture

Like all database systems, the SQL Server performs extremely complex tasks like

1. Memory management.
2. Optimum CPU utilization.
3. Query optimization.
4. Execution planning, etc.

at a lightning fast speed.This article deals  with what takes over when you press that F5 key on you keyboard.

The diagram above gives an overview of what the SQL Server architecture looks like. There are 3 important items here.

1. Database Engine: Performs the storage and query processing tasks.
2. SQL Operating System: Woks on I/O and other higher level management tasks.
3. Interfaces: The External Protocols and SQL Operating System API connect the dots.

The database engine is the heart of MSSQL Server. Think of it as the engine of a motorcycle, it is the part where the real stuff happens. It contains 2 major components.

1. Query Processor: Parses optimizes and executes the query.
2. Storage Engine: Manages buffer and access methods.

Going with motorcycle analogy, the SQL Operating System API and External protocols are the wires and dials. They connect the speedometer to the front wheel, show you the speed. This speed can then be reduced or increased using the accelerator. They simply provide ways to connect internal and external components together and manipulate the operations.

The SQL OS is everything else, it is the frame, the seat, the tires. It is the structure that gets everything else going.You can have an engine and some wires, but unless you have the frame they will never mate.

SQL Server Basic Concepts

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in the relational database. 

It is a standard language for Relational Database System.

All RDMS systems like MySQL,MS Access, Oracle, Sybase and SQL Server use SQL as standard database language.

They are using different dialects, such as

• MS SQL Server using TSQL 
• Oracle using PL/SQL 
• MS Access version of SQL is called JET SQL (native format) etc. 

Why SQL?

• Allow users to access data in RDMS systems. 
• Allows users to describe data. 
• Allows users to define the data in database and manipulate that data. 
• Allows to embed within other languages using SQL modules, libraries & pre-compilers. 
• Allows users to create and drop databases and tables. 
• Allows users to create view, store procedure, functions in a database. 
• Allows users to set permission on tables, procedures and views. 

History

• 1970 - Dr. Edgar.F.”Ted” Codd of IBM is known as father of relational databases. He described a relational model for databases. 
• 1974 - SQL appeared. 
• 1978 - IBM worked to develop Codd’s ideas and released a product named System/R. 
• 1986 - IBM developed the first prototype of relational database and standardized by ANSI. The 1st relational database was released by Relational Software and its later becoming Oracle. 

What is a database? 

Think of it this way, when the humans evolved from fishes and chipmunks, they started gathering objects. Stones, wood, precious metals, whatever they could put their hands on. This obsession with collecting objects continued in the virtual world too, it was rather a necessity. As the information systems spiraled out of control, keeping this information in an orderly fashion with easy retrieval became more and more complex. This is where the database systems came to the rescue!

A structured set of data held in a computer, especially one that is accessible in various ways.

What you see above is the simplest definition of a database. There are 2 main things to note here:
The data is structured
It is accessible in various ways

These are two of the most important requirements for a modern databases system, apart from security. The data should be stored in such a way that is easy to understand and easy to retrieve.

The relational model has to be by far the most popular database model in use today. All you need to know about it is that it stores the information in tables made up of rows and columns and the tables are connected to each other via keys.

Microsoft SQL Server

Microsoft SQL Server is one of the most widely used database systems today.

Microsoft SQL Server is a relational model database server produced by Microsoft.

Microsoft SQL Server Editions

MS SQL Server has different editions based on different end-user needs. You can find the edition of SQL Server running on your system by executing the following command.

select SERVERPROPERTY ('edition')

The above query will give one of the following results:

1. Enterprise: Full blown.
2. Evaluation: Full blown but only for evaluation.
3. Developer: Full blown but can't be deployed on production system.
4. Standard: Lesser high availability and performance feature than Enterprise.
5. Express: The CUI based free edition of SQL Server.

Microsoft SQL Server: Other Features

Microsoft SQL Server not only provides basic database services, it also helps in managing complex tasks and creating automated reports using separate tools. In SQL Server 2012 there is a separate version as well called Business Intelligence.

Some of the tools that add on to the basic functionality of SQL Server  are:

1. SSIS: SQL Server Integration Services lets you create packages that encapsulate complex tasks. These packages can then be deployed on any system.
2. SSRS: SQL Server Reporting Services helps you to create clean, automated reports.
3. SSAS: SQL Server Analysis Services is used to analyse massive amounts of data to make informed business decisions.

How to install a clustered SQL Server 2012 Instance – step-by-step – Part 3

In continuation of our series on how to install a SQL Server 2012 clustered instance, let’s discuss how to add a node into an existing SQL Server clustered instance. The following steps are performed either to add one more node to some already installed clustered instance, or to continue the installation of a brand new clustered instance — It all boils down to the same thing. To perform this phase, you will need to have at least one node installed. In this case, we installed a new SQL Server failover instance in the Part 2 of this series.

So connect to the next node, in this case W2012SRV04, and perform the following steps:

1. Make sure that you have the same SQL Server 2012 media used to install in the other node available and execute the “Setup” binary.

2. The “SQL Server Installation Center” will be opened.

3. Still on “SQL Server Installation Center”, click “Installation” and select “Add node to a SQL Server failover cluster”.

4. A check will run in order to verify the setup support rules. Click “OK”.

5. Now the setup will check and install the latest updates. Keep the “Include SQL Server product updates” checked and click “Next”.

6. Another check will run in order to identify problems within the installation process. Click “Next”.

7. Insert the product key and click “Next”.

8. Accept the terms and click “Next”.

9.  On this step you need to pick the instance where this installation will be related. Notice that you have a list of installed instances, as well as the nodes that the instances are already installed.
In our case, the Instance Name “DB” is installed in only one node, and we need to choose this instance in the list box in the top to proceed to the node addition.

10. Now. confirm the IP settings as you did in the first node installation. Click “Next”.

11. Fill the passwords for the Engine and Agent service account, and click “Next”.

12. Like in the other (first) node, you have the option to send error reports to Microsoft. Click on “Next”.

13. Now the setup will verify if the installation process can be blocked. In the end, click “Next”.

14. Review the options and click on “Install”.

15 Now you can watch the installation progress. Click “Next” when it is done.

16. Now the node addition is complete! Just verify if all of the features have succeeded. Click “Close” and you are done!

At the end of this installation, you will have one more node available to run out our instance. This means that we can now perform a failover from W2012SRV03 to W2012SRV04, so we have now a high availability (HA) solution. In case of a hardware failure on the active node, we will have a failover action.

For some reason, such as a test or for maintenance purposes, you can do a failover manually. To perform this, open the Failover Cluster Manager tool (the same used on step 2), right-click over the role name (in our case “SQL Server (DB)” and then select the “Move” option. Two options will be shown — the first “Best Possible Node” and the second one “Select Node…” Both are valid, but the second gives you the chance to choose the node to move the Role, which is useful when you have more than two nodes.

How to install a clustered SQL Server 2012 Instance – step-by-step – Part 2

The objective of this second article is show how to install the first node of a clustered SQL Server 2012 instance and how to basically manage it from the Failover Cluster Manager tool, on Windows Server 2012 R2.

Just to refresh our memory, this is our infrastructure.

The installation: Starting from the very first node

So let's start the installation, if you remember, as mentioned that we have two steps to complete the installation on both nodes. For now we will start doing the new instance in one of the nodes .

This node will be W2012SRV03

1. With the SQL Server installation binaries available, click
   
2. The SQL Server Installation Center will open. Click “Installation” in the left menu, then select “New SQL Server Failover Cluster Installation”:
3. A check will run in order to find possible constraints when installing the SQL Server Setup support files. At the end of the check, click “OK”:
   
4. Now the installation will check for available updates, I recommend that you include those updates into the installation. Click “Next”:
   
5. At this step, the setup support files will be extracted and installed, click “Install”:
   
6. Finally, we have all the setup files installed. Another check will run in order to validate if problems might occur when SQL Server files is installed.
   
   

   Best Practice:

   It’s recommended to have a clustered MS DTC resource, as well as a dedicated MSDTC resource dedicated to each SQL Server group.
   Here is a link to a good resource about this theme:http://blogs.msdn.com/b/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx

7. At this step, just insert the product key and click “Next”:
   
8. Select “I accept the license terms” and click “Next”:
   
9. To install a SQL Server clustered instance, choose the first option “SQL Server Feature Installation”. Click “Next”.
   
10. Here we have few options to install on “Instance Features” and “Shared Features” sections.
    For “Instance Features” pick the “Database Engine Services” item.
    For “Shared Features” pick “Management Studio (complete)”.Another important point here is the directory that the shared tools will be installed. A good practice is have a dedicated local disk to install SQL Server related files. In the image I’m using the drive C, the same that I have my OS. This is my lab, so I advise that this is not a good practice for a productive system. Click “Next”.
    
11. At this step another check will run, this time to identify problems that might block the setup, based on our choices of the last step. Just wait for its completion and click “Next”.
    
12. This is an important step: Here we will define the instance network name (one of our prerequisites), whether we will use a default or name instance, the Instance ID and the instance root directory.A few things to consider here:
    - On “Detected SQL Server instances and Features on this computer” section, we already have one instance installed. So I had to use a named instance and change the Instance ID to do not conflict with the existing one.
    - Based on the information above, to connect to our instance we will need to use: SQL04\DB.
    - Another important point is the “Instance root directory”: It’s recommended to use a dedicated local disk to install the SQL Server binaries. Avoid using the system drive, a.k.a. “drive C”.Fill and verify all the points an click “Next”:
    
13. This step confirms if the disk space requirements are being met. Click “Next”:
    
14. Now this is related to the Cluster Resource Group name to be used. The installation suggests a name, but you can change it.
    This window also shows the reserved and already used Resource Group names. Change the Resource Group name if you are not satisfied with the suggestion and click “Next”:
    
15. Now the disks! Another piece of the prerequisites has been shown. At this step, we will have the information of all available storage to be used on our new clustered instance.
    As mentioned before, I have only on available disk for this guide, so let’s use this. Just select the desirable disks and click “Next”:
    
16. Do you remember the requisite of an IP? It’s time to use it! Just pick the network that you will use, unmark the DHCP column and fill the address column with the value of the IP. And… “Next”:
    
17. Here is our last requisite: Service Accounts. Fill the information about the SQL Server Engine and Agent service account (login and password).
    Note: For a clustered instance, the “Startup Type” for the services should be as “Manual”. The Cluster Service will manage this for us.
    Click “Next”.
    
18. At the same step, we have another tab where we can define the collation to be used in our database engine. By default it has “Latin1_General_CI_AS”.
    For more information about collations, click here. This can be a very important choice! If you have an special collation requirement, don’t forget to select the right one during the installation, otherwise you will have a hard work ahead to change this.
    
19. Now we need to choose the authentication mode of our instance.The options are either “Windows Authentication”, which will take benefit from domain and local server logins, or “Mixed Mode”, which accepts Domain/Windows logins as well as logins created and managed by SQL Server.If you pick “Mixed Mode” a login called “sa”, member of the “Sysadmin” role will be enabled. For this reason we need to specify the password for this login.In the box bellow, we need to add all domain/Windows users that have access to the instance and will be part of the “Sysadmin” fixed server role. You need to use the three buttons to add/remove logins from this list:
    
20. Now it’s time to define the disk strategy.This step worth an entire article — I’m using only one disk for demonstration purposes, but the recommendation is to use one isolated disk for each one of the points.
    The general rule here is: The most spread, the better!
    
    
    
    We could use a layout like the following:
    
    
    The only thing that we cannot specify directly is a place to store the non-clustered indexes. This separation of the non-clustered and clustered index is a case aside.
    
    Points to take attention here:
    Isolate the TempDB in a fast disk. Remember: From SQL Server 2012 we can store the TempDB in a local disk on clustered installations!
    Place your data files in one disk and log files in another one.
    Request the disks with an appropriate RAID level.
    Pay attention to the partition offset and block size before the installation, even if you are using a Windows 2008+ OS.
    
    After consider all those points, set carefully the disks for each point and click on “Next”.
    
21. Here you have an option to send error reports or not. Click “Next”:
    
22. Another check will run to verify if the failover cluster installation will be blocked. After the check, click “Next”:
    
23. Here we will be able to review all chosen options. Review and click “Install”:
    
24. The installation will begin — wait for completion.
25. In the end you will have a confirmation about the success or not of each feature installation.
    You are done here! Click “Close”:
    
On this second part, we passed for all the steps to install the first node of a clustered instance. Of course some points will be slightly different from your environment, but here are pretty much all the steps to follow. After the completion of this installation you will need to add the other nodes to this clustered installation and you will be able to see the SQL Server “Role” created into the WFC Manager

Dealing with Failover Cluster Manager

As we already have one node of our clustered instance installed, we will need to manage its resources using the Failover Cluster Manager tool.
Click on the “Windows Key”+R.
Write “cluadmin.msc” and click on “Ok”.
The Windows Failover Cluster will be opened.
On the image you can see two “Roles”, representing two clustered SQL Server instances. The labeled as “SQL Server (DB)” is the one that we installed (Do you remember the choice made on the step 14?).
Selecting this Role will show all the resources that are part of this clustered installation, such as IPs, Disks, etc.

At this point, we have the clustered instance installed into one node only, so we cannot do a failover yet.

To stop the SQL Server, which will stop also the SQL Server Agent, right-click over the SQL Server Engine service and do the following:In other hands, to start do the following:

Note: 
When you start the SQL Server, the agent service needs to be started also. One way to reduce the number of steps is first start the Agent, this way, the SQL Server Engine will start automatically. The reason is that the SQL Server Agent is dependent of the SQL Server Engine, this way WFC will try to start the Engine service prior to start the Agent.
Another very important item are the dependencies of our SQL Server.
To check that:
Right-clicking the SQL Server Engine service.
Click on Properties.
Select the “Dependencies” tab.


This way you can see all the resources that the SQL Server Engine is dependent. Looking for our picture if either the "Cluster Disk 1" or "SQL Server network name (SQL04)" fails, the SQL Server Engine will shutdown/failover.

All the clustered resources can be dependent of another resources, For example, the "SQL Server network name (SQL04)" is dependent of the IP. This way, if the IP fails, the resource "SQL Server network name (SQL04)" will be offline and this will fire a SQL Server failover/shutdown.

What to take from here?

Check the dependencies and conditions on this tab, this can be useful to increase you availability rate!
As a practical example:
All the disks are important, but if the backup disk fails, we can continue with the service online, and fix the problem in background. But, if the SQL Server is dependent of this disk, we will have a failover/shutdown.

So, pay attention to this!
What’s next?
After completing the second step, we already have our instance working on a clustered environment, but we have only one available node. We will complete this series with more two parts:
Adding another nodes to our WFCI.
Configuring a dedicated MS DTC resource for our SQL Server Role.