Monday, September 10, 2012

Moving all databases to another server without changing connection string

Today I'm gonna talk about how to moving all databases to another server without changing connection string in web.config, app.config and whatever configuration file.

Problem
Today I met an issue that my current SQL Server needs to be operated for some reasons and the SQL Server service will be offline for a few days. Our application is a global service for the company and it can't be offline for a long time. This means we have to move all databases from the current server to a backup server.

Solution
The basic idea is that create a backup server and restore all databases on it. Then setup a redirect function on the original server so that all requests can be redirected to the new server. This function is called "Alias" in SQL Server, which can be configured in Sql Server Configuration Manager -> SQL Native Client.

There are two summary steps to complete this solution:
1. Backup all databases from the original databases and restore them on the backup server.
2. Setup an Alias on the original server to redirect all requests to the backup server.

A. Backup and Restore
1. Open your SQL Server Management Studio from Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio
2. Login to the current DB server with your credential.
3. Right click you database, select Tasks -> Back Up...
4. Change the backup directory, and then click OK.
5. Copy the backup file to the new DB Server.
6. Follow step 1 to login the new DB server.
7. Right click the Databases node and then click the Restore Database...
8. Select From device, choose the backup file, and then select the database name from the To database drop down list. Click OK button.
9. Now, you have moved the target database from the original server to the backup server.

B. Setup Alias
After you move all database from the original server to the backup server, the next step is setup a redirect settings in the original server to redirect all requests to the backup server. This setting is called 'Alias' in SQL Server.

1. Select SQL Server Configuration Manager from Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools
2. Expand the SQL Native Client 10.0 Configuration -> Aliases. Generally there will be two menus, one is for 32 bit, the other is for 64 bit.
3. Right click the Aliases, select New Alias...
4. There are four fields need to be entered. The Alias Name, the Port No, the Protocol and the Server.
The Alias Name should be the 'Data Source' in your connection string
The default Port No is 1433
The Protocol should choose TCP/IP
The Server field should be the new SQL Server name.
5. After filling this dialog, click OK button to complete the setup. Make sure you setup the alias both in 32 bit and 64 bit.
6. Now, you backup server is ready to go. Disable you current SQL Server service and try your applications, it should work.

Friday, June 22, 2012

Best Practices for Speeding Up Your Web Site

Best Practices for Speeding Up Your Web Site

MVC knowledge

1. Custom Helper
Create a new Class and use the following code
public static class MyHtmlHelper
{
    public static MvcHtmlString Image(this HtmlHelper helper, string src, string altText)
    {
        var builder = new TagBuilder("img");
        builder.MergeAttribute("src", src);
        builder.MergeAttribute("alt", altText);
        return MvcHtmlString.Create(builder.ToString(TagRenderMode.SelfClosing));
    }
}
In cshtml layout page, add @using namespace on the top, the use @Html.Image(src, altText) at any place.

If we need to use this method in any layout pages, we should open the web.config file which is under Views folder, add <add namespace="namespace"/> in <namespce> block.

2. Custom Validation
There are two ways to custom the validation. The first one is custom attributes, the second one is use IValidatableObject interface.

Make the model inherits from the IValidatableObject interface, and then implement it. Such as
public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
{
    // Write your custom validation code here and then
    yield return new ValidationResult("error message", new[] { "fields that against the validation" });
}

In this way, the error message will be displayed behind the fields.

3. Quick add script link
Create the following code in your cshtml layout page,
@helper Script(string scriptName)
{
    <script src="@Url.Content("~/Scripts/" + scriptName)" type="text/javascript" />
}

Then you can use it in your layout page, like @Script("jquery.min.js")
Note: this script method is only available in current layout page, if you want it to be available in any layout pages, you either create a custom helper in C# code or do the following things.

Create a App_Code folder under this Mvc project, create a new MVC view page with name "Content", delete all content in that file, and then type the following code

