I have a mirrored database and I need to make some changes to it. Mainly, adding a view and some stored procedures. Now I know that if you make schema changes you are supposed to remove mirroring and the mirror database, make your changes to the principal, then backup-restore the principal and restore mirroring. Is this the case for stored procedures and views as well? Can I just make my changes to the Principal, then Failover and make my changes to the mirror as well? What are the possible issues and ramifications from doing something like this? It just seems like a very tedious task to have to do every time we want to make a small change, mainly because the database is over 10 gigs so it takes a little while to back up and restore.
我有一个镜像数据库,我需要对它进行一些更改。主要是添加视图和一些存储过程。现在我知道如果你进行架构更改,你应该删除镜像和镜像数据库,对主体进行更改,然后备份 - 恢复主体并恢复镜像。这是存储过程和视图的情况吗?我可以只对Principal进行更改,然后进行故障转移并对镜像进行更改吗?做这样的事情有什么可能的问题和后果?每次我们想要进行一些小改动时,这似乎是一项非常乏味的任务,主要是因为数据库超过10场演出所以需要一点时间进行备份和恢复。
3 个解决方案
#1
11
If you are mirroring, you should be able to apply those changes to the principal and they show up on the mirrored side. If you want there to be schema difference between the two, you can't use mirroring. Are you looking to apply schema changes on just the mirrored database or to both? If both then you don't have to do anything special.
如果您正在镜像,则应该能够将这些更改应用于主体,并且它们将显示在镜像端。如果您希望两者之间存在架构差异,则无法使用镜像。您是否希望仅在镜像数据库或两者上应用架构更改?如果两者都没有做任何特别的事情。
#2
2
Now I know that if you make schema changes you are supposed to remove mirroring and the mirror database, make your changes to the principal, then backup-restore the principal and restore mirroring
现在我知道如果你进行架构更改,你应该删除镜像和镜像数据库,对主体进行更改,然后备份 - 恢复主体并恢复镜像
This is actually wrong. The principal and mirror database are in fact a single database that is stored in two physical locations. Any and every change that occurs in the principal, occurs also in the mirror. They are literarly identical all the time.
这实际上是错误的。主体和镜像数据库实际上是存储在两个物理位置的单个数据库。在主体中发生的任何和每个更改也发生在镜像中。它们始终是完全相同的。
So any schema changes, including tables, views, procedures, functions, schemas, database principals, assemblies and so on and so forth occur on both the principal and the mirror.
因此,在主体和镜像上都会发生任何模式更改,包括表,视图,过程,函数,模式,数据库主体,程序集等等。
The only changes that require special attentions are changes that are related to the database but don't occur in the database: Agent jobs (they are in msdb), server principals (logins), replication settings (they are all over the place in database, msdb and distributor.
需要特别注意的唯一更改是与数据库相关但在数据库中不发生的更改:代理作业(它们位于msdb中),服务器主体(登录),复制设置(它们遍布数据库中) ,msdb和经销商。
#3
1
We have applied schema changes to both a logshipped and mirrored database and not had to take any special action.
Some forms of replication require special mesaures though.
我们已将架构更改应用于logshipped和镜像数据库,而不必采取任何特殊操作。某些形式的复制需要特殊的测量。
#1
11
If you are mirroring, you should be able to apply those changes to the principal and they show up on the mirrored side. If you want there to be schema difference between the two, you can't use mirroring. Are you looking to apply schema changes on just the mirrored database or to both? If both then you don't have to do anything special.
如果您正在镜像,则应该能够将这些更改应用于主体,并且它们将显示在镜像端。如果您希望两者之间存在架构差异,则无法使用镜像。您是否希望仅在镜像数据库或两者上应用架构更改?如果两者都没有做任何特别的事情。
#2
2
Now I know that if you make schema changes you are supposed to remove mirroring and the mirror database, make your changes to the principal, then backup-restore the principal and restore mirroring
现在我知道如果你进行架构更改,你应该删除镜像和镜像数据库,对主体进行更改,然后备份 - 恢复主体并恢复镜像
This is actually wrong. The principal and mirror database are in fact a single database that is stored in two physical locations. Any and every change that occurs in the principal, occurs also in the mirror. They are literarly identical all the time.
这实际上是错误的。主体和镜像数据库实际上是存储在两个物理位置的单个数据库。在主体中发生的任何和每个更改也发生在镜像中。它们始终是完全相同的。
So any schema changes, including tables, views, procedures, functions, schemas, database principals, assemblies and so on and so forth occur on both the principal and the mirror.
因此,在主体和镜像上都会发生任何模式更改,包括表,视图,过程,函数,模式,数据库主体,程序集等等。
The only changes that require special attentions are changes that are related to the database but don't occur in the database: Agent jobs (they are in msdb), server principals (logins), replication settings (they are all over the place in database, msdb and distributor.
需要特别注意的唯一更改是与数据库相关但在数据库中不发生的更改:代理作业(它们位于msdb中),服务器主体(登录),复制设置(它们遍布数据库中) ,msdb和经销商。
#3
1
We have applied schema changes to both a logshipped and mirrored database and not had to take any special action.
Some forms of replication require special mesaures though.
我们已将架构更改应用于logshipped和镜像数据库,而不必采取任何特殊操作。某些形式的复制需要特殊的测量。