[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

SQL Server 2005 Redudancy

Dear,
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
0
Showkatdar
Asked:
Showkatdar
  • 5
  • 4
2 Solutions
 
St3veMaxCommented:
Look at SQL Server Database Mirroring in Books On Line. It's fast failover and not hardware specific.

HTH
0
 
bcsqlCommented:
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.
0
 
ShowkatdarAuthor Commented:
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
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
St3veMaxCommented:
When you say redundancy; how quickly do you need the standby server to be available?
0
 
ShowkatdarAuthor Commented:
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
0
 
St3veMaxCommented:
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.

HTH
0
 
ShowkatdarAuthor Commented:
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]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

 

17)   Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

 

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.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

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.

 

0
 
St3veMaxCommented:
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.
0
 
ShowkatdarAuthor Commented:
Dear,
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.
0
 
ShowkatdarAuthor Commented:
Dear,
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now