I need to keep synchronized a couple of databases, one is on sql 2000 and the other on 2005. The one on 2000 should be kept in read-only mode to make sure the users does not enter data. The 2005 is the one which are going to be updated by the users. So I could develop a script to truncate and insert into the 2000 version with data from 2005 every night. My question is if there is some way to disable the read-only mode temporaly while the script is running. Is there a better approach? Thanks,
我需要保持几个数据库的同步,一个在sql 2000上,另一个在2005上。2000上的数据库应保持只读模式以确保用户不输入数据。 2005年将是由用户更新的。所以我可以开发一个脚本来截断并插入2000版本,每天晚上都有2005年的数据。我的问题是,在脚本运行时是否有某种方法暂时禁用只读模式。有更好的方法吗?谢谢,
3 个解决方案
#1
2
You can disable the read-only mode while the script is running. You may also want to set RESTRICTED_USER to keep any users from accessing the database while processing.
您可以在脚本运行时禁用只读模式。您可能还需要设置RESTRICTED_USER以防止任何用户在处理时访问数据库。
#2
1
I would tend along the response offered by Mike Henderson. Essentially leverage db security to prevent users from modifying data and allow the account used to synchronize data to write.
我会倾向于迈克亨德森的回应。本质上利用数据库安全性来防止用户修改数据并允许用于同步数据的帐户进行写入。
Not sure what your time constraints are, but if you are relying on the db to be read-only to prevent end-users making changes, there is the possibility that they could get in during the time period that the db is in read/write mode.
不确定你的时间限制是什么,但如果你依赖数据库是只读的,以防止最终用户进行更改,那么他们有可能在数据库处于读/写的时间段内进入模式。
#3
0
Just for future reference, this is how I proceeded,
仅供将来参考,这就是我的进展方式,
ALTER DATABASE MyDB SET READ_WRITE WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDB SET READ_ONLY WITH ROLLBACK IMMEDIATE
Thank you,
#1
2
You can disable the read-only mode while the script is running. You may also want to set RESTRICTED_USER to keep any users from accessing the database while processing.
您可以在脚本运行时禁用只读模式。您可能还需要设置RESTRICTED_USER以防止任何用户在处理时访问数据库。
#2
1
I would tend along the response offered by Mike Henderson. Essentially leverage db security to prevent users from modifying data and allow the account used to synchronize data to write.
我会倾向于迈克亨德森的回应。本质上利用数据库安全性来防止用户修改数据并允许用于同步数据的帐户进行写入。
Not sure what your time constraints are, but if you are relying on the db to be read-only to prevent end-users making changes, there is the possibility that they could get in during the time period that the db is in read/write mode.
不确定你的时间限制是什么,但如果你依赖数据库是只读的,以防止最终用户进行更改,那么他们有可能在数据库处于读/写的时间段内进入模式。
#3
0
Just for future reference, this is how I proceeded,
仅供将来参考,这就是我的进展方式,
ALTER DATABASE MyDB SET READ_WRITE WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDB SET READ_ONLY WITH ROLLBACK IMMEDIATE
Thank you,