ASP. Net WebAPI With. Net Core & Micro Services

Join Me in GuruGram(Gurgaon) in IKeva to learn

ASP. Net WebAPI With. Net Core & Micro Services

Warm Regards

Sachin Kalia

Categories: .Net Core , Angular2 , Entity Framework & LINQ , SQL Server , WCF and WebAPI

BOOKMYSEAT APPLICATION Angular Js asp.net webapi and sql server 2012 : part one

BOOKMYSEAT APPLICATION AngularJS  Asp.Net Webapi and Sql Server 2012

Here, I’ve thought to write my thoughts about hands-on Angular. This is the first article, which tells you  how to get your hands dirty with AngularJS, ASP.NET WEBAPI and SQL Server BookMySeat Application Tutorials.
This is the technology stack for BookMySeat library Application, as shown below:

I’ve designed a simple BookMySeat Library Application, where you will be familiarized with a few keywords & components, which you may be confronting in coming days. These keywords are shown below in the article-

Component of Angular Description

Module
Modules serve as containers to assist you to organize the code within your AngularJS Application. Modules can contain sub-modules.

$http
$http is an AngularJS Service for reading the data from the remote Servers.

Angular ui.bootstrap
<scriptdata-require="ui-bootstrap@*"data-semver="0.10.0"src="http://angular-ui.github.io/bootstrap/ui-bootstrap-tpls-0.10.0.js"></script>

Services
Services are a point, where you can put common functionality to an AngularJS Application. For example,  if you would like to share the data with more than one controller then the best way is to promote the data to the Service and then make it available via the Service. Services extend the controllers and make them more globally accessible.

Routes Routing in Angular JS
Routes allow us to determine the ways to navigate to the specific states within our Application. It also allows us to define the configuration options for each specific route, such as which template and controller to use.

View
The view in AngularJS is what exists after AngularJS has compiled and rendered the DOM.

@scope
$scope is essentially the “glue” between the view and controller within an AngularJS Application. It supports two way binding within an Application.

Controller
The controller is to define the methods and properties that the view can bind to and interact with. Controllers should be lightweight and only focus on the view; they’re controlling.

Directive
A directive is an extension of a view in AngularJS, which allows us to create custom, reusable elements. You can also consider the directives as the decorators for your HTML. Directives are used to extend the views and to make these extensions available for use in more than one place.

Route Config
The config block of an AngularJS Application allows for the configuration to be applied before the Application actually runs. This is useful to set up routes, dynamically configuring Services and so on.

Dependency Injection
How can we inject dependency in Angular controller and module? For this sample Application, we have injected $Log,$RouteParams,$modal ,Custom Services and many more.

The Application which we are about to build consists of almost all the defined keywords, mentioned above and the initial look of the application is shown below-

There are a few points which we’ll cover in this Application. To make it complete, you can also reference how this Application will work from this BookMySeat Gif representation,

• The screenshot, shown above, is a sample form for BookMySeat Application .You can book your seat for the specific slot. You can cancel your seat also from the given slot.
• In the same way, we’ll try to integrate UI-bootstrap (modal Popup) to show which seat is booked by the user.
• We will focus on Custom Directive and Custom Service.
• Usage of $http to call to WebApi to populate the data on HTML page.
• Usage of dependency injection at the various levels, Load partial templates to MVC view page.
• There can be some various add ons, which you can do at your level after the completion of this Application like Email notification after booking the seat, cancel the seat, booking slot information and responsive Application .
• The structure of the BookMySeat Application is given below-
  
  The basic life cycle of Angular app is given below-
  

Hope it’ll help you some day. Enjoy coding.

Categories: AngularJS , SQL Server , WCF and WebAPI

Bookmyseat application : angularjs ,webapi and sqlserver 2008 using angular ui bootstrap

 Bookmyseat application angularjs ,webapi and sqlserver 2008 using angular ui bootstrap

