Solutions Archive

How Snapshot Replication Works in MS SQL Server

Posted June 16, 2019 By AuthorVS1

This article explains the snapshot process by going into the details of its working.

Snapshot replication in SQL Server comes handy when you wish to share your database exactly the way it is right now, not focusing on any of the updates that would be made in it. Snapshot replication feature of SQL Server helps users to share data the way it appears in the current moment to the subscribers. It is important to note that although the snapshot replication process can be used in isolation, it is mostly used for providing just the database objects or the initial data which is used for the purpose of transaction and mail merge. If you wish to use snapshot replication in isolation, you should make use of it in the following situations.

  • If there are infrequent changes taking place in the data.
  • If you need to replicate small volumes of data only.
  • If you can afford to have data copies that are outdated on the publisher end.
  • Large changes in the database are made frequently.

The continuous overhead that the publisher needs to take up is much lower in snapshot replication as compared to transactional replication, as it does not take into account any of the incremental changes. In case the dataset you are replicating is large, you will be needing a large amount of resource to ensure that the snapshot is generated and applied properly. To make sure that it is done efficiently, you should keep in mind the size of complete data along with the frequency over which the changes are being made, and if using snapshot replication is the appropriate choice.

Snapshot Replication in SQL Server

Working of Snapshot Replication

Snapshot is used in SQL Server for all types of replication for initializing subscribers. However, in SQL Server, the snapshot agent is used for generating snapshot but not for delivering. The agent used for delivering the snapshot differs on the basis of the type of replication that is in use. The Distribution Agent is used for delivering the files in-case of snapshot and transactional replication. Merge replication makes use of SQL Server Merge Agent. 

Snapshot agent makes use of Distributor for running, it is also run at by the Distribution Agent as well as the Merge Agent for push subscriptions, in-case of pull subscription, they run at Subscribers.

You can opt for applying and generating applications immediately as well as after the subscription process is complete. The snapshot file that contains the schema and the data from published tables, along with database objects is created by the Snapshot Agent and then stored in the snapshot folder for use by the publisher. These files can also be used for the purpose of tracking records in the distribution database. The default snapshot folder is specified when the Distributor has been configured, however, an alternate location can be specified for a publication even when you have a default, and even when you don’t.

Apart from the working of the snapshot that has been described above, there is also another snapshot process that is divided into two parts and is used when making use of merge publication along with certain parameterized filters.

Apart from investing time in optimizing SQL Server, DBAs should also give a key consideration to data safety and accessibility. Investing in a sql fix tool can go a long way in preventing data loss situations.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit

This article addresses how on-demand analytics service works with Azure Data Lake.

Azure Data Lake is an analytics service, which aims at easing big data analytics into a simpler avatar. This allows organizations to focus on managing, running and writing jobs instead of operating on a distributed infrastructure. Users use queries to extract valuable insights and transform their data, instead of configuring or deploying or tuning their hardware. This on-demand service can be used for handling job of big or small scale by making an estimate of their power requirement. Users just need to shell out for the service while it’s running, by making a cost-effective investment. The analytics service is also designed to cater to Azure Active Directory which allows users to manage roles and access, integrated with their on-premises organization’s identity system in an effective manner.

On-demand Analytics Service Works with Azure Data Lake

It also comes with U-SQL language, which unifies goodness of SQL elegantly with user codes expressive power. The scalable distribution of U-SQL allows users to make sense of data across different SQL servers as well as in the store.

Capabilities of Azure Data Lake

Dynamic Scaling

One can consider Data Lake analytics as specifically designed to boost performance while working on a cloud scale. It provides resources dynamically and allows users to perform analytics operations on terabytes of data. It winds resources automatically, so user is only paying for the resource which is being used. Users can work more efficiently towards their organizational goals by focusing their attention on the logic and leaving the processing and storage part to the Data Lake.

Develop Faster and Optimize Effectively

Data Lake Analytics is seamlessly aligned with Visual Studio, offering known tools for debugging, running or tuning a code. U-SQL’s Visualization helps the users in how their code works at a higher scale, which helps in easily identifying the performance related issues and it can also be used for optimizing costs.

