SQL Server HA methods & making use of “Read-Only Routing” feature of “Always On Availability Groups”

What is High Availability SQL Server

High Availability means that the SQL Server instances, or databases will be available and reachable, with the least possible downtime, in case of any server crash or failure.

Below are the listed ways of achieving HA

1. Log shipping
2. Replication
3. Mirroring
4. Always On Failover Cluster
5. Always On Availability Groups

Log shipping

Replication

Mirroring

Always On Failover Cluster

 

Always On Availability Group

SQL Server tree view of Always On Availability Group

“Read Only routing” feature

•Always On secondary replica has feature to handle read-only connection requests

•This feature is called as Read-only routing feature

•By default both read and write operations goes to primary server

•If Application Intent is mentioned as ReadOnly in request connection, then request goes to secondary database

•Supports max 8 secondary databases

•Depending on the version, requests are served either from same secondary server or in round-robin fashion

Steps for trying from SSMS

Application code changes

•Make sure the HA method is AlwaysOn AG method

•Create new connection string by adding ApplicationIntent=ReadOnly in connection string

•While making read-only stored procedure DB calls, use the read-only connection string

Code snippet

<add name="ReadOnlyDBConnection" connectionString="data source=XXXXXX;initial catalog=XXXXX;integrated security=True;MultiSubnetFailover=True;ApplicationIntent=ReadOnly" providerName="System.Data.EntityClient" />

public static string ReadOnlyConnectionString

        {

            get

            {

                return ConfigurationManager.ConnectionStrings[“ReadOnlyDBConnection”].ConnectionString;

            }

        }

private DbConnection GetReadOnlyConnection()

        {

            var connection = this.providerFactory.CreateConnection();

            connection.ConnectionString = ReadOnlyConnectionString;

            return connection;

        }

Big benefits

•Taking off the read load from primary database

•Reduces lock situations

•Both read and write operations will be faster as they happen in different databases.

Overview of ScriptCS

What I found is that, ScriptCS (to be pronounced as Script-C-S) is a light weight open source development environment for using C# as scripting language.

ScriptCS removes the programmer’s dependency from Visual Studio. So one can write and execute the code without using Visual Studio at all. This means one can create a single ScriptCS file and execute it. There is no need to install .Net framework, IIS, etc... Also no cs-project is required to be created. It can be as simple as a single file (no additional DLLs). - This is cool.

So below are the components which enables programmers to write C# code without any project/Visual Studio:
1. Use Chocolatey (NuGet-based application/component installer) in powershell to install ScriptCS.
2. Install Nancy using NuGet Package manager. Nancy serves the web server role to run the apps built in ScriptCS.
3. ScriptCS uses "Roslyn" which is Microsoft's "Compiler as a Service" for compiling C# code. - This is really a revolutionary thought.

So these are all what is needed to run a C# application. There are some additional plug-ins available for syntax highlighting, debugging, etc. but these are non-mandate things.

My feeling is that it is like reinventing the wheel just to avoid Visual Studio. Although there are scenarios where tech evangelists are making use of ScriptCS and promoting it.

Generating data script for table using conditions in SQL Server

Problem Statement

Generate data script from table where records are in millions but we need data script for few records only.

Solution

Create a temporary table with the same structure as of the main table and insert data in the temporary table using the condition for filtering the records for which data script is required.

Or this can be done by:

select * into tmpMyTable from MyTable where <your condition(s)>

Now you can use the normal generate script method for this temp table and chose the option "Data Only" for getting the data script.

In the end drop the temp table.

Simple solution, works fine :)

Simple checkbox list using Knockout JS

Background

Often we need to display list of checkboxes in HTML and there is no native HTML control for checkbox list.
If you are from ASP.Net background then you will know that there exists an asp control for checkbox list. However here we are not going to discuss that.
Here we will see how to generate a customizable checkbox list using Knockout.JS.

Setting up Knockout JS

First step is to setup Knockout.JS framework in your web based application. In respect of time, for that purpose let me redirect you to KnockoutJS. Here you will get steps required to wire knockout with your web application.

