Feeds:
Posts
Comments

Using DNS To Make SQL Server Database Access Less Volatile

August 25, 2010 by Andy Nelms

Suppose you need to move your database(s) from one physical server to another and at the same time you must use another IP address for the new server.  Now each application that connects to the database must change the IP address in their configuration file or worst case in their programs.  This would not be an effective way to spread goodwill and cheer no matter what time of year it occurred.

 Well not being a network guy, but having had my websites hosted by companies that are on top of things I have never had to change my IP address even though my database was moved from one physical server to another.  You might be asking yourself, “What kind of magic was involved?”  Well I am glad you asked.

Let’s say that you have a single SQL Server instance running on a server by the name of “fred” at IP 174.131.235.123 within your company whose name is Acme Road Kill. Let’s say your company website is acmeroadkill.com and it is resident on a server whose IP address is 174.131.235.122.

Now if you create a DNS A (address) record for fred.acmeroadkill.com with an associated IP address of  174.131.235.123 you may then connect to SQL Server replacing the IP address with the name fred.acmeroadkill.com.  With this change in place you can move the database from one IP address to another and the only change needed would be to the DNS record.  No configuration files would need to be changed and no software needs to be changed.

With the above knowledge as background let’s create a more needful case.  Suppose you are new to virtual private servers (VPS) and/or new to dedicated servers and your boss says that you are now responsible for hosting multiple web sites and multiple instances of SQL Server on multiple physical or virtual servers.

In this scenario, you are still working at Acme Road Kill, but you have two SQL Server instances on two separate physical or virtual servers named fred and barney.  Fred and barney have the IP addresses 174.131.235.123  and 174.131.235.124 respectively. Now you define two DNS A records as follows:

fred.acmeroadkill.com  with IP address 174.131.235.123  and
barney.acmeroadkill.com with IP address 174.131.235.124

Now either instance can be accessed with their symbolic names rather than their IP addresses. If you have questions or feedback please let me hear from you.

Posted in SQL Server and DNS | Tagged | Leave a Comment »

Detecting HTTP 404 Errors in Global.asax

August 12, 2010 by Andy Nelms

While I realize this is a simple task and once you see how to do it you will immediately say, I knew that, but for those of you who have not discovered how to detect http 404 errors let me show you.

The following code may be inserted into your Global.asax file.

 protected void Application_Error(object sender, EventArgs e)
{

Exception ex = null;
if (HttpContext.Current.Server.GetLastError() != null)
{

ex = HttpContext.Current.Server.GetLastError().GetBaseException();
if (ex.GetType() == typeof(HttpException))
{

HttpException httpException = (HttpException)ex;

if (httpException.GetHttpCode() == 404)

{

Server.Transfer(“~/ErrorHttp404.aspx”);

return;

}

}

}

The above code if placed into you Global.asax file will intercept Http 404 errors and transfer to your page that handles that particular error.  You may also check for any other particular http error and transfer to another page if you choose.

You see, it is a simple task, now that you know how.

Posted in Uncategorized | Leave a Comment »

Multiple Optional Search Criteria in SQL Server WHERE Clause

July 29, 2010 by Andy Nelms

Today I was reading in Joseph Sack’s book SQL Server 2008 Transact-SQL Recipes and discovered a jewel of a tip.  The jewel is located on page 275.

“So what is the jewel?”, you are asking.  Well I am glad you asked.  Let’s say you have 30 different criteria for which to search for a particular row or rows in your favorite table.  It could have been two criteria, but for effect let’s say it is thirty.  Now your favorite user has told you that he may search this table today with 2 criteria and tomorrow with 10 and on another day with 5 and oh by the way, it will always be in different combinations of search criteria.

I don’t remember the formula for computing the number of combinations  but it is a very large and imposing formula using factorials. Not only is the formula imposing, but the result of the formula is even more so. The number of combinations is quite sizable when there are 30 possible criteria . The number is quite imposing if you were thinking of suggesting a solution that involved multiple stored procedures (1 for each combination) or multiple IF statements if you were about to suggest utilizing those in some rather lengthy fashion.

So now you are asking, what other possibility is there?  Well the solution is the SQL function ISNULL.  Let me explain how ISNULL works.  ISNULL takes two parameters.  The first parameter will be checked to determine if it is null and if it is null, then the second parameter will be returned.  If the first parameter is not null then the first parameter will be returned.

Example:       ISNULL(@OrderNumber,OrderNumber)

If @OrderNumber is not null then the value of the parameter (or local variable) @OrderNumber will be returned. On the other hand if @OrderNumber is null then the column value OrderNumber will be returned.  However looking at this out of context might very well leave you with a blank stare on your face so let me see if I can contextualize it for you.

So we have the following query:

SELECT    OrderNumber,
              CustomerID,
              DateEntered,
              OrderTotal

FROM     Orders
WHERE
        OrderID = ISNULL(@OrderID, OrderID) AND
CustomerID = ISNULL(@CustomerID, CustomerID)

 

The WHERE clause will compare the OrderID column value to the @OrderID parameter (or local variable) if and only if the @OrderID value is not null.  If @OrderID is null then the value of the OrderID  column will be returned and it will always be equal to itself.

This allows you to code as many criteria as you like and if the criteria is NULL then it is ignored.  This technique yields a huge benefit when you need to use multiple optional selection criteria.  

Again this was an idea I read in Joseph Sack’s excellent book SQL Server 2008 Transact-SQL Recipes.  You may follow Joe at http://www.joesack.com

Posted in Sql Filter Criteria, SQL Server Queries, Uncategorized | Tagged , , , , , | 1 Comment »

Useful SQL Server Queries for Getting to Know a Database

September 16, 2008 by Andy Nelms

I thought I would list a few queries here that would help you get to know an unfamiliar SQL Server instance and its databases.

Listing All Databases for a SQL Server Instance

USE Master
GO
SELECT * FROM sys.Databases
GO
 

Listing all Tables for a Given Database 

USE yourDBName
GO
SELECT * FROM sys.tables
GO

Listing a Table’s Structure 

USE yourDBName
GO
sp_help yourTableName
GO  

Listing All Stored Procs for a Given Database

USE yourDBName
SELECT * FROM sysobjects WHERE type=’p’
GO 

Posted in SQL Server Queries | Tagged , , , | Leave a Comment »

Follow

Get every new post delivered to your Inbox.