Hi Folks,

I have created a bookmyseat application which has the following technology stack.

• AmgularJS (  Angular Module,Controller,Angular Service, Routing, Dependancy injection, Service calling using $http.post)
• Asp.Net WebAPI (Get,Post,Put methods with Routing)
• SQl Server 2008R2
• Angular UI-bootstrap UI Bootstrap for PopUp purpose

 

 

I’ll come with step by step articles day by day.

Thanks

Categories: AngularJS , SQL Server , WCF and WebAPI

Swap column value of table using Cursor in SQL Server 2008

Swap column value of table using Cursor in SQL Server 2008

As idea to write an article came from last day when one of my friends encountered a question in an interview. I feel it would be a good brain teaser.

Question - > Please swap a value of specific column value with other. For an example if we have a table t1 having column Gender than Male should be replaced with Female and vice versa.

Note: You should have little knowledge of Cursor and Switch Statement in SQLServer2005,2008 so on.

I’ve taken an idea and created a table keeps the structure as depicted below:

select * from customers

Here we will swap name column values like “Sachin” will be replaced by “dotnetpiper.com” and vice versa.

Output will be like this.

I’ve used cursor to do the desired task .Reason to choose cursor because of it will fetch each row individually and will perform the desired action. Here is the actual SQL query implementation as

  1: DECLARE @name VARCHAR(50) -- database name  
  2: DECLARE DotnetPiper_Cursor CURSOR FOR  
  3: SELECT name 
  4: FROM customers
  5: 
  6: OPEN DotnetPiper_Cursor   
  7: FETCH NEXT FROM DotnetPiper_Cursor INTO @name   
  8: 
  9: WHILE @@FETCH_STATUS = 0   
 10: BEGIN         
 11:       Update customers SET name=( Case when @name='sachin' then 'dotnetpiper.com'
 12:                                        when @name= 'dotnetpiper.com' then 'sachin'
 13:                                         else @name 
 14:                                         End) WHERE CURRENT OF DotnetPiper_Cursor
 15: 
 16:        FETCH NEXT FROM DotnetPiper_Cursor INTO @name   
 17: END   
 18: 
 19: CLOSE DotnetPiper_Cursor   
 20: DEALLOCATE DotnetPiper_Cursor

SQL Snippet to create table Customer table:

  1: USE [Employee]
  2: GO
  3: 
  4: /****** Object:  Table [dbo].[Customers]    Script Date: 08/03/2015 07:18:12 ******/
  5: SET ANSI_NULLS ON
  6: GO
  7: 
  8: SET QUOTED_IDENTIFIER ON
  9: GO
 10: 
 11: SET ANSI_PADDING ON
 12: GO
 13: 
 14: CREATE TABLE [dbo].[Customers](
 15: [ID] [int] NULL,
 16: [Name] [varchar](50) NULL,
 17: [Salary] [varchar](50) NULL
 18: ) ON [PRIMARY]
 19: 
 20: GO
 21: 
 22: SET ANSI_PADDING OFF
 23: GO
 24: 
 25: 
 26: 

 

I wish it will help you in order to crack such question or scenario.

Note: Please share you opinion and advise us for better approach also.I really appreciate you initiation

To know more MVC and WebApi Kindly go through with these links

MVC Articles & WCF and WebApi

Thanks.
Enjoy coding and reading.

Categories: Interview Questions , SQL Server

Difference between ROW_NUMBER() OVER () & PARTITION BY clause

Difference between ROW_NUMBER () OVER () & PARTITION BY clause

 

 

This is one of the main basic and needed terms which you may have used in your daily uses in SQL Server and off course one of the main interview question.

 

Before jumping into code indeed lets understand what it means by ROW_NUMBER() & PARTITION BY clause.

Order by is required clause for using ROW_NUMBER(), as row number is generated on the basis of the column used along with ROW_NUMBER()

