locked
buffer database RRS feed

  • Question

  • hi, i actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
    Thursday, October 29, 2015 4:01 PM

Answers

  • Hi ludovick,

    It seems that there is only one SQL Server in your scenario and you want to prevent any incorrect operations when partners insert data to main database, right?

    If that is the case, you can grant partners read-only(db_datareader) permission to the main database, then let database administrator to insert data into main database, in this circumstance, you don’t have to create buffer database.

    Regards,
    Ice Fan


    Ice Fan
    TechNet Community Support


    Friday, October 30, 2015 9:29 AM
  • Other Option:

    Create a new schema in your database, create same table in this new schema and grant permissions on the schema for your partners, then they can use These tables as they did before with the origin tables; you can check the new data and if the OK, you can copy them over.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 30, 2015 9:33 AM
    Answerer
  • Basically, your partner inserting data in temporary database and you copy data from temporary database to your main database, am I right?

    How your partner accessing your temporary database, does he directly have access? Its not good design to give directly access to database. You should develop some kind of API to insert the data.

    How do you copy your data from temporary database to main database? Do you do any validation?

    The right architecture should be like this.

    1. Create one central database.

    2. Create API to access the database; and insert/delete/update operations. (You can use MVC Web API)

    Web API 

    3. Add authentication in API for each partner/user.

    4. Tell your partner use the API to insert the data.

         This way you don't have to copy any data from one to another. Add some validation in your API. This is industry standard architecture.

    Friday, October 30, 2015 7:55 PM

All replies

  • Hi ludovick,

    It seems that there is only one SQL Server in your scenario and you want to prevent any incorrect operations when partners insert data to main database, right?

    If that is the case, you can grant partners read-only(db_datareader) permission to the main database, then let database administrator to insert data into main database, in this circumstance, you don’t have to create buffer database.

    Regards,
    Ice Fan


    Ice Fan
    TechNet Community Support


    Friday, October 30, 2015 9:29 AM
  • Other Option:

    Create a new schema in your database, create same table in this new schema and grant permissions on the schema for your partners, then they can use These tables as they did before with the origin tables; you can check the new data and if the OK, you can copy them over.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 30, 2015 9:33 AM
    Answerer
  • Basically, your partner inserting data in temporary database and you copy data from temporary database to your main database, am I right?

    How your partner accessing your temporary database, does he directly have access? Its not good design to give directly access to database. You should develop some kind of API to insert the data.

    How do you copy your data from temporary database to main database? Do you do any validation?

    The right architecture should be like this.

    1. Create one central database.

    2. Create API to access the database; and insert/delete/update operations. (You can use MVC Web API)

    Web API 

    3. Add authentication in API for each partner/user.

    4. Tell your partner use the API to insert the data.

         This way you don't have to copy any data from one to another. Add some validation in your API. This is industry standard architecture.

    Friday, October 30, 2015 7:55 PM
  • Make sure you also created  PK,FKs to ensure referential integrity in the database to prevent inconstantly data... To enforce uniqueness create PK or UNIQUE constraints... 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, November 1, 2015 8:50 AM