SQL Server : A tutorial for building a replication between 2 machines (Part I)

Introduction

Replication is a set of technologies for copying and distributing database objects and data from one database to another database. After the intial configuration a synchronization process is running to maintain consistency.The distributor is kept in sync synchroneously with the publisher.

This blogpost is the first blogpost in a series of blogposts about replication on 2 machines. This series of blogpost is about a more complex configuration of replication than the blogpost I've written earlier.

These are the blogposts about this series about replication:

• SQL Server : a tutorial for building a replication between 2 machines (Part I).
• SQL Server : a tutorial for building a replication between 2 machines : Share (Part II).
• SQL Server : a tutorial for building a replication between 2 machines : Distributor (Part III).
• SQL Server : a tutorial for building a replication between 2 machines : Publisher (Part IV).
• SQL Server : a tutorial for building a replication between 2 machines : Subscriber (Part V).
• SQL Server : a tutorial for building a replication between 2 machines : Monitoring (Part VI).

The main parts

In this section I'll go into detail about replication. For replication, there are three main parts important, namely the publisher, the subscriber and distributor:

• Publisher. The publisher has the data (publication) it can offer to other subscribers. There can be multiple subscribers.
• Subscriber. The subscribers are the database servers that want to have the data from the publisher when the data is changed.  
• Distributor. The distributor keeps track of the subscribers and to ensure that the subscribers are notified of changes.

Below another picture I've borrowed from a SQLServerCentral.

Green is read and red is write

Initial data set 

The initial dataset is the dataset that is initially captured on the publisher and stored on the subscriber. This is done by the snapshot agent. It is also possible to carry this out through a backup or SSIS.

Changed dataset 

The log reader agent is running at the distributor. The log reader identifies INSERT, UPDATE, and DELETE statements or other changes marked for replication in the transaction log. The agent will then copy transactions in batches to the distribution database on the distributor. The distribution database is a store-and-forward queue and the final step is storing the changed data set on the subscriber.

The agents

The transactional replication of SQL Server is implemented with a Snapshot Agent, Log Reader Agent, Queue Reader Agent and a Distribution Agent:

• The Snapshot Agent makes snapshot files based on the schema and data from the publisher of the databaseobjects and data. The Snapshot Agent places a shared lock on all tables ready for publishing while the BCP files are generated. This ensures transactional consistency of the data, but it ensures that other requests that attempt to write simultaneously are blocked. Depending on the size of the table this may be substantial. This is only the first time. During normal operations there are much finer-grained and very short-locks required.
• The log reader Agent monitors the transaction log of each database which is configured for replication.. The Log Reader agent is responsible for copying the transactional log records in the distribution database. This is done according to the ACID principles. The LogReader connects the Publisher, and looking through the log files for records marked with the replication flag and copies the information in the distribution database. Transactions can be as long as the LogReader records when tranaction can't be removed from the log file due to problems. Growth of the log file can be the result. A possible cause may lie in the distributor that is out. This is viewable in  the "log_reuse_wait_desc column column in the sys.database.
• The distribution agent copies the initial snapshot files from the snapshot folder and the transactions in the distribution database to the subscriber. The distribution agent is responsible for the transfer of the distribution database to the subscription database. The distribution Agent connects with the distributor and reads the stored changes. Then, the changes will be processed in jacket order.
• The Queue read agent is used together with transactional replication with updating. The agent runs on the distributor and move changes from the subscriber back to the publisher. 

Transactional replication is not performed by SQL Server database engine but with windows programs that connect to the servers involved in the replication process. The agents are implemented by the SQL Server Jobs.

Important choices 

For replication, there are several possible important choices, I've gathered so far:

• Type of replication. Possible options: snapshot (eg 1 x daily.), Transactional Replication (almost realtime), merge replication (merge data from different sources, eg, salespeople). 
• Configuration. It is possible to place the distributor (monitors and supervises the replication process) on a separate machine.
• Push and pull configuration. 
• When chosen for push the distributor gets the data from the Publisher and sends it to the subscriber. 
• For pull the distributor gets the data from the publisher and the subscribers retrieve the data from the Distributor.

Conclusion

In this blogpost I've desribed the main parts of replication, the agents and the important choices of replication.

Greetz,
Hennie

SQL Server Replication : a tutorial for building a simple replication

Introduction

In this blogpost I'll show you how to set up a basic replication in SQL Server (2008 R2). There are multiple sites, blogs and other stuff where you can read the ins and outs of replication. I'll not go into detail about replication.

The basic components

Let's explain some basics. There are a couple of different types of replication possible: transactional, snapshot, and mergereplication. In this blogpost I'll describe the transactional replication. And, there are a couple of keyplayers involved : the distributor, the publisher and the subscriber:

• A distributor. The distributor identifies whether changes has happened on the publisher.
• A publisher. The publisher is the SQL Server instance that publishes the articles.
• A subscriber. SQL Server instance that subscribes to a publication.

There are also other components involved :

• Articles : for each SQL Server object an article needs to be defined.
• Publication : a collection of articles.
• Publication database : a database that contains the articles that needs to be published.
• Distribution database : the distribution database stores metadata and replicated data.
• Subscription : a collection of articles.
• Subscription database : the target database where the articles are stored.

And, there are a couple of agents that take care of the replication:

• Snapshot agent. The snapshot agent creates the snapshot files with the schema and the data of the to be published tables and database objects
• Log reader agent. The log reader Agent monitors the transaction log of every database that is configured for replication. And it copies the data into the distribution database.
• Distribution agent. The distribution agent copies the initial snapshot files of the snapshot folder and the data from the distribution agent into the subscriber database.
• The Queue Reader Agent is used with transactional replication with the queued updating option. 

The tutorial described in this blogpost is executed on one virtual environment. Currently setting up a testenvironment with a Domain controller, a publisher and a distributor in separate Virtual machines. I'll blog about this configuration later.

Setting up the distribution

In this blogpost I'll describe a simple replication setup. Don't bother (yet) about the right AD accounts. Just a plain and simple replication on one database instance (in a virtual environment on my laptop). Okay let's start the tutorial:

1. Start SQL Server Management Studio and connect to an instance.

2. In the Object Explorer, right click on the replication map and click on Configure Distribution.

3. Click on Next.

4. Choose the current instance for setting up the distributor. Don't change anything and press Next.

5. Although it's not recommend to choose Yes here, because of not following security best practices, choose Yes;-). This tutorial is about setting up a simple replication.

6. Now it's time to specify the snapshot folder. The snapshot folder is used for the initial synchronization of the transactional replication. In this tutorial I created the snapshot folder on the same machine. I can imagine that you want to create this snapshot folder on a share on the network.

7. After setting up the snapshot folder, the following window appears. This window specify the distribution database and the location of the database and log files. Click Next.

8. Now we are entering the Publisher window. Here you can specify the Publisher. It grants the server access to the Distributor.

Setting up the publisher

9. Now you have setup the Distributor it's time to setup the Publisher. In SQL Server Management Studio right click on Local Publications, and then New Publication.

10. Press Next.

11. Press Next.

12. Press Next.

13. Press Next.

14. Choose the database that database that should be replicated (in my case AdventureWorksLT). Press Next.

15. Choose transactional replication and press Next.

16. Choose the tables, stored procedures or even fields in the table you want to replicate. Press Next

17. Add filters if you want and press Next.

18. In the following window you can specify when you want to run the snapshot. Choose "Create a snapshot immediately and keep the snapshot available to initialize subscriptions".

19. Use the SQL Server Agent account for the snapshot agent.. Press Next.

20. Press Next..

21. Enter some properties depending on what you want. Press Next.

22. On the Complete the wizard window an overview is given of the settings. Press Next.

Settting up the subscription

23. Now it's time to setup the subscription. The subscription resides mostly on a different machine. In this simple tutorial I'll keep the subscriber on the same machine. Right click in the Object explorer, Replication, Local Subscription and New Subscription and press Next on the opening window of the wizard.

24. Choose the server and the Publisher database. Press Next.

25. Specify whether this is a pull or push subscription.

26. Choose server and database where the subscription database should be. Press Next.

27. Specify the security account for the distributor. Press ... .

28. Choose options like the one in the window below. Press OK.

29. Press Next.

30. Specify the synchronization schedule. Choose Run continuously and press Next.

31. Press Next.

32. Press Next.

33. Press Next.

34. And we are ready to execute. Press Next.

35. Wait and see how the subscriptions are generated.

Looking around

The next thing I did was browsing SQL Server and folders to see what has changed. Below the definitions of the replication process.

The snapshot folder:

The replication is executed by a number of replication agents (jobs) :

• Snapshot agent. performs initial synchronization between the publication database and the subscription database.
• Log reader agent. Reads the transaction log of the Publication database.
• Distribution agent. Applies changes to the subscription database (from the recorded changes in the distribution database).

Below the jobs that controls the replication of articles.

Conclusion

Although it's a simple tutorial, some considerations are shown that can help you when you're implementing replication in a real world scenario. In this blogpost I showed a simple Next Next tutorial for building a replication process.

In future blogpost I'll implement replication on multiple machines and with better security options.

Greetz,
Hennie

SQL Server : The system can not find the LandingPage.exe Error

Introduction

I removed SQL Server from my VirtualBox environment and  I ran into an error during the installation of a new version of SQL Server 2008 R2. In this blogpost I'll describe the error and how you can solve this.

The error