Example: Suppose there are 7 employees in your table 3 are from city Roorkee and others 4 are from city Rohtak and Noida respectively.

Now, if you would use "Order By empid" only, it would assign ROW_NUMBER() on the basis of empid in ascending order from 1, 2, 3, 4,5,6,7

But, if you want to generate row numbers differently for cities then use PARTITION BY in that case.

Please have a look on the example depicted below:

  1: declare @table table(
  2: 
  3: empid varchar(10),Name varchar(20), city char(10)
  4: 
  5: )
  6: 
  7: insert into @table 
  8: 
  9: select 'EMP001','Sachin Kalia', 'RoorKee'
 10: 
 11: union all select 'EMP002', 'Rohit Kalia', 'RoorKee'
 12: 
 13: union all select 'EMP003', 'Yogendra', 'RoorKee'
 14: 
 15: union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'
 16: 
 17: union all select 'EMP005', 'Parvinder', 'Rohtak'
 18: 
 19: union all select 'EMP006', 'Abhinav Singh', 'Noida'
 20: 
 21: union all select 'EMP006', 'Anshu Agarwal', 'Noida'
 22: 
 23: Select * from @table
 24: 
 25: 

After running the above sql statements here is the result.Now we will apply Order By and Partition By clause seperatly in order to understand the facts.

Now execute the given below lines one by one and see the actual facts.

  1: declare @table table(
  2: 
  3: empid varchar(10),Name varchar(20), city char(10)
  4: 
  5: )
  6: 
  7: insert into @table 
  8: 
  9: select 'EMP001','Sachin Kalia', 'RoorKee'
 10: 
 11: union all select 'EMP002', 'Rohit Kalia', 'RoorKee'
 12: 
 13: union all select 'EMP003', 'Yogendra', 'RoorKee'
 14: 
 15: union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'
 16: 
 17: union all select 'EMP005', 'Parvinder', 'Rohtak'
 18: 
 19: union all select 'EMP006', 'Abhinav Singh', 'Noida'
 20: 
 21: union all select 'EMP006', 'Anshu Agarwal', 'Noida'
 22: 
 23: --Select * from @table
 24: 
 25: SELECT *, ROW_NUMBER() OVER (ORDER BY empid ) As Counter
 26: 
 27: FROM @table
 28: 

The above given query will give the the following output :

One more clause that can be used with Over is PARTITION BY in case when you want to set one more level of filtration while generating Row_Number.Here is an example given below:

  1: declare @table table(
  2: 
  3: empid varchar(10),Name varchar(20), city char(10)
  4: 
  5: )
  6: 
  7: insert into @table 
  8: 
  9: select 'EMP001','Sachin Kalia', 'RoorKee'
 10: 
 11: union all select 'EMP002', 'Rohit Kalia', 'RoorKee'
 12: 
 13: union all select 'EMP003', 'Yogendra', 'RoorKee'
 14: 
 15: union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'
 16: 
 17: union all select 'EMP005', 'Parvinder', 'Rohtak'
 18: 
 19: union all select 'EMP006', 'Abhinav Singh', 'Noida'
 20: 
 21: union all select 'EMP006', 'Anshu Agarwal', 'Noida'
 22: 
 23: SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY empid) As CounterByCityName FROM @table
 24: 
 25: 

This is the beauty of these keywords and may utilize to make the rows sequential in manner.

Kindly find an attached SQL file.

Thanks

Categories: SQL Server

Select data of a table from another database in SQL Server 2008R2

Select data of a table from another database in SQL Server 2008R2

In this short snippet blog I’ve demonstrate a condition to use two databases / schemas on the same database server, you can query across databases with the given below syntax :

  1: select * from database1.dbo.table1 t1 join database2.dbo.table2 t2 on t1.column_Name= t2.column_Name

e.g. select * from DBEncrypt.[dbo].TestTable

Sachin Kalia

Categories: SQL Server