Knockout ViewModel

Let us say, you are having a list of fruits (Apple, Mango, Grapes, etc.) which you want to display in a checkbox list. Then you should have an KO observable array for that. You can achieve it simply by following similar code in you JS file:
Assumption : You are having your ViewModelReady with data coming from server.

var self = this;
self.fruitsList = ko.observable([]);
self.selectedFruits = ko.observable([]);

if (data.fruitsList != null) {

   self.fruitsList ($.map(data.fruitsList, function (item) { 

         return new SelectedListItemViewModel(item) }));

}

........................................................

Separate helper function

Assumption : Object coming from server side is having "Value", "Text" and "IsSelected" properties.

function SelectedListItemViewModel(data) {

    this.Key = ko.observable(data.Value);

    this.Name = ko.observable(data.Text);

    this.IsSelected = ko.observable(data.IsSelected);

}

.........................................................

HTML code

In your HTML supported page you can keep the following HTML code to render the checkbox list:

Explanation:

We created a container

to hold all the checkboxes with some specified width and height so that it should get a look of list of controls.

For each item in the fruitList, one checkbox will get created and its value property will get binded with item's Key value.

To display text, we added a span and binded its text property with item's Name value.

Depending upon your object's properties, these two will vary from my example.

The important part to be noted here is another KO observable array "selectedFruits". On checking the checkbox, this observable will get filled with one item and will hold the "value" property of the checkbox. Using this observable array we can find out that in UI how many and what all checkboxes are selected.

Also if this list "selectedFruits" is already having some values in it which are same as of the checkbox's value property then the checkbox will appear as checked. Isn't it good for us!!!

Rest all depends on you that how you want to post this data to server back again.

Happy coding.

Close parent browser window, without a prompt

Hi,
After a lots of digging and permutations & combinations, finaaly I got the working solution for the problem of avoiding the prompt from IE browser when we try to close it using Javascript.
I have not tested in on any other browser other than IE and can guarantee that this works fine with IE.
Code:

function openPopup()
{
window.open('','_self','');
window.opener='';
window.close();
window.open('NewPage.aspx','','');
}

Regards,
Soumen

Passing Values between user controls using Events and Delegates

Introduction

Always there comes a requirement where we developers need to pass values to between different user controls which are parts of a webpage or even sometimes there comes a requirement where we need to pass values from user control to the container page.
The job is easy when we have to pass values which are there on the user controls by exposing the value fields as properties of the user control. However it is challenging when on certain event the values should get passed.

Solution

The simplest solution to this situation I found is to handle this by use of events and delegates. Let me explain the solution with one simple example.
For example on a content page I am having two user controls e.g. UC1 and UC2. Contents of UC1 are:
1. List box having list of States
2. List box having list of Cities in the State (Pre-condition: User selects State from ListBoxStates and respective cities in the selected state gets populated in the ListBoxCities)
3. Button control, which will be used for confirming the user’s selections. Let us name it as btnSelect.
Contents of UC2 are:
1. List of selected cities from UC1. Let us name it as ListBoxSelectedCities.
Our requirement is whenever user selects (multiple) cities from ListBoxCities and clicks on btnSelect in UC1 then the selected cities should get populated in ListBoxSelectedCities in UC2.

In UC1, we need to declare the following objects:

public delegate void PassSelectedValues(string[] selectedCities);

public event PassSelectedValues citiesSelected;

Now in UC1 from the button click event we can raise citiesSelected event, delegate of which will carry the required values which we need to pass in between UC1 and UC2. In button click event we have to create the list of selected cities from ListBoxCities and we have to raise the citiesSelected event like this:

citiesSelected(selectedCities);

Here selectedCities is an array of strings which we have to create by looping through the ListBoxCities.

We have to declare a public property in UC2 which can be used to set the ListBoxSelectedCities. For example in UC2:

public string[] SelectedCitiesList

{

set

{

ListBoxSelectedCities.DataSource = value;

ListBoxSelectedCities.DataBind();

}

}