Below a screenshot of the error "The system can not find the file specified....LandingPage.exe" during the installation of SQL Server 2008 R2.

You can solve this by deleting the folder Microsoft_Corporation in C:\Users\Administrator\AppData\Local\Microsoft

Conclusion

LandingPage.exe error can be resolved by deleting the folder Microsoft_Corporation in C:\Users\Administrator\AppData\Local\Microsoft.

Greetz,
Hennie

SQL Server : Reporting Services Catalog Database File Existence Error

Introduction

I removed SQL Server from my VirtualBox environment and  I ran into an error during the installation of a new version of SQL Server 2008 R2. In this blogpost I'll describe the error and how you can solve this.

The Error

Below a screenshot of the error "Reporting Services Catalog Database File existence" and the error "Reporting Services Catalog Temporary Database File existence" during the installation of SQL Server 2008 R2.

The problem is that the removal of SQL Sever installation didn't remove the database folders.

I deleted the SSRS databases and now the installation of SQL Server proceeds.

Conclusion

When you remove a SQL Server installation from your system, the databases are not deleted and therefore you can run into errors when you install SQL Server again. Rename or remove the Reporting Services databases.

Greetz,
Hennie

SSAS : The ID does not have a 'Database' parent Error

Introduction

Today,  I had a 'great' idea adding a cube in SQL Server Analysis Services (SSAS) to an existing project for testing purposes. I ran into some strange errors : "The  <ID> does not have a 'Database' parent" Error. Also, a red cross in the cube icon in the solution explorer was visible. In this blogpost I'll show the errors, the possible cause and how to fix this..

The <ID> does not have a 'Database' parent Error

So, the following happened to me today. I added a cube that I copied earlier from the same project to an existing project. Below a screenshot of visual Studio (SSAS) of the error:

The red cross in the cube icon

I solved this error by renaming the ID the cube (xml file). The name was already used by another cube in the project. You do this by right clicking on the .cube file in the solution explorer, right click on view code and enter another ID in the XML file. Next step is saving the file and close the XML file.

I don't know if there is another solution. The ID in the properties of cube is greyed out and cannot be changed. I didn't encounter any problems after changing this in the cube. If you experience strange behaviour, let me know..

Conclusion

In this blogpost I've changed the ID in the XML Code of .cube file in order to avoid the  "The <ID> does not have a 'Database' parent" Error.

Greetz,

Hennie

SSAS : Multicurrency Problem (Part III)

Introduction

I wrote two blogposts about the multicurrency problem in SSAS. The first blogpost (Part I) was about the multicurrency solution and is mainly inspired by the book of "Expert Cube Development with SQL Server 2008 Analysis Services" by Chris Webb, Alberto Ferrari and Marco Russo. In the second blogpost (Part II) further improvements were implemented. In this blogpost I'll bring the solution a step further.

Chris Webb wrote in his blogpost "Tuning the Currency Conversion Calculations created by the Add Business Intelligence Wizard" that the "Add Business Intelligence" Wizard is a missed opportunity from Microsoft. He is saying that because the idea is great, simplifying the difficult stuff of SSAS, but the generated MDX script in SSAS is bad implemented.

Improving the Multicurrency problem a step further

In this blogpost won't  describe step by step the complete solution. I'll start with the solution that ended in my second blogpost about MultiCurrency.  In this third blogpost I'll work out the solution that is proposed by Chris Webb in his blogpost.

The business Intelligence Wizard adds a named query in the datasource view, adds an extra dimension to the cube and adds some MDX script to the cube. The following situation could is a typical many-2-many conversion example.

The datasource view:

The dimension usage tab in the cube:

The MDX script calculation in the calculation tab:

 Scope ( { Measures.[Amount]} );
   Scope( Leaves([DimDate]) ,
    [Reporting Currency].[EUR], 
    Leaves([DimCurrency]));
  
    
        
        // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Rate] 
        Scope( { Measures.[Amount]} );
            
         This = [Reporting Currency].[Local] * Measures.[Rate];
 
        End Scope;
 

   End Scope; 
  
   // This is the One to Many section
   // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension 
            Scope( Leaves([DimDate]) ,  
    Except([Reporting Currency].[Currency Name].[Currency Name].Members, 
    {[Reporting Currency].[Currency Name].[Currency Name].[EUR],
    [Reporting Currency].[Currency Name].[Currency Name].[Local]}));
  
    
      
      // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [Rate]… 
      // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
         Scope( { Measures.[Amount]} );
             This = [Reporting Currency].[Currency Name].[EUR] / (Measures.[Rate], LinkMember([Reporting Currency].[Currency Name].CurrentMember, [DimCurrency].[Currency Name])) ;
      End Scope; 
 
  
   End Scope; // Leaves of time, all reporting currencies but local and pivot currency 
  End Scope; // Measures

  // End of the currency conversion wizard generated script  