A stored procedure as system object in SQL SERVER 2008

A stored procedure as system object in SQL SERVER 2008

Today I encountered a situation to use a stored procedure across all objects , so that I could use stored procedure on any database within a given SQL Server instance.

After some digging I found that there are few ways to create such a stored procedure:

1. The stored procedure must be created in the master database.
2. The name of the stored procedure must start with “sp_“.
3. The stored procedure must be marked as a system object.

In this I am pointing to the third one to create stored procedure as system object.Below is the way to create and its use.

  1: CREATE PROCEDURE sp_DotnetPiper
  2: AS
  3: SELECT      name, object_id, type_desc
  4: FROM        sys.objects
  5: WHERE       is_ms_shipped <> 1
  6: 
  7: -- Mark the stored procedure as a system object
  8: EXEC sys.sp_MS_marksystemobject sp_DotnetPiper
  9: 
 10: --To execute the systemmarked procedure
 11: exec sp_DotnetPiper

The first two are quite easily accomplished but the third requirement requires the use of an undocumented stored procedure named  sys.sp_MS_marksystemobject which will mark the created stored procedure as a system object.

After you completed the above steps, you can run the stored procedure on any of the databases found in that instance of SQL Server.

I’ve run the command and it shows the following output as depicted below:

   1:  USE   Northwind

   2:  exec sp_DotnetPiper

 

Note: This is one of the question may ask in an interview .

Hope it will help you

 

Thanks

Categories: Interview Questions , SQL Server

Primary index and Secondary index In Sql Server

Primary index and Secondary index In SQL Server

Primary index:

• A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates.
• This also called as Clustered index.
• e.g. ID  in Employee can be an example of this.

Secondary index:

• A Secondary index is an index which is not a primary index and it may have duplicates values.
• e.g. First_name can be example of it. Because First_name name can have similar values like “james”.

These may be helpful down the line .Enjoy Coding and Smile 

MVC Articles & WCF and WebAPI

Thanks for reading

Categories: SQL Server

Count Number of Columns in a Table and Delay in Sql server

Count Number of Columns in a Table and Delay in Sql server

Hi Geeks,

A simple query to find the number of columns in a table .

Here in example i have used Northwind  database table named as 'Categories' which returns the no of columns.

  1: use Northwind
  2: 
  3: select count('c') 'Columns Count' from sys.syscolumns where id=object_id('Categories')
  4: 

This is very simple ,it generally uses in lot of queries.

2. The second is very interesting fact about Sql server is that to put delay in your query execution or on demand execution of query after certain time or on any specified time.During execution of the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.

Kindly find the depict image as an example.

I you notice at the right of the bottom ,it shows it took 10 seconds to print this message.

This is very cool feature could be used in your query only.WAITFOR can be used with query but not with UDF or cursors.

Hope will help you some where.

Enjoy Coding.

Sachin Kalia

Categories: SQL Server

Database Table Encryption Using Symmetric Key in SQL Server 2008 R2

Database Table Encryption Using Symmetric Key in SQL Server 2008 R2

The purpose of this article is to provide security to a database column's value so that no one can understand what the actual value is.

Scenario: Actually a few days ago I had the situation to add one more layer of security, like encryption and decryption of some keys (for example SaltKey or RSAKey), that were being used in my C# code base file. These keys were being used to encrypt and decrypt the username and password, however I don't want to make these keys public to anyone (for example SaltKey or RSAKey) , because anyone can read this easily using reflector if obfuscation is not applied to the C# code base file (.cs file). So I thought to put these keys into the database and put up all the columns values in encrypted mode and decrypt it since I needed the actual values of these keys.
I studied about this from various portals before implementing. In this article I'll also share some findings that may be helpful to you to create rudimentary queries.
There are many approaches to implement encryption but I've chosen column encryption of a database table. I just encrypted and decrypted the data placed in the table. This approach takes little time to do the required procedure than the other approach.
So let's start with encryption and decryption process executed with a Symmetric Key. I've created a database with the name DBEncrypt with a table “TestTable” that has three columns in it. Kindly look at the screenshot given below:

