Home » Articles posted by rohitmssqlfun

Author Archives: rohitmssqlfun

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′

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

Does Full Backup contain data inserted during backup ?


Question : When you take full backup, do it capture data till the time backup start time or till the time backup end.

Answer : Full backup capture database till the backup completion time. Let check it :-

1) I have initiated the TEST database backup with buffercount =1 so that backup took some time & I can insert some data in meanwhile.

2) You can see that we have data in BackupTest table in between of backup start & end time.

3) Now, I restored Database TEST as TEST2 & found that data is available in BackupTest table that was inserted during backup.

Note : Differential backup also works same like full backup in this case.

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

SQL Server 2008 Service Pack 4 Is Now Available !


Finally after a long waiting period, Microsoft release SQL Server 2008 Service Pack 4, Same is now available for download at the Microsoft Support site. SQL Server 2008 Service Pack 4 contains all the hotfixes released since the initial release of SQL Server 2008 RTM.

I suggest to test SQL Server 2008 Service Pack 4 in test environment & then move to Production after satisfactory results.

Cumulative Updates for SQL Server 2008 SP4 will not be released.

KB Article For SQL Server 2008 Service Pack 4

· http://www.microsoft.com/en-us/download/details.aspx?id=44278

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

SQL Server 2008 R2 Service Pack 3 Is Now Available !


Finally after a long waiting period, Microsoft release SQL Server 2008 R2 Service Pack 3, Same is now available for download at the Microsoft Support site. SQL Server 2008 R2 Service Pack 3 contains all the hotfixes released since the initial release of SQL Server 2008 R2 RTM.

I suggest to test SQL Server 2008 R2 Service Pack 3 in test environment & then move to Production after satisfactory results.

Cumulative Updates for SQL Server 2008 R2 SP3 will not be released.

KB Article For SQL Server 2008 R2 Service Pack 3

· http://www.microsoft.com/en-us/download/details.aspx?id=44271

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

SQL Server Configuration Manager giving Error : The remote procedure call failed. 0x800706be


Issue : I am facing error β€œThe remote procedure call failed. [0x800706be]” while trying to view SQL Services from SQL Server Configuration Manager.

Steps I perform to resolve the issue, Seems to be a WMI error :-

1) If using Window Server 2008 & above, Run command from command prompt to verify WMI repository : winmgmt /verifyrepository

If you got any error, Please contact your OS team for WMI rebuild.

2) Recompiled the MOF file –> mofcomp.exe "C:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof"

3) Re-registered the SQL Server provider DLL –> regsvr32 "C:Program FilesMicrosoft SQL Server90Sharedsqlmgmprovider.dll"

4) Your issue should be resolved now. If you still face same issue that means there are chances that you may be running with different versions of SQL Server. If yes, Please try to patch SQL Server Management Studio (SSMS) with latest available Service pack.

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 228 other followers