using System.Web.Mvc;
@helper Script(string scriptName, UrlHelper url)
{
    <script src="@url.Content("~/Scripts/" + scriptName)" type="text/javascript" />
}

Then you can use it in any layout pages, like @Content.Script("jquery.min.js", Url)

Monday, April 23, 2012

[Forward] Running 32-bit SSIS in a 64-bit Environment

Original Source: http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

After my recent post on where to find the 32-bit ODBC Administrator on a 64-bit SQL Server, a new question was asked about how to get SSIS to run with the 32-bit ODBC instead of the 64-bit ODBC. You need to make a simple configuration change to the properties of your BIDS solution. Here I have a solution called 32bitODBC and it needs to run in 32-bit mode, not 64-bit mode. Since I have a 64-bit SQL Server, BIDS defaults to using the 64-bit runtime. To override this setting, go to the property pages for the solution. Select the Debugging node. Select Run64BitRuntime and set it to False. Save your changes.
image
What about when you finish your work in BIDS and you want to use the 32-bit runtime outside of BIDS? It depends on where you execute your package from.
If you double-click a dtsx file from Windows Explorer, it is executed by the SQL Server 2008 Integration Services Package Execution Utility. The default fully qualified path for that tool is C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe. In other words, it is a 32-bit product, so it doesn’t have any trouble running things in 32-bit mode by definition.
Here’s part of the error message generated from executing the package from a SQL Server Agent job using the default settings:
Description: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
The clue is the part about the architecture mismatch. Using the 64-bit runtime is the default behavior. Fortunately this is easy to change within the job definition by checking the Use 32 bit runtime checkbox.
image

Friday, April 20, 2012

4.1 Maximum Sub Array

I studied the maximum sub array problem from <Introduction to Algorithms>. I'm gonna write down the notion so that I can recall it in the future easily.

The problem
I won't describe the whole problem such as buying or selling a stock. Instead, I'm going to describe the the problem in another words. For a given integer array, which contains some positive or negative values, try to find a continuous sub array that the sum is the maximum.

Here is an example:
Array = {13, -3, -25, 20, -3, -16, -23, 18, 20, -7, 12, -5, -22, 15, -4, 7}
From the given array, we can easily find out the sub array, which is {18, 20, -7, 12}. We can get it because we are good at mathematics. But how to resolve it in programming? That's the maximum sub array problem.

The traditional solution
I do not want to speak so much about the traditional solution coz the most of programmer can tough it when he/she really think about it. But I still need to describe them because we can analyze it and compare with the Divide-Conquer-Combine solution. Okay, here we go!

The easily way to resolve this problem is adding these items one by one, comparing the sum to get the maximum sum, iterating all array items. After that, we will get the maximum sum sub array. Here is the pseudocode:

GetMaxSum(int currentIndex)
{
    sum = -∞;
    max = -∞
    right = -1;
    for(int i = currentIndex; i < array.Length; i++)
    {
        sum += array[i];
        if(sum > max)
        {
            max = sum;
            right = i;
        }
    }
    return (currentIndex, right, max);
}

This is the core code, and we still need a calling procedure, which will iterate the whole array and compare the result one by one. Such as:
First value 13: -3, -25 .......
Second Value -3: -25, 20, -3 .......

We can easily analyze the running times. In the for loop in the GetMaxSum method, it costs array.Length - current index times. For the calling procedure, because we will iterate the whole array (except the final one), which length is array.Length - 1. So the final running times should be ((array.Length -1) + 1)*(array.Length - 1)/2. In another words, it's (n-1) + 1)*(n-1)/2. So far, we can easily get the Θ(n2).

The Divide-Conquer-Combine solution
For the divide-conquer-combine solution, we can divide the array to two parts, such as [first, mid] and [mid + 1, last]. Then, we can try to find the sub array. But before that, it looks like we miss one important criterion that the sub array can cross the mid, which is [i, j]. i first < i < mid, mid < j < last. That's an important one. Okay, now we get three criteria:
1. sub array are in [first, mid]
2. sub array are in [mid + 1, last]
3. sub array are in [i, j], i first < i < mid, mid < j < last