If you notice in the image shown above there are three columns with some text value. My main objective is to encrypt those values and decrypt accordingly.
Create a master key, each database can have one master key and this master key used to protect the private keys of the certificates. After this we'll create a certificate to encrypt the data in the database and symmetric key (the symmetric key used by the sender and the receiver of a message, it's a common key used to encrypt and decrypt the message). We've created a symmetric key using the certificate (EncryptTestCert).

  1: CREATE MASTER KEY ENCRYPTION  
  2: BY PASSWORD = 'Support@123'
  3: 
  4: CREATE CERTIFICATE EncryptTestCert  
  5: WITH SUBJECT = 'SupportCert'
  6: 
  7: CREATE SYMMETRIC KEY TestTableKey  
  8: WITH ALGORITHM = TRIPLE_DES ENCRYPTION  
  9: BY CERTIFICATE EncryptTestCert. 

Now it's time to create another three columns that will keep encrypted data of these three columns (SaltKey, RSAKey and PrivateKey) of table respectively. I've also added one more Column SaltKeyEnCol2 as nvarchar(max). I will share the reason to create an extra column later in this document.

 

The code given below encrypts three columns (SaltKey, RSAKey and PrivateKey) values to the newly created

  1: ALTER TABLE TestTable  
  2: ADD SaltKeyEnCol VARBINARY(256),RSAKeyEnCol VARBINARY(256),PrivateKeyEnCol VARBINARY(256)  
  3: 
  4: ALTER TABLE TestTable  
  5: ADD SaltKeyEnCol2 nvarchar(max) 
  6: 

columns above with an alter commnad. Also note that we are using the same certificate(EncryptTestCert) to open the symmetric key and make it available for use. Kindly have a look at the syntax given below.

  1: OPEN SYMMETRIC KEY TestTableKey DECRYPTION   
  2: BY CERTIFICATE EncryptTestCert,   
  3: UPDATE TestTable   
  4: SET SaltKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey),RSAKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),RSAKey),PrivateKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),PrivateKey) 
  5: 

Kindly manually close the symmetric key otherwise it will remain open for the current session.
After the execution of the preceding query you can have a look and see the affects made into the database. Kindly have a look at the image given below:

If you notice in the image above, it only shows you <Binary data> in encrypted Columns. Which was a little tricky for me initially, after looking at these values I thought I made some mistake and the data doesn't look OK.

To cater this issue, I again created another column with the name “SaltKeyEnCol2” as I have shared this in the preceding in the document and is also shown below. It helped me to see the actual value.

1. ALTER TABLE TestTable  
2. ADD SaltKeyEnCol2 nvarchar(max)

Please execute the SQL query given below and see the effect.

1. OPEN SYMMETRIC KEY TestTableKey DECRYPTION  
2. BY CERTIFICATE EncryptTestCert  
3. UPDATE TestTable SET SaltKeyEnCol2 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey)

The column SaltKeyEnCol2 has some encrypted value in SaltKeyEnCol2 as depicted below in the screen shot rather than the <Binary Data> value.

As we can see above, we've achived the encryption of the required columns. Now the turn is to decrypt the data placed in the encrypted columns. Kindly refer to the image given below with the outcome.