In the content page we have to declare the event handler for the event (citiesSelected) which we have raised from UC1. Thus in content page on page load we have to declare the handler of this event like this:

UC1.citiesSelected += new citiesUserControl.PassSelectedValues(passValuesHandlerMethod);

This handler method in the content page should have the same signature as of the delegate’s signature defined in UC1.

protected void passValuesHandlerMethod(string[] selectedCities)

{

}

Now from this method we can pass the value coming from UC1 to UC2 like this:

protected void passValuesHandlerMethod(string[] selectedCities)

{

UC2.SelectedCitiesList = selectedCities;

}

In this way whichever values we are selecting in UC1 will get passed to UC2.

Conclusion

In this article we have seen that how to pass values between different user controls on a webpage using events and delegates. Same concept can be applied for passing value from user control to the content page.

Hope this will help everyone in handling their cases.

Regards,

Soumen

www.ethicaldeveloper.blogspot.com

Session maintenance using SQL Server for .Net Web applications

Hi,

I am listing all the steps how to configure the SQL Server so that our .Net application can store and retrieve the Session values in and from SQL server. All suggestions for improvement are heartly welcome.

Session maintenance in SQL Server for .Net Applications:
1. Run aspnet_regsql.exe from .Net command prompt. This will run a wizard.
2. Provide server name
3. Use windows authentication if you are having rights to create tables on your server else provide the sys admin user name and password
4. Use the AspState database
5. This will create set of tables in AspState and tempdb databases
6. Create logins for the application user on AspState and tempdb databases
7. Execute the following script:
use APSState
grant EXECUTE on GetHashCode to [user]
grant EXECUTE on GetMajorVersion to [user]
grant EXECUTE on TempGetAppID to [user]
grant EXECUTE on TempGetStateItem to [user]
grant EXECUTE on TempGetStateItem2 to [user]
grant EXECUTE on TempGetStateItem3 to [user]
grant EXECUTE on TempGetStateItemExclusive to [user]
grant EXECUTE on TempGetStateItemExclusive2 to [user]
grant EXECUTE on TempGetStateItemExclusive3 to [user]
grant EXECUTE on TempGetVersion to [user]
grant EXECUTE on TempInsertStateItemLong to [user]
grant EXECUTE on TempInsertStateItemShort to [user]
grant EXECUTE on TempInsertUninitializedItem to [user]
grant EXECUTE on TempReleaseStateItemExclusive to [user]
grant EXECUTE on TempRemoveStateItem to [user]
grant EXECUTE on TempResetTimeout to [user]
grant EXECUTE on TempUpdateStateItemLong to [user]
grant EXECUTE on TempUpdateStateItemLongNullShort to [user]
grant EXECUTE on TempUpdateStateItemShort to [user]
grant EXECUTE on TempUpdateStateItemShortNullLong to [user]
grant SELECT on ASPStateTempApplications to [user]
grant INSERT on ASPStateTempApplications to [user]
grant UPDATE on ASPStateTempApplications to [user]
grant SELECT on ASPStateTempSessions to [user]
grant INSERT on ASPStateTempSessions to [user]
grant UPDATE on ASPStateTempSessions to [user]

use tempdb
grant SELECT on ASPStateTempApplications to [user]
grant INSERT on ASPStateTempApplications to [user]
grant UPDATE on ASPStateTempApplications to [user]
grant SELECT on ASPStateTempSessions to [user]
grant INSERT on ASPStateTempSessions to [user]
grant UPDATE on ASPStateTempSessions to [user]

8. Do the following changes in Web.Config file:
allowCustomSqlDatabase="true" cookieless="false" timeout="20"/>
9. Make sure that all classes in the application, whose objects we are storing in Session, should have the [Serializable] attribute.

Now few points:

1. We can store huge data in Session without effecting the Web server momory.
2. We are shifting the burden of runtime memory to SQL database. So that we can now create the application which can handle huge Session data.
3. Minus point is that every Session request will be redirected to DB Server from Web Server.