Home » SQL Server

Category Archives: SQL Server

Add Database File on Database invloved in DB Mirroring


Originally posted on MSSQLFUN:

Database Mirroring works on transaction logs. It applies transaction logs from Principle to Mirror. But Operation like addition of file is no log operation because of that file not be added to Mirror automatically.

Steps to Add Database File on Database involved in DB Mirroring :-

1) Break the Mirroring

ALTER DATABASE USERDB1 SET PARTNER OFF

2) Add New Data or log file, whatever is required

USE [master]

GO

ALTER DATABASE [UserDB1] ADD FILE

( NAME = N’UserDB1_1′,

FILENAME = N’H:MSSQL12.INS1MSSQLDATAUserDB1_1.ndf’ ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

TO FILEGROUP [PRIMARY]

3) Take Transaction Log backup at Primary

BACKUP LOG USERDB1 TO DISK = ‘C:USERDB1_LOG.TRN’

4) Restore Step 3 Transactional Backup on Mirror with Move option

RESTORE LOG [USERDB1] FROM

DISK = N’C:USERDB1_LOG.TRN’

WITH Move N’UserDB1_1′ TO

N’I:MSSQL12.SQL14I2MSSQLDATAUserDB1.ndf’,

NORECOVERY, NOUNLOAD, STATS = 10

5) Reestablished DB Mirroring again

First on Mirror

ALTER DATABASE USERDB1

SET PARTNER = ‘TCP://SQL2014.RohitGarg.local.in:5022′

View original 91 more words

Add Database File on Database invloved in DB Mirroring


Add Database File on Database invloved in DB Mirroring.

Cumulative Update – 4 for SQL Server 2014 RTM Is Now Available !


The 4thcumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 4 contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

Those who are facing severe issues with their environment, they can plan to test CU4 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP1 final release to deploy on your production environment, to have consolidate build.

KB Article For CU4 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Remove Witness from SQL Server Database Mirroring


1) After connecting to the principal server instance, Right-click the database & go to properties. Select Mirroring from left side option.

2) To remove the witness, delete its server network address from the Witness field.

3) Click Ok to confirm.

You can also perform the same action by below T-SQL.

ALTER DATABASE database_name SET WITNESS OFF

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Add Database File on Database invloved in DB Mirroring


Database Mirroring works on transaction logs. It applies transaction logs from Principle to Mirror. But Operation like addition of file is no log operation because of that file not be added to Mirror automatically.

Steps to Add Database File on Database involved in DB Mirroring :-

1) Break the Mirroring

ALTER DATABASE USERDB1 SET PARTNER OFF

2) Add New Data or log file, whatever is required

USE [master]

GO

ALTER DATABASE [UserDB1] ADD FILE

( NAME = N’UserDB1_1′,

FILENAME = N’H:MSSQL12.INS1MSSQLDATAUserDB1_1.ndf’ ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

TO FILEGROUP [PRIMARY]

3) Take Transaction Log backup at Primary

BACKUP LOG USERDB1 TO DISK = ‘C:USERDB1_LOG.TRN’

4) Restore Step 3 Transactional Backup on Mirror with Move option

RESTORE LOG [USERDB1] FROM

DISK = N’C:USERDB1_LOG.TRN’

WITH Move N’UserDB1_1′ TO

N’I:MSSQL12.SQL14I2MSSQLDATAUserDB1.ndf’,

NORECOVERY, NOUNLOAD, STATS = 10

5) Reestablished DB Mirroring again

First on Mirror

ALTER DATABASE USERDB1

SET PARTNER = ‘TCP://SQL2014.RohitGarg.local.in:5022′

Second on Principle

ALTER DATABASE USERDB1

SET PARTNER = ‘TCP://SQL2K14_2.RohitGarg.local.in:5022′

 

NOTE : In case your Database Mirroring has same file structure at both principal & mirror then no special steps needed. Once you add file at principal, file automatically added to mirror database. Above steps are valid only for different  file structure at both principal & mirror.

Thanks to my friend Sachin for pointing this to me.

 

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Database Mirroring States (SQL Server)


SQL Server Database Mirroring has 5 different states. According to BOL (http://technet.microsoft.com/en-us/library/ms189284.aspx) :-

Mirroring state Description
SYNCHRONIZING The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.
At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
SYNCHRONIZED When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
If transaction safety is set to FULL automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
SUSPENDED The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session.
SUSPENDED is a persistent state that survives partner shutdowns and startups.
PENDING_FAILOVER This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
DISCONNECTED The partner has lost communication with the other partner.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Configure SQL Server Database Mirroring


1) Take Full backup of database at Principle Server

BACKUP DATABASE USERDB1 TO DISK = ‘H:USERDB1.BAK’

2) Take Log Backup at Principle Server

BACKUP LOG USERDB1 TO DISK = ‘H:USERDB1_LOG.TRN’

3) Copy both backup files to Mirror server

4) Restore Full backup with No Recovery at Mirror Server

USE [master]

RESTORE DATABASE [USERDB1] FROM DISK = N’I:USERDB1.BAK’

WITH

MOVE N’UserDB1′ TO N’I:MSSQL12.SQL14I2MSSQLDATAUserDB1.mdf’,

MOVE N’UserDB1_log’ TO N’I:MSSQL12.SQL14I2MSSQLDATAUserDB1_log.ldf’,

NORECOVERY, NOUNLOAD, STATS = 5

5) Restore Log backup with No Recovery at Mirror Server

RESTORE LOG [USERDB1] FROM DISK = N’I:USERDB1_LOG.TRN’

WITH NORECOVERY, STATS = 5

6) Now, Configure Mirroring using SSMS. After connecting to the principal server instance, Right-click the database & go to properties. Select Mirroring from left side option.

7) Click Configure Security, Configure Database Mirroring Security Wizard welcome screen appears, click Next.

8) In the Include Witness Server dialog box, click Yes, and then click Next.

9) On the Principal Server Instance dialog box. Connect to Principle & Click Next.

10) On the Mirror Server Instance dialog box. Connect to Mirror & Click Next.

11) Enter Service accounts you wants to use & click Next.

12) Configuration complete, Click Finish to start configuration.

13) Configuration completed successfully.

14) Click on Start Mirroring.

15) Mirroring configured successfully & Click Ok

16) Sample Database Mirroring working fine.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Follow

Get every new post delivered to your Inbox.

Join 231 other followers