SQL Server 2005 Redudancy

Posted on 2009-04-16
Last Modified: 2012-05-06
I need help. I am working on a site where we have Live SQL Server 2005. What i need is Redudancy of this SQL Server.  We have no domain controller out there. It is stand alone SQL Server.
I dont know much about this. So please help me to solve this issue. I want to know Pre-Requirenments for doing the same, and also please help me to know how to do this.
Waiting for Some body to Reply
Thank you and Regards
Question by:Showkatdar
    LVL 13

    Assisted Solution

    Look at SQL Server Database Mirroring in Books On Line. It's fast failover and not hardware specific.

    LVL 6

    Expert Comment

    While Mirroring is cool you may not have the bandwith to support it to or from a remote site. you should also look at logshipping.

    Author Comment

    Dear all
    Thankx for your comments.
    Well i dont know much about SQL, so i m worried about it. So please if any body can help me find a better solution.
    Just give me quick Steps to achieve this task.
    With Regards
    LVL 13

    Expert Comment

    When you say redundancy; how quickly do you need the standby server to be available?

    Author Comment

    Dear St3veMax,
    Well Actually i m working on a live System, where we have SQL Server 2005 running. What i need is to add one more SQL Server 2005 for Redundancy. By Redundancy i mean if My Primary SQL Server Fails, this second SQL Server should take care of all the things automatically.
    So either we say it can be done through SQL Redundancy, Log Shipping or Mirroring, That i want to know. Which is the best way to do it, how to do it, what hardware Requirenments are there.
    Please help
    With Regards
    LVL 13

    Expert Comment

    OK;As i see it; You have two options for automatic failover. One depends on your application; if it can use the SQL Native Access Client (SNAC); If so, Database Mirroring would work for you in High Availability mode. It's hardware independant; so you dont require identical servers, disk sub-system, etc.

    If the SNAC is not an option; for automatic failover you would be better with an Active/Passive SQL Cluster however this required identical hardware and a shared storage array (HP MSA, EVA, etc).
    With the cluster; you have two nodes; one active, one passive. Should the active fail, it will fall over to the passive node and resume processing.

    It terms of hardware requirements; what ever works for your existing SQL Server will be good enough for either options; you may require additional hardware for a cluster (such as SCSI/Fibre HBA's, etc)

    One final though is that if you need your servers to be geographically apart (i.e. DR/BCP); then Mirroring would be your best bet; but would need to be made to work with teh SNAC and you need to calculate how quickly your application writes to the transaction log; as you would need a WAN link big enough to cope in order to give you the high availability due to the nature in which transactions are committed.


    Accepted Solution

    Dear all,

    I got the solution. In this example we have two machines running windows 2003 server with SQL 2005.
    One Pc is domain controller( MachineB) and one is client (MachineA) to it.

    1)       Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

    2)       MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

    3)       MRRUSR is the MSSQLSERVER service account in both machines

    4)       Machine A is gonna be the PRINCIPAL, B the MIRROR.

    5)       Log on in my machine as MRRUSR

    6)       Open Sql server Studio. Connect to both machines using windows authenticacion.

    7)       Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

    8)       Backup the new DB (Full backup) with .bak extension

    9)       Backup the new DB (Transaction log) with .trn extension

    10)   Copy the both files to a location in B machine.

    11)   Restore .bak into a new DB using NORECOVERY option

    12)   Restore .trn into the previous DB using NORECOVERY option

    13)   Go to Machine A, open a new query

    14)   Take a look to the endpoints

    SELECT type_desc, port FROM sys.tcp_endpoints;
    SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;


    15)   Delete existing ones (DROP ENDPOINT [NAME])

    16)   Create a new endopoint

    CREATE ENDPOINT [Mirroring]


    17)   Start it

    ALTER ENDPOINT [Mirroring]
    FOR database_mirroring (ROLE = PARTNER);


    18)   Do steps 13 to 17 in machine B.

    19)   Return to machine A. Query.

    20)   Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.


    21)   Repeat step 20 from MACHINE B pointing to MACHINE A

    22)   Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

    23)   Lets test them: Add some new data in Machine A database.

    24)   Then, right click over database, then mirror, then in the form select FAILOVER, then say yes.

    25)   Refresh the 2 databases in the UI you should see the new status and roles changed.

    26)   Open Machine B databse, the new data should be there.


    LVL 13

    Expert Comment

    User asked for assistance as didnt know subject area; posted suggestions to look at technology wise and gave some thoughts into a couple of options and now op has closed thread and marked answer found by himself.

    Author Comment

    I got this link from the net, but nobody suggested me so. I got it and did some alterations.
    So nothing wrong in it. Did anybody suggested me thiz? I waited to 3 days to get an answer, but i did not. So i found solution for my problem by myself, what is wrong in this.

    Author Comment

    Please See My Comments, Where I repeadly said i dont know much about SQL. Is not that enough for an Expert to grab what i mean, what i need. If i dont know much about SQL means to give a clear picture on Low Level. A Simple Solution.
    Still i dont have any Issue in distributing Points, But i want to know only is that If i was right or wrong!
    Please go through my Comments and aswell as Experts Comments.
    With Reards

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now