Kindly find in the following the complete database query script.

  1: CREATE MASTER KEY ENCRYPTION  
  2: BY PASSWORD = 'Support@123'  
  3:   
  4: CREATE CERTIFICATE EncryptTestCert  
  5: WITH SUBJECT = 'SupportCert'  
  6:   
  7: CREATE SYMMETRIC KEY TestTableKey  
  8: WITH ALGORITHM = TRIPLE_DES ENCRYPTION  
  9: BY CERTIFICATE EncryptTestCert  
 10:   
 11: ALTER TABLE TestTable  
 12: ADD SaltKeyEnCol VARBINARY(256),RSAKeyEnCol VARBINARY(256),PrivateKeyEnCol VARBINARY(256)  
 13:   
 14: ALTER TABLE TestTable  
 15: ADD SaltKeyEnCol3 nvarchar(max)  
 16: --drop COLUMN SaltKeyEnCol3   
 17:   
 18:   
 19: OPEN SYMMETRIC KEY TestTableKey DECRYPTION  
 20: BY CERTIFICATE EncryptTestCert  
 21: UPDATE TestTable SET SaltKeyEnCol3 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey)  
 22:   
 23:   
 24: OPEN SYMMETRIC KEY TestTableKey DECRYPTION  
 25: BY CERTIFICATE EncryptTestCert,  
 26: UPDATE TestTable  
 27: SET SaltKeyEnCol2 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey),RSAKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),RSAKey),PrivateKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),PrivateKey)  
 28:   
 29: CLOSE SYMMETRIC KEY TestTableKey;  
 30:   
 31: OPEN SYMMETRIC KEY TestTableKey DECRYPTION  
 32: BY CERTIFICATE EncryptTestCert  
 33: SELECT CONVERT(nvarchar(max),DECRYPTBYKEY(SaltKeyEnCol2)) AS DecryptSaltKeyEnCol FROM TestTable  
 34:   
 35: OPEN SYMMETRIC KEY TestTableKey DECRYPTION  
 36: BY CERTIFICATE EncryptTestCert  
 37: SELECT CONVERT(nvarchar(max),DECRYPTBYKEY(SaltKeyEnCol2)) AS DecryptSaltKeyEnCol,CONVERT(nvarchar(max),DECRYPTBYKEY(RSAKeyEnCol)) AS DecryptRSAKeyEnCol,  
 38: CONVERT(nvarchar(max),DECRYPTBYKEY(PrivateKeyEnCol)) AS DecryptPrivateKeyEnCol FROM TestTable  
 39:   
 40: CLOSE SYMMETRIC KEY TestTableKey 

I hope it will help you somewhere. I took the idea from: Introduction to SQL Server Encryption and Symmetric Key Encryption.
Thanks For reading this article

To learn more about MVC please go to the following link.

MVC Articles

Thanks.
Keep coding and Stay Happy

Categories: SQL Server

Difference between Transactional,Snapshot and Merge Replication

Difference between Transactional,Snapshot and Merge Replication

 

• Transactional replication – Transactional replication replicates each transaction from publisher to subscriber for the article/table which is being published. Initially transactional replication takes a snapshot of the publisher database and applies to the subscriber to synchronize the data. As we know that replication is helpful to synchronize the data among the Publisher and subscriber databases. A Log Reader Agent reads transaction from the transaction log and writes it to the distribution database and then to the subscriber database. Each database published using transactional replication keeps Log Reader agent and moves transaction from publisher to distributor. Transactional replication is helpful where real time data is required such as online trading and bank specific transactions to keep live data backup of each debit or credit transaction.

               Note: I also noticed that transactional replication applies only on those article/tables which has primary key in it.This is the only reason it keeps track of data changed at publisher level.

• Snapshot replication - Snapshot replication works slightly different in manner from transactional replication. As name “snapshot” says it takes a snapshot of the published database and moves it to a subscriber database. Snapshot replication completely overwrites the transactions/data at the subscriber database every time as it drops the tables and recreates it again. Snapshot is best in use when data frequency is bit low or subscriber needs data on a certain interval rather than very frequently. E.g. Snapshot replication is to update a list of items that only changes periodically or at certain interval like end of business day. Snapshot is bit slowly than transactional because on each attempt it moves multiple records suppose millions of records from one end(Publisher) to another end(Subscriber).
• Merge replication – As name implies “Merge” it joins publisher and subscriber databases, it is one of the complex replication and helps to keep data consistent among multiple ends. Merge replications works in an integrated manner with Publisher and subscriber, every time Merge Agent traces each change occurred at both end and send those changed transactions to distributor database for further propagation. The Merge Agent runs either at distributor end for push subscriptions or the Subscriber for pull subscriptions. What I understood about Merge replication, its best fit into Retail market like Pantaloons, BigBazar Lifestyle and many more. Where it’s helpful in synchronizing the records among multiple stores as per inventory increased or decreased.