Okay let's summarize the settings that has been set so far (from my former posts):

• Property Type of Currency dimension to Currency
• Property Attribute Type of  CurrencyName to Currency > Currency > Currency ISO Code.
• Property Attribute Type of month to Date > Calendar >Month Of Year
• Property IsAggregatable of the attribute Currency Name to False. 
• Property Usage to key of the attribute Currency Name.
• Property KeyColumns to Currency ID.
• Property NameColumn to Currency Name.

• Property Type of Date dimension to Time
• Property Attribute Type of  Day to Date > Calendar > Day of Week.
• Property Attribute Type of Month to Date > Calendar >Month Of Year

• Property of the MeasureGroup FactEchangeRate to ExchangeRate.

Now, new settings introduced for this blogpost (or described) in this blogpost:

• Property Formatstring of the Measure Amount to Currency.
• Measure Rate is semi additive and therefore set the property Aggregatefucntion to "Last Non Empty". For instance If we want to see the Exchange rate for a week we don't want to see the sum of the Exchange rates of that week but the most recent exchange rate of the week.
• Add to the calculation tab:
• Language([DimCurrency].[Currency Name].[EUR]) = 1043;
• Language([DimCurrency].[Currency Name].[GBP]) = 2057;
• Language([DimCurrency].[Currency Name].[MEX]) = 2058;
• Language([DimCurrency].[Currency Name].[USD]) = 1033;

There are also better solutions by making this a variable solution by storing this information in the currency dimension table and use them by the Value column or the Member property. I'll investigate this in the future.

Now we are going to adjust the solution with the improvement of Chris :

1. Go to the DataSource View in the Solution explorer of the project in Visual Studio

2. Create a named query "Reporting Currency Rates" in the data source view. With the following query :

     SELECT        CurrencyRate_ID, Date_ID, Currency_ID, Rate
     FROM            dbo.FactCurrencyRate

3. Create a relationship between Date and the new created named query  "Reporting Currency Rates"

4. Create a relationship between Reporting Currency and the new created named query  "Reporting Currency Rates". The Data source view would look like this now:

5.  Create a new measure group "Reporting Currency Rates" by clicking on the Cube Structure Tab.

6. Right click on the Cube (MultiCurrencyV3Redo in my case) and create a new measure group.

7.  Adjust the dimension Usage Tab according to the following screenshot:

8. Now go the Calculations tab and replace the following code:

  Scope ( { Measures.[Amount]} );
   Scope( Leaves([DimDate]) ,
    [Reporting Currency].[EUR], 
    Leaves([DimCurrency]));
  
    
        
        // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Rate] 
        Scope( { Measures.[Amount]} );
            
         This = [Reporting Currency].[Local] * Measures.[Rate];
 
        End Scope;
 

   End Scope; 
  
   // This is the One to Many section
   // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension 
            Scope( Leaves([DimDate]) ,  
    Except([Reporting Currency].[Currency Name].[Currency Name].Members, 
    {[Reporting Currency].[Currency Name].[Currency Name].[EUR],
    [Reporting Currency].[Currency Name].[Currency Name].[Local]}));
  
    
      
      // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [Rate]… 
      // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
         Scope( { Measures.[Amount]} );
             This = [Reporting Currency].[Currency Name].[EUR] / (Measures.[Rate], LinkMember([Reporting Currency].[Currency Name].CurrentMember, [DimCurrency].[Currency Name])) ;
      End Scope; 
 
  
   End Scope; // Leaves of time, all reporting currencies but local and pivot currency 
  End Scope; // Measures

  // End of the currency conversion wizard generated script 
 // </Currency conversion> 

By this code :

       Scope ( { Measures.[Amount]} );
    Scope([DimDate].[Date ID].[Date ID].MEMBERS);
      Scope([DimCurrency].[Currency Name].[Currency Name].MEMBERS);
   Scope(Except([Reporting Currency].[Currency Name].[Currency Name].Members, 
    {[Reporting Currency].[Currency Name].[Currency Name].[Local]}));
 
       THIS = IIF([Measures].[Rate - Reporting Currency Rates] = 0, NULL, 
                    IIF([Measures].[Rate] = 0, NULL, 
                        ([Measures].[Amount] *[Measures].[Rate])) / [Measures].[Rate - Reporting Currency Rates]);
        
        End Scope;
       End Scope; 
      End Scope; 
    End Scope;

Now let's take a look at the result:

Conclusion

In this blogpost I've discussed an improvement of the multicurrency problem. This is the third blogpost about multicurrency.

More information available....

There is more information available at the following pages:

• Chris Webb
• Chris Schmidt
• SQL Magazine
• Christian Wade
• Bob Penev
• Technet