Database Mirroring

Night Coder كتب:

وعليكــم السـلام ورحمة الله وبركاتـه..

ما المشكلة تحديداً فى هذا الجزء لان موضوع ال Mirroring كبير ابتداءاً من تحديد ال Mirroring Mode و انتهاءاً بال Failover و بناء ال Farm Topology المناسبة

شكرأ لردك .. قرأت بالانترنت و فهمت مبدأ عمل ال Mirroring .. لكن ما أريده هو الخطوات العمليه لذلك ..

بصراحه انا اعمل بمجال التحليل و ليست لي خبرة عملية بال mirroring لكن احتجتها لموضوع High Availability للشيربوينت .

الموضوع شيق و جديد و سيسعدنى ان اتناوله فى مقالة او اكثر بهذا الخصوص و فى حال وجود مشكلة محددة يمكنك طرحها بالمنتدى و سنتناول الاجابة عنها

شكراً لك لاهتمامك ..

سأبدأ بالموضوع حسب خطوات من موقع مايكروسوفت .. و نسأل الله التوفيق ..

ان شاءالله اذا زبطت الأمور سأعيد شرحها بالمنتدى بخطوات مفصلة :)

هذه الطريقة بالتفصيل

http://www.dotnetfun.com/?guid=tag%3Ablogg...697483086672715

اقتباس

Mirroring Databases in SQL Server 2005

Introduction

This tutorial will show you how to mirror one database from one instance of SQL Server 2005 to another. I will be using the AdventureWorks database for this demonstration and will not be using a Witness server (only a Principal and Mirror server [i.e., Source and Target]).

Please note that both the Principal and Mirror servers must be of the same SQL Server edition. If they are not, mirroring will not work.

Enable Mirroring in SQL Server 2005 if SP1 is Not Installed

On computer/instance mirror A:

1. Open the SQL Server Configuration Manager for SQL Server 2005, typically, this is located in the Programs menu of the Start button (Microsoft SQL Server 2005, then Configuration Tools).
   
2. Select the SQL Server 2005 Services node.
   
3. In the right pane, right-click on the SQL Server service, then click on Properties.
   
   
4. Click on the Advanced tab.
   
5. Locate the Startup Parameters field, then append trace flag 1400 to it: ; -T 1400
   
   
6. Right-click on the SQL Server service, then click on either Restart or Start.
   

On computer/instance mirror B:

1. Repeat the steps outlined for On computer/instance mirror A for this computer.
   

Please note mirroring doesn't seem to work if you use this trace flag with SQL Server 2005 without any Service Packs installed. The trace flag seems to work ONLY with older RTM versions. So, if you have SQL Server 2005 with no Service Packs installed, please install them on all running instances.

Preparing Databases for Mirroring

1. Using SSMS (SQL Server Management Studio), set the Recovery Model of the source database to Full, if it's not already.
   
   
2. Backup the source database AND transaction log(s), a full backup first, then a transaction log(s) backup.
   
   
3. Restore the just-backed up source database AND transaction log(s) to the target server/instance using the WITH NORECOVERY option for each restore(s), starting with the full database restore, then transaction log(s) restore(s), ensuring that the data file paths are not physically the same (i.e., do not overwrite the source data files with this restore).
   
   
   

Configuring and Starting Mirroring

1. Using SSMS, launch the Mirror... task on the source database.
   
   
2. Click on the Configure Security... button.
   
3. Click on Next.
   
   
4. Select No to NOT include a witness server.
   
5. Click on Next.
   
6. Click on Next to save the security configuration.
   
7. Select the source server, which should already be selected, in the Principal server instance field, set or leave the port, then specify a unique Endpoint name.
   
   
8. Click on Next.
   
9. Select the target server in the Mirror server instance field, set or leave the port, then specify a unique Endpoint name.
   
   
10. Click on Next.
    
11. Set the Principal and Mirror Service Accounts, preferrably, they will be the same account.
    
    
12. Click on Next.
    
13. Click on Finish.
    
    If you run into problems, re-click on the Configure Security button to change the parameters. You should get Successful results for both Endpoints.
    
    
14. Click on Do Not Start Mirroring.
    
    
15. Start mirroring by clicking on the Start Mirroring button. If successfull, the Pause button should be enabled.
    
    

Using the Database Mirroring Monitor

1. In SSMS, right-click on the source database, navigate to Tasks, then click on Launch Database Mirroring Monitor...
   
   
2. Click on Register mirrored database.
   
   
3. Click on the Connect button, then select the Mirror/target server.
   
4. Click on the Register checkbox in the Mirrored databases list.
   
   
5. Click on OK.
   
6. In the left pane, expand the Database Mirroring Monitor node by clicking on the plus sign.
   
7. Click on the mirrored database. You should now see the status of the mirror.
   
   

