THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Master Data Services Team

Blog for the Microsoft SQL Server Master Data Services (MDS) team. Blog posts are contributed by various team members.

  • New features in SQL CTP2.3 releases

    We are excited to share the new features which shipped in SQL CTP2.3 public release.  You can download the product here:http://www.microsoft.com/en-us/download/details.aspx?id=48726, and Excel add-in here: http://www.microsoft.com/en-us/download/details.aspx?id=47343

    In this monthly release, we added a few highly asked features.  Please give it try and look forward to hearing your feedback.

    Manage Business Rules (MDS Add-in for Excel)

    In the Master Data Services Add-in for Excel you can manage Business Rules, such as creating and editing business rules. Business rules are used to validate data. For more information, see Manage Business Rules (MDS Add-in for Excel).

    Many-to-Many Relationships in Derived Hierarchies

    You can now create a Derived Hierarchy that displays many-to-many relationships. A many-to-many relationship between two entities may be modeled through the use of a third entity that provides a mapping between them. The mapping entity is an entity that has two or more domain-based attributes referencing other entities.

    For example, entity M has a domain-based attribute that references A and a domain-based attribute that references B. You can create a hierarchy from A to B using the mapping entity.

    For more information, see Show Many-to-Many Relationships in Derived Hierarchies (Master Data Services)

    Merge Conflicts

    If you try to publish data that has been changed by another user, the publish will fail with a conflict error. To resolve this error, you can perform merge conflicts and republish the changes. For more information, see Merge Conflicts (Master Data Services) and Merge Conflicts (MDS Add-in for Excel)

     

     SQL Master Data Services Team

  • What's New in Master Data Services - SQL2016 CTP2.2(July) Release

    SQL Server 2016 Community Technology Preview 2.2 is publicly released and the latest bits can be found at http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

    In CTP2.2 MDS introduced following changes:

    1. Custom Indexes

    You can create a non-clustered index on one attribute (single index) or on a list of attributes (composite index), in an entity, to help improve the query performance. For more information, see Custom Index (Master Data Services).

    2. Entity Sync Relationship

    You can share entity data between different models by creating an entity sync relationship. For more information, see Entity Sync Relationship (Master Data Services).

    3. Member Revision History

    A member revision history is recorded when a member is changed. You can rollback a revision history, as well as view and annotate revisions. Using the Log Retention Days property, you can specify how long historical data is retained. For more information, see Member Revision History (Master Data Services).

  • Transactions, Validation Issue and Staging table cleanup

    Transactions, Validation Issue and Staging table cleanup:

    Master Data Services previous versions didn’t have a supported way to clean the transaction logs, validation issues history and Staging tables. For a MDS system with lot of data changes and ETL processes these tables can grow exponentially and lead to performance degradation and storage space issues.

    In SQL Server 2016 we will provide option to clean these tables on a pre-determined intervals or schedule. Based on the user setting the data in these tables will be truncated.

    What is cleaned?

    All the transaction history older than specified number of days, all the validation issues history older than specified number of days and all the staging batches which ran before specified number of days.

    How:

    There will be settings where user can specify for how many days he wants to retain the logs and data in these tables. He can do this in two places. One using a System Setting (LogRetentionDays) which will apply to all the models in the system. Second ways is to set the property at Model level which will override the system level setting.

    System Setting:

    By default the system setting will be -1 which means do not truncate/clean any tables. If the value is 0 then the tables will retain only today’s data and all the previous days data logs will be truncated. For n > 0 the logs will be retained for n number of days.

    To set this value use the MDS Configuration Manger tool and change the setting shown below.

    clip_image002

    Model level Setting:

    By default the model setting is NULL which means the value will be inherited from the System Setting “Log Retention in Days”. If you want to override system setting and NOT clean any logs then set as -1. If you do not want to retain any transaction logs and validation history and already processed staging data then set as 0. For n > 0 the logs will be retained for n number of days. Acceptable values: -1 to 5000.

    To Set or Update the Model property in the MDS Web UI go to System Administration -> Manage -> Model and change the property shown below

    clip_image003

    Scheduler:

    When you create the database from Configuration Manager, by default it schedules a SQL Agent Job, “MDS_<DB Name>_Log_Maintenance” which triggers every day at 2AM and scans all the models and cleans the table as per the settings above.

    If you want to change the frequency of this job then use the SQL Agent -> Job (MDS_<DB Name>_Log_Maintenance) and update the schedule. You can use SQL Server Management Studio to make any changes to this scheduled job.

    How to manually cleanup the tables without setting a recurring cleanup as above

    You can call the Stored Procedures manually which will clean up the tables. To clean Transaction tables use mdm.udpTransactionsCleanup, to clean Validation History use mdm.udpValidationsCleanup and to clean staging table use mdm.udpEntityStagingBatchTableCleanup.

    Sample:

    DECLARE @CleanupOlderThanDate date = '2014-11-11',

    @ModelID INT = 7

    --Cleanup Transaction Logs

    EXEC mdm.udpTransactionsCleanup @ModelID, @CleanupOlderThanDate;

    --Cleanup Validation History

    EXEC mdm.udpValidationsCleanup @ModelID, @CleanupOlderThanDate;

    --Claenup EBS tables

    EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

    More related information can be found at:

    Transactions (Master Data Services)

    Create a Model (Master Data Services)

    System Settings (Master Data Services)

  • Performance and Scale Improvement in SQL Server 2016

    Overall performance and scale Improvement

    We have made significant improvement on performance from backend database, middle tier services to frontend UI including both WebUI and Excel UI. The performance is improved on all scenarios, especially on the master data loading, while the server side CPU and memory will be lower than previous version.

    The default settings are optimized based on our test data, should be good for most of customer scenarios. There are few settings can be tweaks based on individual scenario.

    1. Data Compression

    We introduce the data compression on entity level. Default setting is enabled.

    clip_image001

    When data compression is enabled, all this entity related tables and indexes are compressed with SQL Row Level compression. It will significant reduce the Disk I/O when read or update the master data, especially when master data has millions of rows and/or has lots of NULL value columns.

    But it will slightly increase the CPU usage on the SQL engine side, as well. Since major of the SQL operation is I/O bound, we set default value as True.

    If you are facing CPU bound on the server, you may turn off data compression by Edit the Entity.

    More information can be found at:

    https://msdn.microsoft.com/en-us/library/cc280449.aspx

    2. Dynamic Data Compression

    We enforced the server enable the feature Dynamic Content Compression IIS feature. The default setting in the service is enable dynamic compression.

    clip_image002

    The MDS API is using xml format. Enable dynamic content compression will significant reduce the size the xml response and save the network I/O, while increase the CPU usage.

    If you are facing CPU bound on the server, you may turn off data compression by put following setting in the web config.

    <configuration>
    <system.webServer>
    <urlCompression doStaticCompression="true" doDynamicCompression="false" />
    </system.webServer>
    </configuration>

    More information can be found at:

    http://www.iis.net/configreference/system.webserver/urlcompression

    Although, both of above change will lower I/O usage by trading off CPU usage, we did great improvement on service side CPU as well. So we are expecting the CPU usage is lower than previous in general, even with compression enabled.

    3. Index maintenance.

    We found index fragmentation and increasing transaction log are the two main reasons why performance is degrading over time in many of user cases.

    We introduce two SQL Job to do index maintenance and log maintenance.

    We have a different blog for Log Maintenance.

    clip_image003

    The default schedule for Index Maintenance job is Weekly, Saturday 2AM. You can change/add more schedule in job property.

    You can also manual kick run the index maintenance by execute store procedure udpDefragmentation.

    Exec [mdm].[udpDefragmentation]

    We recommend to run this sproc to maintain the indexes after each massive master data insert or update, or after new version is created from existing version.

    Index with more than 30% fragmentation will be rebuild online. During the rebuild, the performance will be affected on the CRUD operation on the same table. Run the store procedure at off business hours if the performance degrade is a concern.

    More information about index fragmentation problem can be found at:

    https://msdn.microsoft.com/en-us/library/ms189858.aspx

    For related information refer to:

    Create an Entity (Master Data Services)

    What’s New (Master Data Services)

  • Security Improvements

    Super User Functional permission:

    In the previous release, whoever install the MDS originally, is the server admin. The user will have user id 0. There is no easy way to transfer server admin from one user to the other. It will require DB admin go the user table and change SID on user id 0. There is no easy to have multiple server admins or give server admin permission to a group.

    New Super User functional permission is introduced in this release. Super user has the same permission as user id 0 in the previous releases. It can be assigned in same way that assign other functional permission. It can be assigned to user or group.

    clip_image001

    Model Admin Permission:

    In the previous release, model admin permission is implicitly assigned based on some calculation result. If user only have update permission on the model level and do not have any other permission in the model sub tree, the user is model admin. If someday later, user got another explicit permission assign in the model sub tree, such as entity level, the user will lose model admin permission.

    New explicit Model Admin Permission is introduced in this release. The admin is available on the model level.

    clip_image002

    Granular Access Permission:

    In the previous release, we have Read-Only and Update permission. It is similar to R/RW permission, where RW permission means all the permission including Create Update and Delete.

    And we have been seeing more and more user case requires more granular permission than RW, especially spit delete/create from update. So user can only update the master data but cannot create or delete the data.

    So we introduce 4 granular access permission Read, Create, Update and Delete. They can be used together. For example, Create+Update means user can create and update but cannot delete.

    Since it does not make sense user only have Create, Update or Delete without Read permission, so when assign Create, Update and Delete permission to user, Read is given automatically. For example, assign user Update permission, the UI will show user has Update and Read permission.

    clip_image003

    Comparison:

    Previous Release

    Current Release

    User Id 0

    User has Super User functional permission

    User has update permission on model and doesn’t any permissions in the subtree

    User has Admin Permission on model

    Read-Only

    User has Read Access Permission Only

    Update

    User has all 4 Access Permission.

    Shortcut in UI in All

    Deny

    Deny

     

    The user with old permission will be converted to new permission, during upgrade.

    You can find more information at:

    Administrators (Master Data Services)

    Model Object Permissions (Master Data Services)

    Hierarchy Member Permissions (Master Data Services)

  • Deprecated: Explicit Hierarchies and Collections


        The SQL Server 2016 version of Master Data Services deprecates Explicit Hierarchies (EH) and Collection and all related components. Members that before were modeled as Consolidated (EH parent) and Collection member types will henceforth be modeled as Leaf members in Derived Hierarchies (DH). This is made possible by new DH features that better enable them to take the place of EHs: 

    1.       Recursive Derived Hierarchies (RDH) can now be used to assign member security permissions

    clip_image002[4]

    In the above example, the simple RDH is comprised of a single recursive level representing an Employee->Manager relationship. RDHs can be more complex, by including level(s) below and/or above a recursive level. The analog of an EH is a RDH with a single non-recursive level beneath the recursive level.

     

    Member duplication: But be aware that when a RDH contains a level above a recursive level, members at the recursive level can appear more than once within the hierarchy (under their recursive parent and under their non-recursive parent). Member duplication can also happen when the topmost level is recursive but does not anchor null recursions, in which case each member appears directly under Root and under its non-null parent. The number of times a member appears in such a hierarchy is equal to its maximum depth within the hierarchy. RDHs that allow member duplication cannot be used for member security. Otherwise, a member could inherit permissions along different inheritance paths, resulting in ambiguous resolution.

     

    2.       The DH Explorer page now shows unassigned (unused) members for each hierarchy level:

    clip_image004[4]

    In the above screenshot, the “MyUnusedSubCategory” member of the SubCategory entity is not assigned to a category. That is, the value of its Category attribute is blank, as show in the grid on the right. Before, such orphaned members could not be easily seen in the DH Explorer. But now they and any of their children are shown under the new “Unused” node. Unused nodes are grouped by hierarchy level. Members can be dragged-and-dropped or cut-and-pasted back and forth between the Unused and Root nodes, at the applicable levels.

     

    The Unused node is likewise visible in the Preview pane when managing DH levels from the System Administration page…

    clip_image006[4] 

     

    … and when managing hierarchy member security permissions:

    clip_image008[4]

    Any member, whether under Root or Unused, can be assigned a permission. The Root, Unused, and Unused Level pseudo members can also be assigned permissions.

     

    To reduce UI clutter, the Unused nodes will only be shown when applicable. For example, a hierarchy with only a single level cannot have unused members, because all members appear under root. So the Unused node will not be shown. Likewise for a simple RDH with no levels above or below the recursive level:

    clip_image010[4]

     

     

    For a hierarchy that can only have unused members at a single level (like a DH with two levels or a RDH with a single level below the recursive level), the level-specific unused nodes will not be shown. Instead, all unused members will appear directly under the top-level Unused node. The below screenshot shows the Employee->Manager RDH with a single level below the recursive level, that indicates the Project Managers (PM) for the Project entity.

    clip_image012[4]

     

    3.       Added a stored procedure that converts EHs to RDHs and Consolidated and Collection members to Leaf members: mdm.udpConvertCollectionAndConsolidatedMembersToLeaf

    For this next release, EHs and non-leaf members are deprecated but still fully supported. The conversion sproc is optional and is only executed manually. But in a future release when support for these deprecated item is removed, the conversion sproc will be mandatory and run as part of the db upgrade process. It is recommended that users of EHs and/or Collection members run this sproc as soon as practicable, to get an earlier start in identifying and resolving any issues. Backup the database before running the sproc! See the sproc’s code header for more details about the conversion process, which as of this writing is thus:

    /*

    ==============================================================================

     Copyright (c) Microsoft Corporation. All Rights Reserved.

    ==============================================================================

     

    Converts deprecated collection members and consolidated members to leaf members. Also converts their corresponding deprecated explicit hierarchies (EH) to recursive derived hierarchies (RDH).

     

    Conversion process:

     

    Converting Consolidated members and EHs

    ----------------------------------------

    Suppose there is an entity named Product that contains the following EHs:

       EH Name          Mandatory EH?

      *********        ***************

       Bundle               Yes

       NonMandatory         No

     

    - Create one new entity per EH. The name of the new entity is "<Leaf entity name>_<EH name>". In the above example, two new entities will be created, "Product_Bundle" and "Product_NonMandatory".

        -- Copy consolidated attributes from original EH's entity to leaf attributes on each new entity.

        -- Copy all consolidated members of the EH to leaf members on the corresponding new entity. For mandatory hierarchies that have leaf members directly under ROOT, add a "Pseudo Root" member. For non-mandatory EHs that have unassigned leaf members, leave them as-is.

        -- Add a new self-referencing DBA, named "Parent", to the new entity and set its value to reflect the original consolidated member's parent in the EH.

    - Create new DBAs on the original entity, one per EH on the entity. The name of each DBA will be the EH name. In the above example, two DBAs will be added to the Product entity: Bundle (which references Product_Bundle as its domain entity) and NonMandatory (which references Product_NonMandatory). The value of these DBAs is set to reflect each leaf member's parent in the EH being copied.

    - Copy transactions. EH parent/sibling move transactions are converted to attribute value change transactions. If the @DeleteOriginals flag is set, the original transaction rows will be updated, rather than copied, for efficiency. Otherwise, transaction annotations are also copied.

    - Create one new RDH per EH. Each RDH will have a single non-recursive level beneath the recursive levels. The name of the RDH will follow the same pattern as the new entity

      names, i.e. "<Leaf entity name>_<EH name>", or "Product_Bundle" and "Product_NonMandatory" in the above example.

    - For each existing DH with an EH cap, create a new RDH with (potentially) multiple levels below. The name of the new hierarchy will be "<EH cap DH name>_NoCap".

    - Copy business rules (BR), both rules that applied to the consolidated members being copied and leaf rules that were referencing consolidated parent attributes. In both cases, the consolidated parent attribute references are converted to instead apply to the attributes of the new entities.

     

    Converting Collections.

    For each entity with collection member(s):

    ----------------------------------------

    - Create a new entity "<entity name>_Col". This corresponds to the deprecated "%_CN" table.

        -- Copy collection attributes from original entity to leaf attributes on the new entity

        -- Copy collection members from original entity to leaf members on then new entity

    - Create new entity "<entity name>_ColMem" to track collection membership info. This corresponds to the deprecated "%_CM" table.

        -- Add parent DBA, "Parent_<entity name>_Col", that will reference the parent "collection"

        -- Add child DBAs, that correspond to collection members (exactly one will have a non-null value, per member):

            -- "Child_<entity name>" for leaf members of the collection. References the original entity.

            -- "Child_<entity name>_Col" for collection members of the collection

            -- "Child_<EH name>", one per EH on the original entity, for consolidated members of the collection

        -- Add attributes for "%_CM" table columns Weight and SortOrder

        -- Create new members, one for each "%_CM" table row

    - Copy collection member transactions. If the @DeleteOriginals flag is set, the original transaction rows will be updated, rather than copied, for efficiency. Otherwise, transaction annotations are also copied.

     

    Additional steps

    ----------------------------------------

    - Copy consolidated and collection attribute groups to leaf attribute groups on their corresponding new entities.

    - Copy consolidated and collection subscription views (SV)

            Old ViewFormat_ID            Deprecated?    New ViewFormat_ID

           *******************          *************  *******************

            1 (Leaf members)                No          unchanged

            2 (Consolidated members)       Yes          1 (Leaf members)

            3 (Collection members)         Yes          1 (Leaf members)

            4 (Collection membership)      Yes          1 (Leaf members)

            5 (Explicit Parent-Child)      Yes          7 (Derived Parent-Child)

            6 (Explicit levels)            Yes          8 (Derived levels)

            7 (Derived Parent-Child)        No          unchanged

            8 (Derived levels)              No          unchanged

        For the consolidated-related SVs (ViewFormat_ID 2, 5, and 6) there is a one-to-many mapping. One new SV is created per EH. To disambiguate them, the EH name is appended to the SV name.

    - Security permissions

        -- Copy model object permissions from old entity and its consolidated and collection member types, attributes, and attribute groups, to their corresponding new entity objects.

        -- Copy member permissions from old EHs to new RDHs (collections and DHs with an EH cap cannot be used for member security, so they will have no permissions to copy).

    - If the @DeleteOriginals flag is set, delete the originals of the copied items.

    - Kick off the service broker to process member security.

     

    Additional notes:

    1. EH child sort order info, as stored in the "%_HR" table's SortOrder column, is lost. Derived Hierarchies do not support child sort order.

    2. All copied/converted BRs are left in an unpublished state. The user must manually publish these rules, if desired, after running this script (the publish process can only be executed from the web service API, not from SQL)

    3. Validation issues (as stored in tables tbl_{MID}_VL, tbl_{MID}_VLH)) are not copied. They can be regenerated after BRs are published.

    4. The copy process checks for and handles any naming conflicts that would violate uniqueness constraints. For example, if the script tries to create a new entity named "Product_Bundle", but that name is already used by another entity within the model, then it will try to use "Product_Bundle1" for the new entity name. If that name is already taken, then it will try "Product_Bundle2", and so on.

    5. The copy process preserves original audit info (i.e. created/lastChanged userID/dateTime, etc) for copied items (i.e. master data, DH levels, security permissions, transactions, business rules, etc).

    */

    CREATE PROCEDURE mdm.udpConvertCollectionAndConsolidatedMembersToLeaf

    (

         @Model_ID          INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified model will be operated on.

        ,@Entity_ID         INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified entity will be operated on.

        ,@DeleteOriginals   BIT = 1 -- When 0, the sproc is non-destructive. When 1, the sproc deletes the original collections, consolidated members, EHs, etc that are converted.

    )

    AS BEGIN ...

     

    4 (Collection membership) Yes 1 (Leaf members)

    5 (Explicit Parent-Child) Yes 7 (Derived Parent-Child)

    6 (Explicit levels) Yes 8 (Derived levels)

    7 (Derived Parent-Child) No unchanged

    8 (Derived levels) No unchanged

    For the consolidated-related SVs (ViewFormat_ID 2, 5, and 6) there is a one-to-many mapping. One new SV is created per EH. To disambiguate them, the EH name is appended to the SV name.

    - Security permissions

    -- Copy model object permissions from old entity and its consolidated and collection member types, attributes, and attribute groups, to their corresponding new entity objects.

    -- Copy member permissions from old EHs to new RDHs (collections and DHs with an EH cap cannot be used for member security, so they will have no permissions to copy).

    - If the @DeleteOriginals flag is set, delete the originals of the copied items.

    - Kick off the service broker to process member security.

    Additional notes:

    1. EH child sort order info, as stored in the "%_HR" table's SortOrder column, is lost. Derived Hierarchies do not support child sort order.

    2. All copied/converted BRs are left in an unpublished state. The user must manually publish these rules, if desired, after running this script (the publish process can only be executed from the web service API, not from SQL)

    3. Validation issues (as stored in tables tbl_{MID}_VL, tbl_{MID}_VLH)) are not copied. They can be regenerated after BRs are published.

    4. The copy process checks for and handles any naming conflicts that would violate uniqueness constraints. For example, if the script tries to create a new entity named "Product_Bundle", but that name is already used by another entity within the model, then it will try to use "Product_Bundle1" for the new entity name. If that name is already taken, then it will try "Product_Bundle2", and so on.

    5. The copy process preserves original audit info (i.e. created/lastChanged userID/dateTime, etc) for copied items (i.e. master data, DH levels, security permissions, transactions, business rules, etc).

    */

    CREATE PROCEDURE mdm.udpConvertCollectionAndConsolidatedMembersToLeaf

    (

    @Model_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified model will be operated on.

    ,@Entity_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified entity will be operated on.

    ,@DeleteOriginals BIT = 1 -- When 0, the sproc is non-destructive. When 1, the sproc deletes the original collections, consolidated members, EHs, etc that are converted.

    )

    AS BEGIN ...

    ----

    For More information refer to:

    Derived Hierarchies (Master Data Services)

    Recursive Hierarchies (Master Data Services)

  • Troubleshooting logging improvement

    Troubleshooting logging improvement

    In the SQL Server 2016, we did logging improvement to improve the debug ability and make the troubleshooting easier.

    1.       Tracing setting in the web.config

        <sources>

          <!-- Adjust the switch value to control the types of messages that should be logged.

               http://msdn.microsoft.com/en-us/library/system.diagnostics.sourcelevels

               Use the a switchValue of Verbose to generate a full log. Please be aware that

               the trace file can get quite large very quickly -->

          <source name="MDS" switchType="System.Diagnostics.SourceSwitch" switchValue="Warning, ActivityTracing">

            <listeners>

              <!-- Set a directory path where the service account you chose while setting up Master Data Services has read and write privileges.

                   Default path is Logs in WebApplication folder, for example C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

                   New log file will be created everyday or every 10 mb.

                   When directory size hits the 200mb limitation, the oldest file will be deleted.-->

              <add name="FileTraceListener"

                   type="Microsoft.MasterDataServices.Core.Logging.FileTraceListener, Microsoft.MasterDataServices.Core"

                   initializeData="DirectoryPath = Logs; FileSizeInMb = 10; MaxDirectorySizeInMb = 200"/>

              <remove name="Default"/>

            </listeners>

          </source>

        </sources>

         We have a new section in the web.config for tracing setting.

         By default,

    a.       Tracing is enabled for Warning and below level, plus ActivityTracing.

    b.      The Logs are saved in the Logs folder under the WebApplication folder. For example, C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

    c.       The file file will be created for each day or every 10 mb

    d.      Maximum usage on Logs folder will be 200mb, the oldest log will be deleted.

    e.      The log format is CSV.

    2.       Log format

     

    Time

    When the trace entry happens

    CorrelationId

    One correlation id is assigned for each request. The all the traced trigged by this request will share the same correlation id.

    Operation

    The request operation name. If the request is web ui request, the operation name is the url. If request is API request, the operation name is the service name.

    Level

    The level of this trace entry.

    Message

    The message body of the trace

     

    The log is CSV format and can be opened in Microsoft Excel or other tools for filtering.

     

    3.       Correlation Id

    Each request will be assign a correlation id, the all trace trigger by this request will share the same id.

    When an error happens, in the UI, the correlation id will be shown in the error message.

     

    Web UI:

    clip_image001[6]

     

    Excel Addin UI:

     

    clip_image003[6]

    t.com/en-us/library/system.diagnostics.sourcelevels

    Use the a switchValue of Verbose to generate a full log. Please be aware that

    the trace file can get quite large very quickly -->

    <source name="MDS" switchType="System.Diagnostics.SourceSwitch" switchValue="Warning, ActivityTracing">

    <listeners>

    <!-- Set a directory path where the service account you chose while setting up Master Data Services has read and write privileges.

    Default path is Logs in WebApplication folder, for example C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

    New log file will be created everyday or every 10 mb.

    When directory size hits the 200mb limitation, the oldest file will be deleted.-->

    <add name="FileTraceListener"

    type="Microsoft.MasterDataServices.Core.Logging.FileTraceListener, Microsoft.MasterDataServices.Core"

    initializeData="DirectoryPath = Logs; FileSizeInMb = 10; MaxDirectorySizeInMb = 200"/>

    <remove name="Default"/>

    </listeners>

    </source>

    </sources>

    We have a new section in the web.config for tracing setting.

    By default,

    a. Tracing is enabled for Warning and below level, plus ActivityTracing.

    b. The Logs are saved in the Logs folder under the WebApplication folder. For example, C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

    c. The file file will be created for each day or every 10 mb

    d. Maximum usage on Logs folder will be 200mb, the oldest log will be deleted.

    e. The log format is CSV.

    2. Log format

    Time

    When the trace entry happens

    CorrelationId

    One correlation id is assigned for each request. The all the traced trigged by this request will share the same correlation id.

    Operation

    The request operation name. If the request is web ui request, the operation name is the url. If request is API request, the operation name is the service name.

    Level

    The level of this trace entry.

    Message

    The message body of the trace

    The log is CSV format and can be opened in Microsoft Excel or other tools for filtering.

    3. Correlation Id

    Each request will be assign a correlation id, the all trace trigger by this request will share the same id.

    When an error happens, in the UI, the correlation id will be shown in the error message.

    Web UI:

    clip_image001[4]

    Excel Addin UI:

    clip_image003[4]

    For more information:

    Tracing (Master Data Services)

  • What's New in SQL2016 CTP2 Release


    SQL Server 2016 CTP2 -- Now Available for public download.  In CTP2 release, there are many new MDS features. 

    What’s New for MDS 


    Improved Performance

    Performance improvements enable you to create larger models, load data more efficiently, and get better overall performance. This includes improvement the performance of the add-in for Microsoft Excel has been improved to decrease data load times and enable the add-in to handle larger entities.

    For more information about the add-in for Microsoft Excel, see Master Data Services Add-in for Microsoft Excel.

    The following feature improvements are included.

    • There is data compression on the entity level, which by default is enabled. When data compression is enabled, all the entity related tables and indexes are compressed with SQL Row Level compression. This significantly reduces the disk I/O when reading or updating the master data, especially when the master data has millions of rows and/or has a lot of NULL value columns.

    Because there is a slight increase in the CPU usage on the SQL Server engine side, if you have CPU bound on the server you can turn off data compression by editing the entity.

    For more information, see Create an Entity (Master Data Services)and Data Compression.

    • The Dynamic Content Compression IIS feature is enabled, by default. This significantly reduces the size of the xml response and saves the network I/O, though CPU usage is increased. If you have CPU bound on the server, you can turn off data compression by adding the following setting to the Master Data Services Web.config file.

    Copy

    <configuration>
        <system.webServer>
           <urlCompression doStaticCompression="true" doDynamicCompression="false " />
        </system.webServer>
     </configuration>
     

    For more information, see URL Compression

    • The following new SQL Server Agent jobs do index and log maintenance.

      • MDS_MDM_Sample_Index_Maintenace

      • MDS_MDM_Sample_Log_Maintenace

    By default the MDS_MDM_Sample_Index_Maintenance job runs weekly. You can modify the schedule. You can also manually run the job at any time by using the udpDefragmentation stored procedure. It is recommended that you run the stored procedure each time a large volume of master data is inserted or updated, or after a new version is created from the existing version.

    An index with more than 30% fragmentation is rebuilt online. During the rebuild, the performance is affected on the CRUD operation on the same table. If performance degradation is a concern, it is recommended that you run the store procedure during off business hours. For more information about index fragmentation, see Reorganize and Rebuild Indexes.

    Improved Security

    The new Super User function permission gives a user or group the same permissions as the Server Admin in the previous release of Master Data Services. The Super User permission can be assigned to multiple users and groups. In the previous release, the user who originally installed Master Data Services was the server admin, and it was difficult to transfer this permission to another user or a group. For more information, see Functional Area Permissions (Master Data Services).

    A user can now explicitly be assigned the Admin permission at the model level. This means that if the user is later assigned permissions in the model subtree, such as the entity level, he will not lose this Admin permission.

    In this release of Master Data Services, we’re providing more granular levels of permissions by introducing the following new permissions: Read, Create, Update and Delete. For example, a user that has only the Update permission can now update the master data without creating or deleting the data. When you give a user the Create, Update or Delete permission, the user is automatically assigned the Read permission. You can also combine the Read, Create, Update and Delete permissions.

    When you upgrade to SQL Server 2016 Community Technology Preview 2 (CTP2) Master Data Services, old permissions are converted to new permissions as shown in the following table.

    Permission in previous release

    New permission

    User who originally installs Master Data Services has the Server Admin permission.

    User has the Super User function permission

    User has Update permissions at the model level and no permissions in the model subtree, and so is implicitly a model admin.

    User has explicit Admin permissions at the model level.

    User has Read-only permissions.

    User has Read access permissions.

    User has Update permissions.

    User has all four access permissions: Create, Update, Delete, and Read.

    User has Deny permissions

    User has Deny permissions

    For more information about permissions, see Security (Master Data Services).

    Transaction Log Maintenance

    You can now clean transaction logs at pre-determined intervals or on a schedule, using the System settings and at the model level. For a MDS system with lot of data changes and ETL processes these tables can grow exponentially and lead to performance degradation and storage space issues.

    The following types of data can be removed from the logs.

    • Transaction history older than a specified number of days.

    • Validation issues history older than a specified number of days.

    • Staging batches that ran before a specified number of days.

    You can configure the frequency that data is removed from the transaction logs, using the System settings and at the model level. For more information, see System Settings (Master Data Services)and Create a Model (Master Data Services). For more information about transactions, see Transactions (Master Data Services).

    The SQL Server Agent job, MDS_MDM_Sample_Log_Maintenace, triggers cleanup of the transaction logs and runs every night. You can use SQL Server Agent to modify the schedule for this job.

    You can also call stored procedures to clean the transaction logs. For more information, see Transactions (Master Data Services).

    Improved Troubleshooting

    In SQL Server 2016 Community Technology Preview 2 (CTP2) Master Data Services, features have been added to improve debugging and make it easier to troubleshoot issues. For more information, see Tracing (Master Data Services).

    Improved Manageability

    Improvements in manageability help to lower maintenance costs and positively impact your return on investment (ROI). These improvements include transaction log maintenance and improvements to security, as well as the following new features.

    • Using attribute names that are longer than 50 characters.

    • Renaming and hiding Name and Code attributes.

  • How to cleanup transaction logs, Validation issues and Staging tables

    Master Data Services till recently didn’t have a supported way to clean the transaction logs, validation issues history and Staging tables. For a MDS system with lot of data changes and ETL processes over the period these tables can grow exponentially and lead to performance degradation and storage space issues.

    To overcome this problem in “Cumulative update 15 for SQL Server 2012 SP1we are providing some helper Stored Procedures which users can call to clean the tables.

    What is cleaned?

    All the transaction history older than specified date, all the validation issues history older than specified date and all the staging batches which ran before specified date.

    How:

    You can call the Stored Procedures manually which will clean up the tables. To clean Transaction tables use mdm.udpTransactionsCleanup, to clean Validation History use mdm.udpValidationsCleanup and to clean staging table use mdm.udpEntityStagingBatchTableCleanup.

    Sample:

    --@ModelID is the model ID that you clean up the log for.

    --@CLeanupOlderThanDate is the date before that the logs or records are deleted.

    DECLARE @CleanupOlderThanDate date = '2015-01-01',

       @ModelID INT = 7     --You can get this fromtable mdm.tblModel

    --Cleanup Transaction Logs

    EXEC mdm.udpTransactionsCleanup @ModelID, @CleanupOlderThanDate;

     

    --Cleanup Validation History

    EXEC mdm.udpValidationsCleanup @ModelID, @CleanupOlderThanDate;

     

    --Claenup EBS tables

    EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

    ----------------------

     

    You can also run the cleanup on predetermined schedule by setting up a SQL Agent Job which triggers in regular intervals to call the stored procedures to clean up the data.  You can use SQL Server Management Studio to make any changes to this scheduled job.

    Upcoming Releases:

    These stored procedures will also be release as part of the upcoming cumulative update for SQL Server 2014.

  • Exporting Master Data White Paper Published

    [This post was created by Jim van de Erve of the Content and Community Experience Group, Microsoft.] 

    Microsoft SQL Server 2012 Master Data Services (MDS) enables you to build and manage master data for your operational or analytical needs. But how do you export that master data from MDS into your systems? The "Exporting Master Data from Master Data Services" white paper at http://msdn.microsoft.com/en-us/library/jj218341.aspx shows you how. It describes the subscription views that you can export your master data into, and shows how to use SQL Server Integration Services (SSIS) to import your master data from a subscription view into a SQL Server table. Included is a step-by-step procedure that demonstrates how to set the process up, with sample T-SQL.

    Legal Notice

    © 2012 Microsoft Corporation. All rights reserved. This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only. Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • How to Install Master Data Services (MDS) Service Pack 1 (for SQL Server 2008 R2)

    [This post was created by Jason Howell, Senior Escalation Engineer in the SQL Server Support team specializing in MDS]

    SQL Server 2008 R2 Service pack 1 was released in July 2011, as well as CU#1 for Service Pack 1 a few days afterwards, and CU #2 just yesterday August 15th, 2011.

    The purpose of this blog is to call out the difference in finding and running the download specifically for Service Pack 1, since it is packaged and extracted differently that Cumulative Updates. Other than the download & extraction steps, it is pretty much the same process as other Cumulative Updates for MDS. Steps 1 and 3 are the main differences here as compared to this this prior blog post.

    Installing the Service Pack 1 for MDS requires manual intervention in this release, and running the typical Service Pack 1 setup for the other parts of SQL Server itself is not enough to patch MDS databases and websites.

    If your server does not have an existing MDS installation, you can install a fresh copy of MDS following these steps in MSDN: Installing and Configuring Master Data Services You can use the SP1 MSI to install a fresh copy of MDS if needed.

    If you already have a copy of MDS up and running, you can patch the existing instance with Service Pack 1 updates.

    Always test the update and upgrade process in a non-production environment before applying an update and upgrading the MDS database in your production environment.

    1. Download SQL Server 2008 R2 Service Pack 1

    The first step is to download the service pack.

    You can use this link to download the English version, or change the language as needed. http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727

    For example get the file SQLServer2008R2SP1-KB2528583-x64-ENU.exe

    However, wait until you have read Step 3 to run the download.

    A. It’s worth noting that the MDS Service Pack 1 download package looks different than the CU downloads.

    Service Pack 1 does not have a specific MDS download like the CU’s downloads have. Pick the SP1 for x64.

    clip_image001

    Note: The KB for 2008 R2 Service Pack 1 (KB2528583) is located here: http://support.microsoft.com/kb/2528583

    B. What’s in SQL Server 2008 R2 Service Pack 1?

    Service Pack 1 includes fixes for SQL Server 2008 R2 CU1 through CU6 plus a few other fixes that are not in the RTM CU’s. Read the Kb for an exact list of the additional fixes not in CU1-CU6.

    Moving forward, fixes made in the RTM cumulative updates are also ported forward to the next subsequent cumulative update for SP1. However, fixes made to the SP1 cumulative updates will NOT be ported back to the RTM unless there is express need to do so. This makes SP1 updates favorable moving forward to get the most fixes.

    The green cumulative updates for MDS are included in Service Pack 1. The orange items below are carried forward in CU#1 for SP1. To get the latest updates, then use SP1 CU2 instead of SP1 itself. The list grows every 2 months, so refer to this list if in doubt which one is latest.

    • RTM & Updates
      • 10.50.1600.1 Release to Manufacturer – May 10, 2010
      • 10.50.1617.0 RTM Security Patch MS11-049 (2494088) June 14, 2011
      • 10.50.1702.0 RTM CU #1 (981355) May 18, 2010
      • 10.50.1720.0 RTM CU #2 (2072493) June 21, 2010
      • 10.50.1734.0 RTM CU #3 (2261464) Aug 16, 2010
      • 10.50.1746.0 RTM CU #4 (2345451) Oct 18, 2010
      • 10.50.1753.0 RTM CU #5 (2438347) Dec 20, 2010
      • 10.50.1765.0 RTM CU #6 (2489376) Feb 21, 2011
      • 10.50.1777.0 RTM CU #7 (2507770) Apr 18, 2011
      • 10.50.1790.0 RTM CU Security Patch MS11-049 (2494086) June 14, 2011
      • 10.50.1797.0 RTM CU #8 (2534352) June 20, 2011
      • 10.50.1804.0 RTM CU #9 (2567713) Aug 15, 2011
      • etc…
    • SERVICE PACK 1 & Updates
      • 10.50.2500.0 SP1 Release to Web (2528583) July 13, 2011 (contains RTM CU6 + others)
      • 10.50.2769.0 SP1 CU #1 (2544793) July 18, 2011 (contains RTM CU7 - RTM CU8)
      • 10.50.2772.0 SP1 CU #2 (2567714) Aug 15, 2011
      • etc…

    C. Instead of SP1 itself, you can alternately download CU2 (or later) for Service Pack 1

    Instead of SP1 itself, you could run CU1 or CU2 (or later CU) for SP1 if you have business requirements to do so. There is a specific MDS file available for download for each CU, which contains only the piece to patch Master Data Services.

    Since the MDS download contains a *.msi, it is unlike the rest of SQL feature area CU’s which use *.msp patches that require SP1 as a prerequisite to installing. Therefore you could use the SP1 CU2 file for a fresh install of MDS.

    clip_image003

    When you fill out the request page, as shown above the hotfix website will send you an email with the link to download the CU update.

    2. MDS Preparations – stop current activity, backup your MDS Database, check the starting version

    The upgrade process is two-fold: First, Upgrade the binaries. Secondly, upgrade the database schema

    Therefore, before we begin, let’s stop all current activity so that we have a clean and safe copy of the MDS database in a consistent state, and make a backup of the MDS database and transaction log to be sure we have a safe copy just in case.

    A. Stop the IIS web site or application pool.

    This will keep users out of the system while we do the upgrade maintenance. You may want to alert the MDS users about the scheduled maintenance according to your businesses' change control processes.

    Open Internet Information Services (IIS) Manager and stop the Master Data Manager Web application and the MDS Web service (if enabled). You can do this either by stopping the application pool that contains the Web application and service, or you can stop the Web site that contains them. If you stop the application pool, you stop requests for all applications in that application pool. If you stop the Web site, you stop only traffic to the Web site and any applications and services it contains.

    clip_image004OR clip_image005

    B. Stop any query or maintenance operations

    Such as scheduled jobs, reports, or custom applications that interact with the MDS database directly.

    You could stop your SQL Agent service temporarily if unsure about scheduled jobs that might affect MDS data.

    C. Backup the MDS database and the transaction log

    You get to pick the name of your MDS database when you originally set it up with the Configuration Manager, so your database name may vary.

    If you use FULL recovery model on the MDS database, you can back up the transaction LOG also.

    Use a TSQL query or SSMS backup as you please.

    BACKUP DATABASE MDS TO DISK='C:\mybackups\mds_before_sp1.bak'
    BACKUP LOG MDS TO DISK='C:\mybackups\mds_log_before_sp1.trn'
    Object Explorer in SQL Server Management Studio:

    clip_image006

    D. Query mdm.tblSystem in the current MDS database.

    Review value for SchemaVersion and note the value, so you can compare the value after you apply the MDS update to verify that the value has been increased. The SchemaVersion may vary based on the build you are starting with. Some MDS Patches do no schema upgrades, but some do. Depending on which version you start from, Service Pack 1 may or may not upgrade the schema.

    USE MDS
    GO
    SELECT * FROM mdm.tblSystem

    3. Run the Service Pack 1 executable and msi’s

    The SP1 download SQLServer2008R2SP1-KB2528583-x64-ENU.exe is a self-extracting zip. You can run SP1 for all of SQL if you want (see step 3A) or manually extract just the Master Data Services *.msi if needed (see step 3B).

    Double-clicking the .exe extracts SP1 to a temp folder on the root of the drive

    clip_image007

    After extracting, it will prompt you with security UAC to run the service pack 1 setup.

    A. Option 1: Run SP1 for all SQL Server feature areas first, then find the MDS msi.

    If you need to patch all of the features of your SQL Server with Service Pack 1, follow the normal steps in the GUI.

    Running SP1 will extract the MasterDataServices.msi file but it will not run it automatically.

    After you have finished patching SQL Server 2008 R2 with Service Pack 1, you can then find the MSI for MDS in the setup bootstrap folder. It’s kept here for caching purposes:

    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cache\KB2528583\ServicePack\1033_enu_lp\x64\setup\ masterdataservices.msi

    On my computer it is in this folder, but I imagine the language (1033=English) may vary based on which SQL locale you have used in the installation.

    Within that folder, click the MSI to run the MDS SP1 setup masterdataservices.msi

    B. Option 2: Manually Extract SP1 if you do not need to patch any other parts of SQL.

    If you don’t need to patch the other features of SQL Server, or maybe you need to just patch MDS as a separate step, you can manually extract the Service Pack 1 and get to the MSI. Several 3rd party compression tools let you right click on the .exe and extract it to a folder of your choosing as well.

    From a command line, you can run:

    SQLServer2008R2SP1-KB2528583-x64-ENU.exe /extract

    clip_image008

    clip_image009

    Find the MDS msi in the specified location, in the subfolder:
    <Extracted folder>\1033_enu_lp\x64\setup\masterdataservices.msi

    clip_image010

    4. Run the masterdataservices.msi manually (like any other MDS update)

    clip_image011

    clip_image012

    Run through the installation until it completes

    .clip_image013

    Note: If you start the MDS website at this point and visit it, you may get a version incompatible error. This is expected behavior when the schema version increases because the MDS binaries are upgraded, but the database is not yet upgraded. Users cannot use MDS yet if you see this message.

    Client Version: 10.51.2500.0
    Database Version: 1.0.0.0
    The Client version is incompatible with the Database version. Please ask your administrator to upgrade the Client components or the Database components or both.

    clip_image014

    Note the binaries and scripts are upgraded already to 10.50.2500.0 (the build number of R2 Service Pack 1)

    Binaries are the *.exe or *.dll in the Master Data Services installation folders such as

    C:\Program Files\Microsoft SQL Server\Master Data Services\Configuration\

    C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin

    When you right click on them in Windows Explorer, and view the properties, you can check the file version:

    clip_image015

    5. The Master Data Services Configuration Manager may need to run to complete the MDS database schema upgrade.

    A. Run Configuration Manager

    It will try to run automatically after the setup completes, or you can run it later from the start menu. Complete the additional steps therein to upgrade your MDS Database and your MDS Websites.

    clip_image016

    B. Connect and select your database

    In the MDS Configuration Manager, click the [Select Database…] button.

    Get connected to your MDS database on whichever SQL Server it may reside on. Note if you utilize a named instance, you have to manually type in the servername\instancename because the configuration manager forgets which instance to connect to every time.

    clip_image017

    C. Upgrade your MDS database if needed

    If you see the below red error text "This database requires an upgrade. You cannot change system settings until the database is upgraded" then you need to upgrade your MDS database.

    Sometimes you do not see this text, if you already had the schema upgrades are already in place from a prior cumulative update patch, then no further action is required, skip to step 6.

    If you get the error after you picked the database server and the existing database name, in the MDS Configuration Manager, use the [Upgrade Database…] button to upgrade the database.

    clip_image018

    D. Click through the Upgrade Database Wizard

    When you run the Upgrade, you will see several screens in the upgrade wizard. It will show which schema upgrades need to happen. It’s easy, so I’ll just show the progress bar:

    clip_image019

    E. Now check the database SchemaVersion in your MDS database by running the query to confirm the schema is now upgraded.

    I expect 1.0.7.0 for Service Pack 1.

    USE MDS
    GO
    SELECT * FROM mdm.tblSystem

    clip_image022

    6. Start the MDS website and Application Pool and make sure it works.

    A. Start the web site and application pools

    After the database upgrade is complete, your users can start using the MDS website again. Start the Website and or Application Pools, and remember to enable any SQL Agent jobs or other maintenance you may have disabled for the upgrade timeframe.

    clip_image020clip_image021

    B. Browse through your MDS website to make sure the models are present, and that things seem normal.

    You can get IIS to help you if you forgot the URL:

    clip_image023

    You can confirm the version number in the Help menu (blue question mark on the upper right of on the web page).

    The MDS website shows the version as 10.51.2500.0 for Service Pack 1.

    clip_image024

    clip_image025

  • New Master Data Services Book Available

    A new Master Data Services book is now available on amazon.com.

    Microsoft SQL Server 2008 R2 Master Data Services;  by Tyler Graham and Suzanne Selhorn;  ISBN 007175623X

     

    This book was written by two members of the Microsoft MDS team and is a resource and reference for using the SQL Server 2008 R2 version of Master Data Services. A Kindle version is also available.

    An updated version of the book will soon be available for SQL Server 2012:

    Microsoft SQL Server 2012 Master Data Services 2/E; by Tyler Graham and Suzanne Selhorn; ISBN 0071797858 

  • New Master Data Services Training Available

    [posted by Suzanne Selhorn, Technical Writer on the MDS team]

    Some new self-paced training is now available on the Microsoft Download Center. To take advantage of this training, you should have a working installation of MDS with sample data already loaded.

    01 Introduction
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/01 Introduction.pdf
    This lesson provides an overview of MDS.

    02 MDS Environment
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/02 MDS Environment.pdf
    This lesson provides an overview of MDS terminology and features of the Master Data Manager web application.

    03 Managing Entities
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/03 Managing Entities.pdf
    In this lesson, MDS terminology is explored in more depth. You start working with data and learn how to use the Master Data Manager web application to do common tasks.

    04 Managing Hierarchies
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/04 Managing Hierarchies.pdf
    This lesson provides information about all of the available hierarchy types in MDS. You practice adding, removing, and moving members in hierarchies.

    05 Creating Collections
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/05 Creating Collections.pdf
    This lesson shows you how to create collections, and how to add and remove items from a collection.

    06 Version Management
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/06 Version Management.pdf
    This lesson explains version management and shows you how to view and reverse transactions.

    07 Modeling Guidelines
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/07 Modeling Guidelines.pdf
    This lesson gives you ideas for how to use MDS to model your organization's data. It prepares you for the next lesson, where you will use the knowledge you've gained to start creating your model.

    08 Model Building
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/08 Model Building.pdf
    In this lesson, you will use the knowledge from Lesson 07 to begin creating a model for your data.

  • Reserved Words

    (This was posted by Suzanne Selhorn, Technical Writer on the MDS team) 

    People often ask about which words are reserved—which words they shouldn’t use when creating model objects or words they shouldn't use for members. Here is a list of words you should not use.

    Model object reserved words
    Don't use these for names of objects (models, entities, attributes, version flags, subscription views, etc).

    ID LastChgUserName
    Code LastChgVersionID
    Name LastChgVersionNumber
    EDMWeight MDMMemberStatus
    EDMMemberStatus Status
    EnterDateTime Status_ID
    EnterDTM ValidationStatus
    EnterUserID ValidationStatus_ID
    EnterUserName Version_ID
    EnterVersionNumber       VersionFlag
    LastChgDateTime VersionName
    LastChgDTM VersionNumber
    LastChgUserID

    Member reserved words
    Don't use these words for members.

    Root EDMMemberStatus
    MDMUnused EDMUnused
    MDMMemberStatus EDMWeight
  • Downloading and Installing SQL Server 2008 R2 Master Data Services (MDS) Cumulative Updates

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)

    Note: This article is applicable to Microsoft SQL Server 2008 R2 Master Data Services feature. SQL Server 2012 now ships Master Data Services on the DVD with the other SQL Server 2012 features and is patched by the same Cumulative Updates and Service Packs for SQL Server 2012 and the downloads are no longer specific to MDS for separate download .

    Recently, we released the first cumulative update (CU) for Master Data Services (MDS). This blog entry explains how to download and install that CU, as well as subsequent CUs for MDS.

    Each Master Data Services (MDS) cumulative update is released as a stand-alone installer for SQL Server 2008 R2 Master Data Services. An update can be installed on a server without a previous installation of MDS, or it can be applied over an existing RTM installation or previously updated installation of MDS.

    Note MDS is a feature of the versions and editions of SQL Server listed in Setup Requirements (Master Data Services) in SQL Server Books Online (BOL). Any computer where you install MDS must be licensed accordingly. For more information, refer to the End User License Agreement (EULA).

    To download an MDS Update:

    1. At the top of the Knowledge Base page for the download, click View and request hotfix downloads.
    2. Read the terms of the agreement and click I Agree if you agree and want to continue.
    3. On the Hotfix Request page, select the necessary file to obtain this update.
    4. Specify an e-mail address to e-mail this update to, and type the characters in the picture. Then click Request hotfix.
    5. On the Hotfix Confirmation page, review the information and wait for the hotfix e-mail to arrive.
    6. From the hotfix e-mail, open the link and download the .Zip file.
    7. Extract the .Zip file and provide the password included in the hotfix e-mail. MasterDataServices.msi will be extracted to the location you specify.

    Installing MDS cumulative updates on a server without an existing MDS installation

    If your server does not have an existing MDS installation, you can follow the process documented in Installing and Configuring Master Data Services in SQL Server BOL. However, instead of running MasterDataServices.msi from the product media as directed in the documentation, run MasterDataServices.msi from the location where you extracted it in the download process above. Then create and configure the MDS database, Master Data Manager Web application, and MDS Web services as documented in SQL Server BOL.

    Applying MDS cumulative updates to an existing MDS installation

    If you apply an MDS cumulative update to an existing installation of MDS, you must run the installation package and then use Master Data Services Configuration Manager to upgrade the database.

    Important   Always test the update and upgrade process in a non-production environment before applying an update and upgrading the MDS database in your production environment.

    Before you start the upgrade process:

    • Open Internet Information Services (IIS) Manager and stop the Master Data Manager Web application and the MDS Web service (if enabled). You can do this either by stopping the application pool that contains the Web application and service, or you can stop the Web site that contains them. If you stop the application pool, you stop requests for all applications in that application pool. If you stop the Web site, you stop only traffic to the Web site and any applications and services it contains.
    • Stop any operations, such as scheduled jobs, that affect the MDS database.
    • Query mdm.tblSystem in the current MDS database. Review value for SchemaVersion and compare the value after you apply the MDS update to verify that the value has been increased.
    • Take a current backup of the MDS database. If you are using the full recovery model, take a log backup immediately before you upgrade the database. If you are using the simple recovery model, take a full or differential database backup. For more information about backups, see “Backup Overview (SQL Server)” at http://msdn.microsoft.com/library/ms175477(SQL.105).aspx.

    To apply an MDS cumulative update:

    1. On the server where you want to install the update, navigate to the location where you extracted MasterDataServices.msi and double-click it.
    2. The following message appears: A lower version of this product has been detected on your system. Would you like to upgrade your existing installation? Click Yes to proceed with the upgrade process.
    3. The Microsoft SQL Server 2008 R2 Master Data Services installation wizard opens. Follow the instructions in the wizard to configure installation options and then click Install.
    4. After installation completes, Master Data Services Configuration Manager opens so you can upgrade the MDS database.
    5. Click Databases, and then click Select Database. Connect to the database you want to upgrade. Once the database is selected, the following message appears: This database requires an upgrade. You cannot change system settings until the database is upgraded.
    6. Click Upgrade Database. The Upgrade Database Wizard opens. Follow the instructions in the wizard to upgrade the database and then click Finish.
    7. To verify that the database upgrade was successful, connect to the MDS database and query mdm.tblSystem. The value for SchemaVersion should be increased and match the value as specified for the individual cumulative update.

    If the update was unsuccessful, open Control Panel and use Programs and Features to uninstall Microsoft SQL Server 2008 R2 Master Data Services and then re-run the steps in this procedure.

    After you apply the cumulative update and upgrade the database, start the Master Data Manager Web application, MDS Web service (if enabled in your environment), and any operations that you suspended prior to the upgrade process. Verify that your environment works as expected before you allow users to access the environment.

More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement