locked
MDS Deployment Script RRS feed

  • Question

  • Hi All

    I was wondering if there is any T-SQL script to deploy the package in MDS?

     I am working with one of the client. We are using MDS to maintain dimension data before it goes to data warehouse. We created model, entities and populated it with data then we created a package and tied to deploy it on Test server. The package is getting loaded in the wizard but it’s never returning deployment status and eventually timing out. Good thing is Model is deployed successfully but the wizard is not returning successful completion of deployment status.

     

    Thursday, November 24, 2011 3:40 AM

Answers

  • Hi Mali, I made the folllowing post on your other thread (MDS Deployment Issues) before I saw this earlier post.

     

    May I ask what version of MDS you are running? There is no T-SQL script to deploy MDS packages, but in SQL Server Master Data Services 2012 there is a console application (MDSModelDeploy.exe) for deploying packages. You need to run it on the server, but it avoids the web session timeouts that you are running into, and so works better for large packages than the wizard.

    As you have noted, even when you receive the web timeout, the deployment is still continuing -- there is unfortunately no way to see the progress from the wizard at that point. If you have logging turned on in web.config ("All", or "Activity Tracing", see here for details on setting up logging) you will see log messages that track the status of the deployment, and you can check for successful completion that way.

     

    Thanks

    Mark


    This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by MALI_DBA Thursday, December 1, 2011 12:19 AM
    Tuesday, November 29, 2011 10:18 PM
  •  NetApp Solution works but it has it own limitations. Difficult to convince the client for that approach.
    • Marked as answer by MALI_DBA Wednesday, December 21, 2011 3:56 AM
    Wednesday, November 30, 2011 11:40 PM
  • You cannot vote on your own post
    0

    Hi All

     

    The solution to the problem. when we want to create the MDS package make sure you dose not include the data in it. Take the empty package and deploy it to other environment and later populate the data in the staging table and process it. its wokring fine for me. If the data is not processed from the MDS front ends then use the below TSQL script to process the data load in the stage tables. 

    DECLARE @ModelName nVarchar(50) = 'Reference Data' -- Change Model Name here

    DECLARE @Model_id int

    DECLARE @UserName nvarchar(50)= 'NTADMIN\Administrator'  -- CHANGE USER NAME HERE

    DECLARE @User_ID int

    DECLARE @Version_ID int

    DECLARE @BATCH_ID INT

    SET @BATCH_ID =  (SELECT MAX(ID) FROM [mdm].[tblStgBatch])

    SET @User_ID = (SELECT ID FROM mdm.tblUser u  WHERE u.UserName = @UserName)

    SET @Model_ID = (SELECT Model_ID FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName)

    SET @Version_ID =(SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION

              WHERE Model_ID = @Model_ID)

     

    EXEC mdm.udpStagingProcess @User_ID=@user_ID, @Version_ID=@version_ID, @StagingType_ID=4, @LogFlag=1, @DoValidate=1, @Batch_ID = @BATCH_ID


    Mali
    • Marked as answer by MALI_DBA Wednesday, December 21, 2011 3:56 AM
    Wednesday, December 21, 2011 3:56 AM