Failing Over to the Mirrored/Target Database

1. Execute the following T-SQL command at the mirror:ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
   

Issues to Consider About Database Mirroring and this Tutorial

1. The source/Principal and target/Mirror SQL Server Service account(s) must be granted CONNECT permission on the target/Mirror and source/Principal servers respectively. If not, mirroring will fail upon start.
   
2. Encryption - By default, data is encrypted, sent to the mirror, then decrypted. This uses up more server resources and bandwidth than it would without encryption. So, ONLY use encryption if the data being mirrored is sensitive and should be encrypted.
   
3. Witness - This tutorial doesn't use a Witness server. A witness server can provided automated failover capabilities.
   
4. Asynchronous mirroring is only available in SQL Server 2005 Enterprise & Developer Editions, Synchronous mirroring is available in all others, except for Express Edition. So, fully evaluate the performance implications when using mirroring with SQL Server 2005 Standard Edition, as performance might be negatively impacted in a production environment.
   

جزاك الله خيراً على مشاركتك

اود ان اوضح فقط ان تنفيذ الخطوات المذكورة بدون معرفة جيدة بال Architecture المتواجدة او فهم جيد لل Mirroring هو امر غير جيد بالمرة

Night Coder كتب:

الموضوع شيق و جديد و سيسعدنى ان اتناوله فى مقالة او اكثر بهذا الخصوص و فى حال وجود مشكلة محددة يمكنك طرحها بالمنتدى و سنتناول الاجابة عنها

Night Coder كتب:

جزاك الله خيراً على مشاركتك

اود ان اوضح فقط ان تنفيذ الخطوات المذكورة بدون معرفة جيدة بال Architecture المتواجدة او فهم جيد لل Mirroring هو امر غير جيد بالمرة

اخي الكريم .. أنا مشيت بالخطوات جميعها و انتهيت من الـ

Configuration EndPoint

وكانت النتيجه Success

لكن المشكله التي صادفتني بعد ذلك :

اقتباس

Database mirroring cannot be enabled because the "testmirror" database may have bulk logged changes that have not been backed up. the last log backup on the principal must be restored on the mirror.(Microsoft SQL Server, Error:1475)

انا مستخدم الـ Restore WITH RECOVERY

حاولت استخدم الـ NoRecovery لكن كانت النتيجه

انه يظل يعمل

اقتباس

Restoring ............

بدون أن ينهي

ما الحل ؟

Run The Following Statement First

ALTER DATABASE YourDBName SET AUTO_CLOSE OFF

Then Take Full Backup ForThe Database and Restore It On Mirror Using "WITH NORECOVERY " Option

and Do The Same For The Log File

Night Coder كتب:

Run The Following Statement First

ALTER DATABASE TestMirror SET AUTO_CLOSE OFF

Then Take Full Backup ForThe Database and Restore It On Mirror Using "WITH NORECOVERY " Option

عملتها من خلال الـ

properties --> option

غيرتها الى False

أظن أنها تفي بالغرض ؟

Has It Solved Your Problem ?

للأسف أخي .. لا

نفس المشكلة السابقة .. يظله عامل

Restoring ............

علماً اني استخدم قاعده بيانات test حجمها لا يتجاوز 20 ميغا

أكثر من ساعه و هو يعمل ريستور بدون نتيجه

** استفسار اخر :

الوضع القياسي انه افترض البورت 5022 للسيرفر الأصيل و للسيرفر البديل

هل ابقيها للأصيل كما هي و اجعلها للبديل 5023 ؟؟

الحمدلله حُلَت المشكله السابقة

لكن الان تصادفني مشكلة أخرى قبل Start Mirorrong

وهي :

اقتباس

'The server network address "TCP://[server][domain address]:5022" can not be reached or does not exist. Check the network address name and reissue the command.'

ما الحل ؟؟

مع العلم انه كل السيرفس شغاله

و الفيروول كمان

عملت ping و telnet للسيرفر و البورت

و كلشي تمام

لكن المشكلة انه مش قادر يشوف الجهاز نفسه ..

اقتباس

الحمدلله حُلَت المشكله السابقة

Kindly Mention The Reason and The Solution

Night Coder كتب:

Kindly Mention The Reason and The Solution

لما تعمل Restore database :

source database -->> Restore WITH NORECOVERY

أما

transaction log -->> Restore WITH RECOVERY

خطأي السابق انه كنت اعمل NORECOVERY للسورس و للوغ

-----------------

أنا الان اواجه المشكله error 1418

اقتباس

'The server network address "TCP://[server][domain address]:5022" can not be reached or does not exist. Check the network address name and reissue the command.'

أرجو المساعده من الاخوان اذا مرت معهم

http://msdn.microsoft.com/en-us/library/aa337361.aspx

http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx

شكراً لك .. لكن لا زلت أواجه الخطأ 1418