Let me show the pesudocode for the third one first:
GetCrossingSubArray(int mid)
{
    left = -1
    leftMax = -∞;
    sum = -∞;
    for(int i = mid; i >= first; i--)
    {
        sum += array[i];
        if(sum > leftMax)
        {
            left = i;
            leftMax = sum;
        }
    }

    right = -1

    rightMax = -∞;
    sum = -∞;
    for(int i = mid; i <= last; i++)
    {
        sum += array[i];
        if(sum >  rightMax)
        {
             right = i;
             rightMax = sum;
        }
    }
    return (left, right, leftMax + rightMax);
}

Also, we still need the call procedure. Absolutely, it contains the criteria 1 and 2. That is:
GetSubArray(int first, int last)
{
    if(first == last)
        return (first, last, array[first]) //only one element
    else
    {
        int mid = (first + last)/2;
        leftMax = GetSubArray(first, mid);
        rightMax = GetSubArray(mid + 1, last);
        crossingMax = GetCrossingSubArray(mid);
        if(leftMax > rightMax && leftMax > crossingMax)
            return leftMax;
        else if(rightMax > leftMax && rightMax > corssingMax)
            return rightMax;
        else
            return crossingMax;
    }
}

For analyzing this algorithm, we need to calculate the running time separately. For the GetCrossingSubArray, the for loop is a constants time, which is n. For the GetSubArray, the running time is T(n) = 2T(n/2) + GetCrossingSubArray, which is T(n) = 2T(n/2) + n. Therefore, the final running times are: Θ(nlgn) + Θ(n). (Considering the GetSubArray as a tree, each level's sum is n. The tree high is lgn, which is the same with merge sort)

Tuesday, April 10, 2012

Register HttpModule in IIS6 and IIS7


For IIS6 and IIS7 Classic Mode register your module in web.config in the following manner:
<configuration>
  <system.web>
    <httpModules>
      <add name="Your HttpModule name" type="Namespace.ClassName,AssemblyName"/>
    </httpModules>
  </system.web>
</configuration>
For IIS7 in Integrated Mode do the following:
<configuration>
  <system.webServer>
    <modules>
      <add name="Your HttpModule name" type="Namespace.ClassName,AssemblyName"/>
</modules> </system.webServer> </configuration>

Monday, March 26, 2012

ESP JDBC Connector can't establish a connection to SQL Server database

The Problem
I setup a new ESP Server and install JDBC Connector on it. But it always threw an exception when I trigger the run from command line:

13:07:59,691 ERROR [JDBCConnector] Excpetion thrown in adapter class: com.fastsearch.esp.connectors.jdbc.JDBCAdapter, Could not connect to database: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "RSA premaster secret error".
13:07:59,693 ERROR [JDBCConnector] Caused by: Could not connect to database: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "RSA premaster secret error".
13:07:59,697 ERROR [JDBCConnector] Caused by: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "RSA premaster secret error".
13:07:59,698 ERROR [JDBCConnector] Caused by: RSA premaster secret error
13:07:59,699 ERROR [JDBCConnector] Caused by: SunTlsRsaPremasterSecret KeyGenerator not available


But the connector works fine if I trigger the run from Admin UI.

The Reason
I searched this issue in Google for a couple of hours and someone said the JDBC Connector will always use SSL encryption for SQL Server if it can. There is no way to disable it.

The Solution
Someone mentioned that we can install the new Microsoft JDBC Driver version but it is still helpless. Fortunately, I have another server setup with 'Correct' environment. I compared a quite folders, configure files and figured out the real solution.

After changing the java environment settings with following parameters, the JDBC Connector start working for me. Hopefully it still work for you. :)

Variable Value
Java_Home C:\Program Files (x86)\Java\jre
ClassPath .;%JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\lib\tools.jar
Path %JAVA_HOME%\bin