All replies

  • No, model deployment package can be only deployed by MDS UI/console tool, or through MDS Model Deployment API. To deploy package on T-SQL level is not possible.


    Yang Wang

    Microsoft|SQL Server|Master Data Services

    • Edited by Wang, Yang Monday, November 28, 2011 10:09 PM
    Monday, November 28, 2011 10:08 PM
  • No, model deployment package can be only deployed by MDS UI/console tool, or through MDS Model Deployment API. To deploy package on T-SQL level is not possible.

     


     

    Yang Wang

    Microsoft|SQL Server|Master Data Services


    OK if thats the case, i was womdering if there is some other ways to deploy MDS as the deployment wizard is not behaving as its described.

    Problem:

    The Problem is when MDS package is deployed to the pre-prod environment it is not loading data.

    The Package is creating the model and all its related Entities but failing to load the data. Further to this MDS deployment wizard is timing out i.e. it is not returning the Deployment Progress status?    

    Further to this a similar error is also noticed on Test Server i.e. the package is getting loaded in the wizard but it’s never returning deployment status and eventually timing out, but good thing is Model, Entites and the data in it is deployed successfully but the wizard is not returning successful completion of deployment status.

    Please note that we have created model, entities and populated it with data on the Dev Server then we created a package and tied to deploy it on Test and Prod server.

     

    Monday, November 28, 2011 11:11 PM
  •  

    From my understanding deployment wizard can only work with schema copy and small data migration. ( thousands of records is OK)

    If your entity has 1M records, then the XML message deployment wizard generated is very huge, definitely we will have WCF time out issue.

    Can you work at the database level for data replication?

    Possible solution is

    1) from your dev server, making a script for creating all db objects in your MDS database ( do not include data)

    2) run the script in production to create the schema 

    3) do data migration using SQL Server Management Studio

    4) From meta data, we can know the exact tables/columns for each entity

    5) Deployment Package  can be used only before you insert any data into domain entity. ( or with small entities)

    6) If possible, just use database backup/restore instead

    -----------------------------------------------------------MDS meta data definition------------------------------------------------------

    Yiqian

    • Edited by Hu yiqian Tuesday, November 29, 2011 2:47 AM
    • Marked as answer by MALI_DBA Wednesday, November 30, 2011 11:38 PM
    • Unmarked as answer by MALI_DBA Wednesday, December 21, 2011 3:56 AM
    Tuesday, November 29, 2011 2:42 AM
  • Hi Mali,

    I have experienced similar issue and Wizard deployment never worked for me.Since no detailed progress is available it seems this functionlity is the worst part of MDM. As a workaroud its quite easy code a Net App to get the metadata /permissions/entity member from existing mdm instance , serialize them to create a package and later this package can be desrialzed and exported back to any target mdm instance . This actually works since i have created similar application for my project.

    Let me know if u need detailed info on this .

    Tuesday, November 29, 2011 7:44 AM
  • Hi Mali, I made the folllowing post on your other thread (MDS Deployment Issues) before I saw this earlier post.

     

    May I ask what version of MDS you are running? There is no T-SQL script to deploy MDS packages, but in SQL Server Master Data Services 2012 there is a console application (MDSModelDeploy.exe) for deploying packages. You need to run it on the server, but it avoids the web session timeouts that you are running into, and so works better for large packages than the wizard.

    As you have noted, even when you receive the web timeout, the deployment is still continuing -- there is unfortunately no way to see the progress from the wizard at that point. If you have logging turned on in web.config ("All", or "Activity Tracing", see here for details on setting up logging) you will see log messages that track the status of the deployment, and you can check for successful completion that way.

     

    Thanks

    Mark


    This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by MALI_DBA Thursday, December 1, 2011 12:19 AM
    Tuesday, November 29, 2011 10:18 PM
  •  NetApp Solution works but it has it own limitations. Difficult to convince the client for that approach.
    • Marked as answer by MALI_DBA Wednesday, December 21, 2011 3:56 AM
    Wednesday, November 30, 2011 11:40 PM
  • You cannot vote on your own post
    0

    Hi All

     

    The solution to the problem. when we want to create the MDS package make sure you dose not include the data in it. Take the empty package and deploy it to other environment and later populate the data in the staging table and process it. its wokring fine for me. If the data is not processed from the MDS front ends then use the below TSQL script to process the data load in the stage tables. 

    DECLARE @ModelName nVarchar(50) = 'Reference Data' -- Change Model Name here

    DECLARE @Model_id int

    DECLARE @UserName nvarchar(50)= 'NTADMIN\Administrator'  -- CHANGE USER NAME HERE

    DECLARE @User_ID int

    DECLARE @Version_ID int

    DECLARE @BATCH_ID INT

    SET @BATCH_ID =  (SELECT MAX(ID) FROM [mdm].[tblStgBatch])

    SET @User_ID = (SELECT ID FROM mdm.tblUser u  WHERE u.UserName = @UserName)

    SET @Model_ID = (SELECT Model_ID FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName)

    SET @Version_ID =(SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION

              WHERE Model_ID = @Model_ID)

     

    EXEC mdm.udpStagingProcess @User_ID=@user_ID, @Version_ID=@version_ID, @StagingType_ID=4, @LogFlag=1, @DoValidate=1, @Batch_ID = @BATCH_ID


    Mali
    • Marked as answer by MALI_DBA Wednesday, December 21, 2011 3:56 AM
    Wednesday, December 21, 2011 3:56 AM
  • Anup,

    Can I possibly get more information on the .Net solution or code your are describing here. I am at a MDS client in Chicago. iwhiteside@msn.com

    Thank you,

    Ira Warren Whiteside

    Monday, November 4, 2013 5:00 PM