To know more about Replication please go through these links.

 

• SNAPSHOT REPLICATION IN SQL SERVER 2008: PART1
• SNAPSHOT REPLICATION (SETTING UP PUBLISHER) IN SQL SERVER 2008: PART2
• SNAPSHOT REPLICATION (SETTING UP SUBSCRIBER) IN SQL SERVER 2008: PART 3

 

Thanks

Sachin Kalia

Categories: SQL Server

Snapshot Replication (Setting up Subscriber) in SQL Server 2008: Part 3

Snapshot Replication (Setting up Subscriber) in SQL Server 2008: Part 3

This article is a continuation of the following articles:

• SNAPSHOT REPLICATION IN SQL SERVER 2008: PART1 
• SNAPSHOT REPLICATION (SETTING UP PUBLISHER) IN SQL SERVER 2008: PART2

I thought to keep the pictorial concept so that anyone can easily setup and map at their end.

Publisher:
The Publisher can be denoted as the origin of the database on which the schema or record amendments take place. Publisher is a point that manages data for replication and sends commands to the distributor for further process, for example S1.

Subscriber
The Subscribers is the point, where the subscriber database incurs the notification and a copy of the data from the distributor about the changes happens in the publisher database. The subscriber database generally lives on a different something, for example S2.

Setting up the Subscriber
Once the publisher (Snapshot replication (Setting up publisher) in SQL Server) has been setup, it's time to create the subscriber for it.
The following procedure is required to set up the subscriber.

Step 1:Right-click on DBCOmpany_Pub and choose the option “New Subscription” to create a subscription.

Step 2: A Publisher dropdown shows the publisher name that we have created recently in the last activity. Please refer to the image below for reference and click on the Next button.

Step 3 :The following window will appear and click on the "Next" button.

Step 4 : In this step we will add new subscribers that will be a different machine or the same machine where we have already set up the Publisher account (for example S2). Kindly refer to the screen captured below for a better understanding. Then click on the Next Button.

Step 5 : Please see the image given below and take the necessary action.

Kindly refer to the image below after clicking on the button as advised above in the image. Now choose the necessary actions in a prompted dialog box and press OK.

After fulfilling the window shown above kindly click on the Next button and proceed further.

 
Step 6 : The following window will appear and click on "Next".

Step 7 :The following window will appear and click on "Next".

Step 8 :Another window will appear and click on "Next".

Step 9 : The following window will appear and click on "Finish".

Step 10 : A window will appear with some messages for Successful creation of the Subscription.

Step 11 :Expand the publisher and you can see the subscriber as shown in the image below.

Step 12 :Expand Replication -> Local Publication the choose publication from there, right-click on the selected publisher and choose Launch Replication Monitor. Kindly refer to the image below:

The Replication monitor describes to you in detail about the success or failure of the snapshot replication. You can read all the details from the three options given there.

As soon as we are done with the setup of Subscriber, it's time to watch the replication. As soon as you run the Replication the monitor starts synchronizing the data from the publisher to the subscriber though there may be chances that data is not synchronizing from the publisher to the subscriber, in that case you just go to view the snapshot agent status to verify its running status as shown in the image below:

The Magic of Data replication is over.
Thanks for reading this. I'll post another part soon.

To learn more about MVC, please go to the following link.
MVC Articles
Enjoy Coding and Reading

Categories: SQL Server