SQL Mirror HA(SQL serverµÄ¸ß¿ÉÓÃÐÔ)[×ªÔØ]
¶ÔSQL Server 2008 ½øÐÐMirrorµÄ»ù±¾ÅäÖü°°²×°¡£
×¼±¸¹¤×÷
1. Principal ÓëMirror ÒÔ¼° Witness Èý̨ʵÀý¶¼°²×°ÎªÏàͬ°æ±¾£¬Ä¿Ç°Êǵ½SQL Server 2008 SP1¡£
2. ÐèÒªÓÐÒ»¸öÓòÕÊ»§·Ö±ðÌí¼Óµ½ Èý̨Êý¾Ý¿âʵÀýËùÔÚµÄServer µÄ Administrators×é¡¢ÒÔ¼°SQL Server Ïà¹ØµÄ×éÖУ» ÓÉÓÚ¾ßÌåµÄ×éµÄ¹¦Äܲ»Çå³þ£¬Òò´Ë½¨ÒéËùÓÐ×é¶¼Ìí¼Ó¸ÃÓòÕÊ»§£¬È·±£È¨ÏÞÔËÐÐÕý³£¡£
3. ½«µÚ¶þ²½ÖеÄÓòÕÊ»§ÉèÖÃΪSQL Server ·þÎñ£¬SQL Server Agent µÄÆô¶¯ÕÊ»§£¬²¢ÖØÆô·þÎñ¡£
4. È·±£Principal ÓëMiroor Á½Ì¨ÊµÀýÉϵÄÊý¾ÝÊý¾Ý¼°ÈÕÖ¾Ïàͬ¡£±¸·ÝģʽΪÍêÈ«±¸·Ý£¬»Ö¸´Ä£Ê½ÎªÍêÈ«£¬ÏòMirrorʵÀýÉϻָ´Êý¾Ý¿âʱ±ØÐëÑ¡ÔñΪNoRecoveryģʽ¡£
´´½¨EndPoint
1. ÔÚPrincipalÓë MirrorʵÀýÉÏ´´½¨EndPoint£¬ÔËÐнű¾£º
Create EndPoint On Principal and Mirror Instance
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING
(ROLE = PARTNER, ENCRYPTION = SUPPORTED)
--»ò
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING
(AUTHENTICATION=WINDOWS NTLM,ROLE = PARTNER)
2. ÔÚWitnessʵÀýÉÏ´´½¨EndPoint£¬ÔËÐнű¾£º
Create EndPoint On Witness Instance
CREATE ENDPOINT mirroring_ep
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING
( ENCRYPTION = SUPPORTED, ROLE=WITNESS );
--»ò
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING
(AUTHENTICATION=WINDOWS NTLM,ROLE = WITNESS)
²é¿´EndPoint¼°É¾³ýEndPoint½Å±¾£º
Select * from Sys.database_mirroring_endpoints
Select * from sys.database_mirroring
Drop Endpoint endpoint_mirroring
´´½¨ Mirror Session
1.ÔÚMirror ʵÀýÉÏ´´½¨Session£¬ÔËÐнű¾£º
ALTER DATABASE DataBaseName
SET PARTNER = 'TCP://PrincipalServerName:5022';
2.ÔÚPrincipal ʵÀýÉÏ´´½¨Session £¬ÔËÐнű¾£º
ALTER DATABASE DataBaseName
SET PARTNER = 'TCP://MirrorServerName:5022';
3.ÔÚPrincipal ʵÀýÉÏÖ¸¶¨Witness£¬ÔËÐнű¾£º
ALTER DATABASE HB2008_Gome
SET
Ïà¹ØÎĵµ£º
TABLE MASTER ×Ö¶Î ID DETAIL.....
TABLE BIZ ×Ö¶Î SYS_ID CODE_ID .......
²éѯʱÐèÒªµÄÊÇIDµÄÃèÊö
1,SELECT A.SYS_ID,A.CODE_ID,B.DETAIL,C.DETAIL...... from BIZ A,MASTER B,MASTER C WHERE A.SYS_ID=B.ID AND A.CODE_ID=C.ID
2,SELECT SYS_ID,(SELECT DETAIL from MASTER ......
sql serverºÍoracleÐÐתÁеÄÒ»ÖÖµäÐÍ·½·¨
ǰÑÔ£ºÍøÉÏÓв»ÉÙÎÄÕÂÊǽ²ÐÐתÁе쬵«ÊǴ󲿷ֶ¼ÊÇÖ±½ÓÌù´úÂ룬ºöÊÓÁËÖмä¹ý³Ì£¬±¾ÈË×Ô¼ºË¼¿¼ÁËÏÂΪʲôҪÕâÑùʵÏÖ£¬²¢ÇÒ×öÁËÈçϵıʼǣ¬¶ÔÓÐЩ¶®µÄÈËÀ´Ëµ¿ÉÄÜûÓмÛÖµ£¬Ï£Íû¶Ô»¹²»¶®µÄÈËÓÐÒ»µã½è¼øÒâÒå¡£
¶ÔÓÚÓÐЩҵÎñÀ´Ëµ£¬Êý¾ÝÔÚ±íÖеĴ洢ºÍÆä×îÖÕµÄGrid±íÏÖÇ¡ºÃÏ൱ÓÚ°ÑÔ ......
Sql Server2005µÄÒ»¸öÐÂÌØÐÔ±ãÊÇÎÒµÈÁ˺ܾõÄRow_Number(),ÒÔǰÓÃOracleʱÓÃrownumberд·ÖÒ³´æ´¢¹ý³ÌºÜ·½±ã£º£©
ÏÂÃæÊÇÎÒ×öµÄÒ»¸öССµÄ²âÊÔ£¬²âÊÔÎÒÔÀ´ÔÚsql server2000ÏÂËùÓõķÖÒ³´æ´¢¹ý³ÌÓëʹÓÃRow_Number()±àдµÄ´æ´¢¹ý³ÌÔÚSql Server2005ÉϵÄÖ´ÐÐЧÂÊ...
Êý¾Ý±í£º
REATE TABLE [dbo].[test](
[UserId] [int] Pri ......
ÆÕͨÐÐÁÐת»»
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí
---- ---- ---- ----
ÀîËÄ 74 84 94
ÕÅÈý 74 83 93
-------------------
*/
create table tb(Ð ......
for ACCESS :
update a, b set a.name=b.name1 where a.id=b.id
for SQL Server:
"update a set a.name=b.name1 from a,b where a.id=b.id"
update a set a.status=b.status
from table1 a,table2 b
&nbs ......