m*********y
发帖数: 389 | 1 我想写一个query,which will check 3 copies of the same databases, check the
user log in are set up properly, check all the fields are in sync and all
indexes are in sync. Basically, my goal is to make sure all 3 databases on 3
different servers are identical copies.. For some reasons, they never are!
I want the check results to be sent to the group via email and list all the
variances, if there are any, for instance, if a field exist on one copy but
not on the other 2 copies..
I know I can do it in SSIS package if I want to receive emails. Just want to
know how this is archieved in your company/group.
Many thanks. | a9
发帖数: 21638 | 2 they never are
你还查股子啥劲儿啊。
3
!
the
but
to
【在 m*********y 的大作中提到】
: 我想写一个query,which will check 3 copies of the same databases, check the
: user log in are set up properly, check all the fields are in sync and all
: indexes are in sync. Basically, my goal is to make sure all 3 databases on 3
: different servers are identical copies.. For some reasons, they never are!
: I want the check results to be sent to the group via email and list all the
: variances, if there are any, for instance, if a field exist on one copy but
: not on the other 2 copies..
: I know I can do it in SSIS package if I want to receive emails. Just want to
: know how this is archieved in your company/group.
: Many thanks.
| m*********y
发帖数: 389 | 3 they should be in sync.. but there are different processes running on the 3
copies which result in the 3 copies having different fields, indexes etc..
Anyone know how this can be archieved? | i****a
发帖数: 36252 | 4 look into merge replication
3
!
the
but
to
【在 m*********y 的大作中提到】
: 我想写一个query,which will check 3 copies of the same databases, check the
: user log in are set up properly, check all the fields are in sync and all
: indexes are in sync. Basically, my goal is to make sure all 3 databases on 3
: different servers are identical copies.. For some reasons, they never are!
: I want the check results to be sent to the group via email and list all the
: variances, if there are any, for instance, if a field exist on one copy but
: not on the other 2 copies..
: I know I can do it in SSIS package if I want to receive emails. Just want to
: know how this is archieved in your company/group.
: Many thanks.
| m*********y
发帖数: 389 | | g***l
发帖数: 18555 | 6 MERGE慢的很，不NSYNC是什么原因?看看PEER TO PEER REPLICATION吧 | g***l
发帖数: 18555 | 7 交易型数据库的特点就是CETRALIZED, NORMAL FORM， REDUCE REDUDANCY，为什么弄出
三个SOURCE来呢？如果不一样，你的CONFLICT RESOLUTION是什么呢？每一种情况都要
考虑到， | m*********y
发帖数: 389 | 8 为什么要弄出3个source呢？
一个是development server, 我们要run很多vendor software 在dev server上，让数
据更干净更standardized；一个是production server,外部用户直接连上去进行下载和
御览；再有一个作为back up,给印度的developer们做测试用。这三个服务器，原则上
必须in sync。但是主要问题是，我们的swap process通常不能完整的拷贝dev server
到production server上去，导致我们在dev server上做的改进没有体现出来，原因很
多，有时候是因为我们运行的process间隔时间很短，负责swap的人忘记了swap,有时候
即使swap了，那个swap的script没有反映新的变化，比如加了新的column，而swap
script里面根本没有新的column，有时候是swap process was timed out， 而
operator都不知道。总而言之，这个人工的swap导致很多时候3个server not in sync.
..
我就是想找一个程序，或者写一个scheduled task,抓出所有dev server和production
server之间不sync的地方..merge replication looks doable, 但是很可能一个晚上只
能sync大约5到6个数据库，而我的目的是把上一个服务器上上百个数据库都sync起来。 | y****w
发帖数: 3747 | 9 大家都以为你说的是prod server.
你需要的是一个schema comparison tool. quest的工具有这个功能． 我以前也用过
，有点小问题，后来还是自己写脚本去比较． 搞清楚你要比较什么是最重要的，实现
一般不难查系统表就行了．
server
sync.
【在 m*********y 的大作中提到】
: 为什么要弄出3个source呢？
: 一个是development server, 我们要run很多vendor software 在dev server上，让数
: 据更干净更standardized；一个是production server,外部用户直接连上去进行下载和
: 御览；再有一个作为back up,给印度的developer们做测试用。这三个服务器，原则上
: 必须in sync。但是主要问题是，我们的swap process通常不能完整的拷贝dev server
: 到production server上去，导致我们在dev server上做的改进没有体现出来，原因很
: 多，有时候是因为我们运行的process间隔时间很短，负责swap的人忘记了swap,有时候
: 即使swap了，那个swap的script没有反映新的变化，比如加了新的column，而swap
: script里面根本没有新的column，有时候是swap process was timed out， 而
: operator都不知道。总而言之，这个人工的swap导致很多时候3个server not in sync.
| m*********y
发帖数: 389 | 10 Thanks for your insight, I know exactly what I want to achieve, just not
sure if there are better way to do it.. here is some queries I wrote for
checking inconsistencies across the same db on different servers.
Is Quest a tool within SQL server tuner?
Anyway, i have these un-sync issues that I want to check:
1.Log in
2.column name
3.index
4.total rows
5.total size of the table
I want to check these things on Dev server and Production server, and then I
want the production team and DBA and operators get an email alert whenever
one of the above mentioned things are out of sync so I can go ahead and fix
it.
Can Quest do that?
So here are the test script I wrote for checking these things, can someone
tell me if it be optimized?
--checking log in on Dev
use DEV_DB
DECLARE @SqlStatement nvarchar(4000)
Declare @loginName varchar (100)
Select @loginName = 'HQ\Production'
If not Exists (select loginname from master.dbo.syslogins where name = @
loginName and dbname = 'DEV_DB')
Begin
Set @SqlStatement = 'CREATE LOGIN [' + @loginName + '] FROM WINDOWS WITH
DEFAULT_DATABASE=[DEV_DB], DEFAULT_LANGUAGE=[us_english]'
EXEC sp_executesql @SqlStatement
End
--checking if a field has been created, if not, create it
use dev_db
print '--**check dev_db fields creation **--'
if exists (select name from syscolumns where name in ('new_column_2012') )
print 'field new_column_2012 exists' else print '!!field new_column_2012
DOES NOT EXIST!!'
if not exists (select name from syscolumns where name in ('new_column_2012'
) )
alter table mn_voters add new_column_2012 CHAR(2) NOT NULL DEFAULT ''
ELSE PRINT 'Field new_column_2012 already exists'
The bottle neck here is that I want all the alerts to be sent to production
team, because I won't be able to get up at 2am to fix inconsistency but our
outsea team can do that..
Any more insights? I greatly appreciate your ideas! Thanks!
【在 y****w 的大作中提到】
: 大家都以为你说的是prod server.
: 你需要的是一个schema comparison tool. quest的工具有这个功能． 我以前也用过
: ，有点小问题，后来还是自己写脚本去比较． 搞清楚你要比较什么是最重要的，实现
: 一般不难查系统表就行了．
:
: server
: sync.
| y****w
发帖数: 3747 | 11 automatic job,
send email by SMTP module,
good enough?? 你呼唤下zenny等sql大牛吧．
exec master.dbo.sp_SQLNotify ‘s****[email protected]’,'admin@localserver.
com’,'Backup Job Failure,’The Backup Job failed’
【在 m*********y 的大作中提到】
: Thanks for your insight, I know exactly what I want to achieve, just not
: sure if there are better way to do it.. here is some queries I wrote for
: checking inconsistencies across the same db on different servers.
: Is Quest a tool within SQL server tuner?
: Anyway, i have these un-sync issues that I want to check:
: 1.Log in
: 2.column name
: 3.index
: 4.total rows
: 5.total size of the table
| i****a
发帖数: 36252 | 12 1st, I think 印度 QA database should never be a data source. How big is your
database? Is backup from production and restore to 印度 QA server possible?
If it is then you only have 2 data sources to worry about. (if not, just
snapshot or transactional one-way replication from prod to QA)
And ideally, you should have a "staging" database to run vendor software for
data clean up, then replicate it back on to production server. You can
refresh staging database by backup form prod and restore, if possible allows.
if you can separate data cleaning and code development, then it's again
simpler and cleaner.
so sounds to me it's not purely a technical issue. I think this is better
solved by improving your "release" process.
server
sync.
【在 m*********y 的大作中提到】
: 为什么要弄出3个source呢？
: 一个是development server, 我们要run很多vendor software 在dev server上，让数
: 据更干净更standardized；一个是production server,外部用户直接连上去进行下载和
: 御览；再有一个作为back up,给印度的developer们做测试用。这三个服务器，原则上
: 必须in sync。但是主要问题是，我们的swap process通常不能完整的拷贝dev server
: 到production server上去，导致我们在dev server上做的改进没有体现出来，原因很
: 多，有时候是因为我们运行的process间隔时间很短，负责swap的人忘记了swap,有时候
: 即使swap了，那个swap的script没有反映新的变化，比如加了新的column，而swap
: script里面根本没有新的column，有时候是swap process was timed out， 而
: operator都不知道。总而言之，这个人工的swap导致很多时候3个server not in sync.
| g***l
发帖数: 18555 | 13 我们都是做ENVIRONMENT REFRESH的，一周一次就固定了，不可能老跟着PRODUCTION变
，这样没法QA了。这种INCREMENTAL REFRESH需要用ETL来做，
当然不能保证SCHEMA NSYNC。其实BACKUP RESTORE是最简单的，但不能是天天都做。 |
|