U-SQL – Powerful and Simple

Data Lake Analytics comes with U-SQL language that is simple to use and comes with an essence of familiarity. It is also designed to work with big data, which makes itso efficient on Data Lake.

IT investments for seamless integrations

Data Lake Analytics can effectively use users existing investments for identifying, securing, managing and data warehousing tasks. It essentially eases data governance while making it easier for the user to extend their existing data applications in quick time. Data Lake Analytics boasts of built-in monitoring features and auditing elements that works according to user permission and management through its integrated Active Directory.

Cost Effective and Affordable

Data Lake Analytics is an affordable, cost-effective service for working on a real world big data scenario. Users pay depending on the service’s operation time, and the service infrastructure with intelligence that it automatically cuts the power as soon as the task is complete. This ensures that the user is only charged for power which is being used in performing his/her tasks. The user also doesn’t have to spend on any sort of license, hardware or agreement to consume their services.

Access your complete Azure Database

Data Lake Analytics works best with Azure Data, promising top grade level of output and performance while offering parallelization for big data real world workloads. Itcan also be effectively used with Azure SQL Database and Azure Blob Storage.

With drastic improvements made in the latest SQL Server editions, one can make the mistake of thinking the application as flawless. Unfortunately it is hardly so and it still makes great sense to invest in a sql recovery tool.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit

In this article, users can learn to authorize additional connections after connecting them to a Database Engine in SQL Server.

Users can easily connect Database Engine to tools which are running in a computer, especially if they know the names of the instance, or if they are connecting to the engine as a part of the Administrator group. Note that in order to connect a database engine user needs to operate the connection procedure from the host SQL server.

Authorize Additional Connections in SQL Server

Steps for determining the name of Instance

Step 1: Login as a member of Windows Administrator group.

Step 2: Go to Connect to Server and Click Cancel.

Step 3: Select Registered Servers on View menu, if the registered servers don’t automatically display on your screen.

Step 4: Select Database Engine from Registered Servers toolbar > and expand Database Engine> then right-click on Local Server Groups > Go to Tasks > Select Register Local Servers.

Steps to Connect to a Database Engine

Step 1: Open Management Studio, go to File menu and select Connect Object Explorer.

Dialog box Connect to Server will open. Here the Server type will display the type of component which was last used by users.

Step 2: Now Select Database Engine.

Step 3: Open Server name box, here users need to type the Database engine instance that they want to connect. The default name of SQL Server instance is the same as the computer name.

Step 4:  Then click on Connect.

Authorizing Additional Connections

When a user installs a SQL server, he/she becomes an administrator, and one of the first things they do is authorize selected users or engines to connect to their network. For doing so, the user needs to create a login and authorize that login for accessing as a user in that database.

These logins can be Windows Authentication logins that take credentials from Windows or SQL Server Authentication or independent logins with your Windows credentials.

Steps for Authorizing Additional Connections

Note: User needs to create a new login account and access it as a user.

Step 1: Go to Management Studio, > Right Click on Logins > Select New Login.

Login- A New dialog will appear on your screen.

Step 2: The first page is for inserting General information, go to Login name and type ID of your Window login in this format <domain>\<login>.

Step 3: Go to Default Database and select AdventureWorks2008R2 or master, whatever is available. If both options are available then select the former.

Step 4: To turn new login into an administrator, open Server Roles page, and select sysadmin, or just leave it blank.

Step 5: In User Mapping page, select AdventureWorks2008R2 or master database, whichever is available? A user box is already populated with logins so ensure to use a unique login as to avoid any database from replacing any other account. When this file will be closed it will add this user to the selected database.

Step 6: In order to map the login with other database owner schema, type dbo in Default Schema Box.

Step 7: Accept the standard default setting for the Status box and select ‘OK’ to finally create the login.

Now users can use this login to grant Authorization to Additional Connections.

Despite best practices in place, companies can encounter a SQL crash. Hence as a failsafe measure keep a powerful tool to repair sql server database handy.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit