Thursday, January 30, 2014

Considerations for SQL Server Replication



There are some points to consider while implementing replication. These points include some technical and environment requirements for the replication, means in this article, I will explain about configuration setting for SQL Server replication. 

Check the below points, here you will get clear idea about configuration for replication-

1.  Select the proper version of SQL Server

As we know that there are different versions of SQL Server present in the market, so it must that first we have to select proper SQL Server version for replication.

Below table will make you understand to select proper version of SQL Server for Replication.

Reference from- SQL Server Versions

SQL Server Versions



By seeing above table, we can easily identify suitable version of SQL Server for our requirement.

2. Name of the Server (Use actual name of server)

While implementing replication, we should use name of the server (Actual Name of the Server) not IP address, If the user logins to server using IP (without actual SQL Server Name) then the replication wizard for creating publication will not work and it will generate error like this-

 SQL Server Name


So, we should use Server Name not IP Address of server.

3.  Start SQL Server Agent Service

This service should be started before going for replication, to start this we can get list of services using the Control Panel and then we can start.

Follow this -  Control Panel\All Control Panel Items\Administrative Tools\Services

 SQL Server Agent Service

By default, the SQL Server Agent service is disabled when SQL Server is installed unless you explicitly choose to auto start the service during installation.

4.  Enable the TCP IP Ports of SQL Server Instance

TCP IP Ports of SQL Server should be enabled because data transfer is done using TCP Protocol.
To enable this just open the SQL Server Configuration manager and Check for SQL Server Network Configuration, Here we will find Protocols for SQL Server Instance.   Just right click on TCP/IP protocol and enable it.

5. Enable the Named Pipes of SQL Server Instance

As we have enabled the TCP IP Ports, the same way we have to enable Named pipes using the SQL Server Configuration manager.
You can see below image, Here TCP/IP port and Names Pipes are enabled.


 SQL Server Configuration


6. Turn off the Firewall settings

In replication web synchronization requires additional ports to be opened on the firewall because of that firewall should be turned off.

7. Sufficient Disk Space

It may be possible that numbers of Subscribers are more, so in this case we should be more careful about disk space because after synchronization all the database will be sync and this require more space. Synchronization generates high values of data so it is must that we should have sufficient disk space to save the data.

8.  High Bandwidth

It is must that Bandwidth should be good because In case of large amount of data, it uses large amount of bandwidth. Database synchronization will be fast in case of high bandwidth.

9. Proper Database design

It is must that database should be designed properly to reduce possibility of conflict. It may be possible, conflict may occur but if our database design is proper then conflict can be resolved very easily.

We can consider these points while designing database structure-

(i) For Update Conflict-   Each record should have one column to track update time of the record so that while resolving the conflict, we can decide that which record should be reflected (Publisher or Subscriber record). By this way update conflict can be solved very easily.

(ii) For Insert Conflict- Each record should have primary key, it should not repeat at other subscriber also because after synchronization all records will be in same place. By this way insert conflict will not occur.

Like this, we can reduce possibility of conflict.

10. Selection of replication type


It is must that we should well-known about types of replication before implementing replication because we have to select the proper type of replication based on our environment.

These are the considerations, when we go for replication. By following these points implementation of replication can be done easily.

How to change connection string for EDMX in runtime?



In some cases, we need to change the connection string of edmx model in run time; we can change it from code behind code.

In this article, I am explaining - How to change connection string for EDMX in runtime?

Check the below code-

  protected void btnGetData_Click(object sender, EventArgs e)
        {
            try
            {
                TestDBEntities context = new TestDBEntities(GetConnectionString("DBName"));
                var result1 = from data in context.tab_Employees
                                       select data;
                gvEmpDetails.DataSource = result1.ToList();
                gvEmpDetails.DataBind();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

//Method to get connection string
        private string GetConnectionString(string strDBName)
        {
            SqlConnectionStringBuilder sqlBuilder;
            EntityConnectionStringBuilder entityBuilder;
            try
            {
                sqlBuilder = new SqlConnectionStringBuilder();
                sqlBuilder.DataSource = @"server name";
                sqlBuilder.InitialCatalog = strDBName;
                sqlBuilder.IntegratedSecurity = true;
                sqlBuilder.MultipleActiveResultSets = true;

                entityBuilder = new EntityConnectionStringBuilder();
                entityBuilder.Provider = "System.Data.SqlClient";
                entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
                entityBuilder.Metadata = @"res://*/ModelName.csdl|res://*/ModelName.ssdl|res://*/ModelName.msl";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return entityBuilder.ToString();
        }


In above code, while creating connection string, you need to pass details for connection string. Like-
Database name, Server name etc.

Here ModelName= Name of the model which you have created while generating edmx.

How to avoid or lock enter key in form?


Use this simple code to avoid enter key on form-


  <script type="text/javascript">
        $(document).keypress(
                    function (event) {
                        if (event.which == '13') {
                            event.preventDefault();
                            }
                                    }
                            );
 </script>

Wednesday, January 8, 2014

How to insert bulk record into database using SQL Server,C# and XML

If we have list of records then we can not process each record to insert into database because it is time taking process and it will degrade the performance.In this case we can use bulk insert. Suppose if we have bulk record in xml then we can easily dump that xml record into database.

Check the below code sample to insert bulk record.

Suppose this is sample XML string -

XML Data String

<?xml version='1.0' encoding='utf-8'?>
<Employees>
  <Emp>
    <EmpID>101</EmpID>
    <EmpName>Jitendra</EmpName>
    <Age>27</Age>
  </Emp>
  <Emp>
    <EmpID>102</EmpID>
    <EmpName>Ramesh</EmpName>
    <Age>30</Age>
  </Emp>
  <Emp>
    <EmpID>103</EmpID>
    <EmpName>Raj</EmpName>
    <Age>24</Age>
  </Emp>
 </Employees>
 

 
Now check the below stored procedure –

Stored Procedure

This stored proceduer will take the XML string as input and it will process this XML to insert into table.

--Stored procedure to insert bulk record using XML

CREATE PROCEDURE [dbo].[SP_EmpBulkInsert]   
(    @XMLData text    )   
AS   
BEGIN   
 SET NOCOUNT ON   
 BEGIN TRANSACTION   
 Begin Try   

  DECLARE @XMLDocPointer INT   
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XMLData   

   INSERT INTO tab_Employees
   (EmpID, EmpName, Age)   
   SELECT EmpID,EmpName,Age  
   FROM OPENXML(@XMLDocPointer,'/Employees/Emp',2)   
   WITH  (EmpID INT,EmpName nvarchar(50),Age INT)    

   EXEC sp_xml_removedocument @XMLDocPointer   
   COMMIT TRANSACTION   
   Return 0;    
 End Try   
 Begin Catch   
   ROLLBACK TRANSACTION   
 End Catch   
END


In this stored procedure I am inserting data into tab_Employees table, based on column selection data will be inserted to this table. Now chek the below code to call this stored procedure from C# application.

C# Code-

//Code to insert bulk records
        private void btnBulkInsert_Click(object sender, EventArgs e)
        {
            try
            {
                //Code to insert bulk record using XML
                string strConnectionString = "Your connection string";
                //Getting XML string
                string strXML = GetXMLString();
               
                //Connecting to database
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
                {
                    conn.ConnectionString = Convert.ToString(strConnectionString);
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SP_EmpBulkInsert", conn);
                    cmd.Parameters.AddWithValue("@XMLData", strXML);
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Records inserted successfully.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

         //XML string to insert bulk record
        private string GetXMLString()
        {
            return  @"<?xml version='1.0' encoding='utf-8'?>
<Employees>
  <Emp>
    <EmpID>101</EmpID>
    <EmpName>Jitendra</EmpName>
    <Age>27</Age>
  </Emp>
  <Emp>
    <EmpID>102</EmpID>
    <EmpName>Ramesh</EmpName>
    <Age>30</Age>
  </Emp>
  <Emp>
    <EmpID>103</EmpID>
    <EmpName>Raj</EmpName>
    <Age>24</Age>
  </Emp>
 </Employees>";
        }


In above code, I am getting XML string using GetXMLString() method. After getting the XML string I am passing this string as parameter for stored procedure. Finally when this code will be executed then you will find the new records in tab_Employees table.