locked
Exposing subscription view data to other servers RRS feed

  • Question

  • Hi All,

    I was hoping to get some advice around exposing master data to databases on separate servers. 

    Historically there are two ways to do this:

    1. Via replication of Indexed Views
    2. Creating tables on the MDS database populated by subscription views and then replicating these tables

    Some preliminary investigation has found that option 1 is not possible due to the constraints around schema binding which affects the base tables.

    Option 2 seems quite messy given that any schema changes to the subscription views will require modifications to the populated tables (and the process that controls this) as well as the reinitialization of all publications.

    Finally the question - Is there a better way to do this that I've overlooked? All MDS information seems to stop at exporting data via subscription views.

    Thanks

    Ben

    Wednesday, April 20, 2011 11:17 AM

Answers

  • Hi Ben,

    The only other way to extract data out of Master Data Services is through the API. There's a good link on getting started with the API here:

    http://sqlblog.com/blogs/mds_team/archive/2010/01/12/getting-started-with-the-web-services-api-in-sql-server-2008-r2-master-data-services.aspx

    However, the database level exporting seems more relevant to you, hence you're correct to stick with the subscription views.

    You mention "replicating" in option 2. Not sure if you mean SQL Server replication there or not. I'd avoid SQL Server replication personally, I find it doesn't handle changes that elegantly. The method that I'd use in your situation is to use an ETL tool such as SSIS to transfer data to your target systems. Yes you will have to make a few changes if you change the sub views, but SSIS will make it relatively easy to make those sort of changes. Besides any changes in MDS should mean that you go through a proper round of testing etc, when you'd pick up any problems with data integration etc.

    Hope that helps

    Jeremy


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx
    • Marked as answer by Ben K Ng Wednesday, April 20, 2011 2:08 PM
    Wednesday, April 20, 2011 11:34 AM

All replies

  • Hi Ben,

    The only other way to extract data out of Master Data Services is through the API. There's a good link on getting started with the API here:

    http://sqlblog.com/blogs/mds_team/archive/2010/01/12/getting-started-with-the-web-services-api-in-sql-server-2008-r2-master-data-services.aspx

    However, the database level exporting seems more relevant to you, hence you're correct to stick with the subscription views.

    You mention "replicating" in option 2. Not sure if you mean SQL Server replication there or not. I'd avoid SQL Server replication personally, I find it doesn't handle changes that elegantly. The method that I'd use in your situation is to use an ETL tool such as SSIS to transfer data to your target systems. Yes you will have to make a few changes if you change the sub views, but SSIS will make it relatively easy to make those sort of changes. Besides any changes in MDS should mean that you go through a proper round of testing etc, when you'd pick up any problems with data integration etc.

    Hope that helps

    Jeremy


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx
    • Marked as answer by Ben K Ng Wednesday, April 20, 2011 2:08 PM
    Wednesday, April 20, 2011 11:34 AM
  • Hi Jeremy,

    Thanks for the fast reply.

    Yes I did mean SQL Server Replication for option 2 and agree that it's not an ideal solution.

    I'd dismissed the SSIS idea initially due to possible issues with latency/maintenance but on reflection, that does sound like the best option.

    Thanks

    Ben

     

    Wednesday, April 20, 2011 2:08 PM