MS SQL Server :: Mirror Setup Problems

Mirror Setup

Jul 11, 2007

Hello,

I'm trying to setup database mirroring between 3 servers and the details are as follows:

1. All 3 machines reside in the same domain.

2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.

3. All the 3 machines are having their individual service domain accounts.

4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.

5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.

6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.

7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.

8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.

9. On Server 2 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'

10. On Server 1 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'

ALTER DATABASE <dbname>

SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'

On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."

I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.

Thanks

View 13 Replies View Related

SQL 2005 SP2 Mirror Setup

Mar 7, 2007

I'm trying to setup a SQL 2005 SP2 Mirror in between two mashines with no witness server.

I have successfully established the following:

1. Backup The Database on Principal Server: OK!

2. Copy the backup over to the mirror server: OK!

3. Restore the Database on Mirror Server: OK!

4. Establish Endpoint on Principal Server: Encyrption Disabled OK! TCP - PORT 5022

4.1 Test Script: Checks fine (values match)

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id
5. Establish Endpoint on Mirror Server: Encyrption Disabled OK! TCP - PORT 5022

5.1 Test Script: Checks fine (values match) (same script as above)

6. Specify Principal server from Mirror server: OK! Connecting with IP

7. Specify Mirror server from Principal server: OK! Connecting with IP

8. View Mirror Information:

8.1 Test Script: Shows everything in NULL

SELECT mirroring_state_desc, mirroring_partner_name, mirroring_witness_name, mirroring_witness_state_desc, mirroring_role_desc, mirroring_safety_level_desc
FROM sys.database_mirroring

Mirror endpoints are still in red even though they initialized correctly.
Mirror monitor shows no mirrors are up.
SQL Logs shows: The Database Mirroring protocal transport is now listening for connections.
Any help would be appreciated.

View 1 Replies View Related

DB Engine :: Can Mirror A Database On Which AlwaysOn Is Already Setup

Jul 29, 2015

We have a database with AlwaysOn enabled, where the database is getting mirrored using AlwaysOn to a secondary node in a two node cluster. Now we want to set up a DR site for that database in a different data center.

So trying to analyse what technologies. We can use for setting up the DR for that database. Can we use mirroring / replication or what's the best way of setting up DR on that database.

View 3 Replies View Related

Mirroring :: Async Mirror Setup For High Number Of Databases

Oct 27, 2015

I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances.

What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.

What all needs to be checked for looking into the feasibility of going ahead with a async mirror setup as mentioned above.

View 4 Replies View Related

Setup And Upgrade :: Both Database Went To Suspended Mode In Principal And Mirror

Aug 10, 2015

I have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.

View 3 Replies View Related

Mirroring :: How To Reduce LDF Size While Mirror Enabled With Mirror And Witness Server

Jun 14, 2015

I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.

View 4 Replies View Related

Mirror And Witness Connection In A Disconnected State Immediately After Adding Witness Server To Mirror Session.

Jan 31, 2008

After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.

The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.

ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'

After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.

The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.

Any idea's why the state between Mirror and Witness Connection remains Disconnected?

Thanks,

View 9 Replies View Related

Mirror Database-How Do I Remove It From Being The Mirror

Dec 27, 2006

I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.

View 1 Replies View Related

MDAC 2.8 Fatal Setup Error. This Setup Does Not Support Installing On This Operating System

Jan 5, 2006

I have XP Pro SP2 with MDAC 2.8.1022. It had a problem so I tried to reinstall MDAC and got a Fatal Setup Error. This setup does not support installing on this operating system. I downloaded MDAC 2.8 1177 and get the same error.

I thought of uninstalling/reinstalling SP2, but this is a 2 month old Dell Latitude 610 with factory installed XP. There is no Windows Service Pack 2 option listed in the Control Panel > Add/Remove Programs.

There's some other strange things, so I wonder if they are related.

1) I have Paul set up as an administrator account. Some folders like MSSQL show that account with no permissions. I grant all the permissions to Paul for that folder. I come back later and the permissions are gone.

2) I deleted 20 files in Explorer, but 7 of them did not go away. I deleted those 7 again and they instatnly reappeared. I deleted those 7 again and then they finally went away.

3) I get a slow reaction time for things like Windows Explorer and opening and closing programs. This is suprising since it has 2 gig of RAM and 2.3 Gig processor. Could it be a memory handling problem that's causing OS problems. Probably, the memory didn't handle the OS installation well and the whole system is compromised now.

View 12 Replies View Related

Setup And Upgrade :: 2008 R2 Setup UI Has Incomplete List Of Installed Features - Can't Uninstall RS

Jul 23, 2015

We're doing upgrades from SQL 2008 R2 to SQL 2014. This is blocked due to RS is installed but not configured. Our desired action is to uninstall RS and proceed with the upgrade. But when setuparp.exe is raised, it does not list all the features on the 'Select Features' page. In fact, it only lists the last 2 shared features (SQ Client Connectivity SDK and Microsoft Sync Framework). However, all items appear to be listed on the 'Select Instance' page including RS. I've seen this issue on 2 of our SQL 2008 R2 Servers already.

View 3 Replies View Related

Setup And Upgrade :: Error Setup Account Privileges

Nov 15, 2015

I'm trying to install SQL Server Management studio 2012 on my Windows 7 (x64) standalone laptop. When I click "New SQL stand-alone installation..." it runs a Setup Support Rules check and always fails "Setup Account Privileges". I've looked into the error and I keep getting that I need to change security rules but I don't have that option in window 7. How do I get around this without having to resort to a computer running Windows Server?

I have Visual Studio 2013 premium installed along with Localdb v11. I just want to connect and manage my database engine through SSMS when developing any application.

View 2 Replies View Related

Please Post Setup And Installation Questions In The Setup Forum

Nov 17, 2005

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

View 11 Replies View Related

Setup And Upgrade :: One Machine 6 IPs - How To Setup DNS For Server

Apr 24, 2015

I am running a number of SQL instances on my PC. Within the network, I have think server with various System Center components. For compatibility reasons, some features of System Center 2012 R2 had to be delegated to different SQL databases. My question is, because there is now more than one IP address on my system, and each instance of SQL is assigned to its own IP, is there a way to setup DNS and SQL so the namespace points to the desired IP address? For Instance:

MSSQL2008 instance is set to run on = 11.12.13.1
MSSQL2012 is set to run on = 11.12.13.2
IN DNS:
A Record: Mike-PC = 11.12.13.1
A Record: Mike-PC = 11.12.13.2

If I want to use MSSQL2008 by specifying Mike-PC as the DNS name, how would I do that with 100% accuracy? If there is another way to get the job done, I am more than willing to approach this differently.

View 3 Replies View Related

Can You Mirror 64-Bit SQL -&&> 32-Bit SQL

Mar 15, 2006

We have a new server that is 64-bit and I have been approached about using the old 32-bit SQL Server (after a complete format and reinstall) for a mirrored server.

Either that, or buy a new Server with less power that would be used in times of need.

View 4 Replies View Related

Mirror

Dec 7, 2007

I have two sql 2005 servers. I want to do mirroring. My question is does mirroring supports store procedures, ssis packages, user log in ids also? I know mirroring supports database level. In this case, mirroring should supports sp and indexes also. Am I right?

View 3 Replies View Related

When To Use A Mirror

Jun 21, 2007

My client wants to provide a redundant server at a remote location. They want to have db access in a worst case "building burns down" scenario. Can I use mirroring to a remote location? Do I want to use mirroring to a remote location? What happens to my mirror set when some knucklehead with a backhoe digs up my high speed cable?

Am I better off, in this case, sticking with log shipping and manually bringing up the stand by server?

View 1 Replies View Related

Server Mirror

Dec 19, 2006

Hi all,
I feel happy to post my first thread at dbforums.com, and I think it's the community of DB professionals.
my question is:
I have two servers in my organization, one of them is running an SQL server database. Is there any way to make the second server a mirror of the first one and to make it runs automatically as the main server when the first server is down?

Thanks for replying soon.
Haytham

View 4 Replies View Related

How To Mirror A Table Exactly When It Changes?

Aug 6, 2007

Hi folks,

I am creating an external database for a legacy application and one of the requirements is that every table has to be declared twice in the database:

TABLE_READ and TABLE_WRITE.

Now the idea is that as soon as a change is made to TABLE_WRITE, these
changes should be reflected in TABLE_READ.

Now there are dozens of these tables, and I dont want to have to go
through and write AFTER UPDATE/INSTERT/DELETE triggers for all of
them. Is there an easier way?

- can you use replication within the same database instance (ie, replicating tables instead of instances)
- some kind of table mirroring within a single database?
- is there a good solution using tsql/stored procedures?

Thanks in advance,

Alex

View 2 Replies View Related

Mirror Backup

Feb 20, 2007

Hello All!

I have a very simple question.
Im studying for 40-431 and just finished reading about Log Shipping.
It says in the book that when using Log Shipping you cannot run any other log backups because that would affect the LSNs. Can one do log backups with mirroring - principle and mirror?
Because mirroring uses log to keep the mirror up-to-date and synchs on LSN.

Thank you

View 1 Replies View Related

DB Mirror Problem

Jun 21, 2007

Hi,

I've got a problem with the DB mirroring in SQL 2005.

Configuration:

Server 1 Name: ABC001 NIC#1: IP 10.1.1.1 no DNS Server, no Gateway NIC#2: IP 192.1.1.11

Server 2 Name: ABC002 NIC#1: IP 10.1.1.2 no DNS Server, no Gateway NIC#2: IP 192.1.1.12

NIC#1 is an cross-over between the 2 server, NIC#2 is related to the local network.

I cannot setup the mirror with the built-in tool nor by hand.

With the tool I receive the message:

"

One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again.

The syntax for a fully-qualified TCP address is:
TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>

"

By hand with (Endpoints are ready, added by the tool):
-- Specify the partner from the principal server
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://ABC002:5022';

also
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://10.1.1.1:5022';

makes a problem like "other server cannot be reached"

I can ping the other server and use windows sharing. I have create an host entry for ABC002 to use the NIC#1 alltime.

Has anyone an idea how to resolve this?

Thanks!

View 1 Replies View Related

Replicate Or Mirror

Feb 21, 2007

Hi,

Trying to build an replication and subscription beteween db in SQL 2000. The db holds our business system (Dynamics NAV, earlier Navision) and several of the tables include $, spaces and even :

We can take some of this away when creating a new db or make scripts for some actions but I wonder if the mirroring in 2005 could handle this?

Thanks,
Mikael

View 3 Replies View Related

SQL Accounts In Mirror

Jan 26, 2007

Im pretty new to DBA world

We have a SQL2005 Standard setup with mirror and witness

I create a Database in the Principle, create a SQLLogon account and give it permission to the database. All works.

I then fail the databse over to SQL2 and the database is there, it has the SQLAccount I create at the database level, but a logon does not work. I notice there is not login account at the database level and If I attempt to create one, I am told there is one already. I try to assign permission to that account for the database and it again replys that there is already on.

Is this refered to as an orphaned logon?

I was a post on Moving logins from on server to another, is that what I must do?

THank you

View 7 Replies View Related

MIRROR And Continuity

May 30, 2007

I have a question and excuse me if I look dumb ... but I can't MIRROR MASTER or other dBs. If I lose the server then (primary) ... will the db still be open? do I not require some of these non-mirrorable database to run?

View 3 Replies View Related

Backup With Mirror

Mar 26, 2007

Hope this is the right thread,

When you backup with Mirror?

Is that concurrent to those devices and if so, what happens if one fails does the entire backup fail?

Cheers,

Casey

View 1 Replies View Related

Re-establishing Mirror

May 8, 2006

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

View 16 Replies View Related

SQL Mirror Issue- Web.config

Oct 24, 2007

I've setup sql mirroring which works fine. my problem is that when the mirror takes over from the partner that means the IP address of the SQL server has changed which is coded into the web.config file.

How can I get my web.conig file to recognise 2 sql servers that are being mirrored so it can see whichever one is the principal.
Currently, when the mirror takes over my web application does not work as the IP address of the sql server has changed.
Does anyone know the fix for this?
thnaks

View 1 Replies View Related

Can You Mirror A User To Another Userid?

Jan 24, 2006

I have a user which has the currect rights to do what he needs but others with the same permissions (from what I can tell) can not do somethings. It looks like I have 2 options

a.) find a way to mirror the working one to 30 others ids
or
b.) find some tool that will do a detailed comparision of the two ids

Any suggestions/tools(free)/scripts that would work?

Thanks

View 1 Replies View Related

Sql Version Mirror-replication

Jun 13, 2007

Hi i have tried a search on this topic but didn't find much.

I have my fiest sql db live on my we server and all works fine, more luck than skill i think.

But now i wory about backing up my data i have tried various tools backing up the db every 5 minutes and then sending it too another server via ftp this tool works fine but when we have a few people on and the db is accessed frequently it can't back it up i assume this is to do with the backup tool needing an exclusive lock on it.

What is the difference between Replication and data mirror both of these seem to be talked about alot and is there a simple guide somewhere on how to set these up.

Or if i am coming at this from totally the wrong angle then please let me know

Thanxs

View 12 Replies View Related

Reporting Off Of A Database Mirror

Aug 24, 2006

All,

I have created an asynchronous database mirror and a snapshot of the mirrored database similar to what is described in the following article:

http://msdn2.microsoft.com/en-US/library/ms175511.aspx

The article says that once you create the mirror that "At this point, new client connections can be programmatically directed to the latest snapshot."

My question is, how can you programmatically direct connections to the latest snapshot?

Does anyone have a suggestion, or know what the article is getting at? We will use SQL Server Reporting services to query off of the database snapshot.

Thank you,

Racer-D

View 2 Replies View Related

SQL Data Mirror Question

Aug 24, 2006

Does any one have any recommendations concerning replication when it comes to hardware failure.

I have many sites with one SQL Server in each - i'm contemplating the installation of hot spare servers in some locations - should a server die - I can put the hot spare into production fairly quickly, mainly because the DATA would Already be on the server - in other words a restore would not be needed thus lessening the time to get the spare server running etc etc.

I'm exploring a third party tool called "Double-take" - byte level replication

But then i read about SQL2005 (which we have not yet implemeted) that may have a built in SQL mirror possibility - (i might need primary server - a Secondary - and a "witness" server which allows automatic failover)

Any one's advice would be graetly appreciated.

Gibson767

View 1 Replies View Related

Merge Replication And Mirror

Dec 5, 2007

Hi ALL,

I have a problem with merge replication and mirror. I have
Principal server: B is subscriber of A.
Mirror server: D is subscriber of C.
F is distributor and also witness.
D is mirror of B.
C is mirror of A.

I also set parameter PublisherFailoverPartner for Merge agent and log reader agent.

1. Merge replication between A and B is ok
2. Mirror between D and B is ok
Mirror between C and A is ok

3. When i failove, merge replication between D and C does not work. The message is shown

The merge process could not connect to the Publisher 'SGIMIS-2:Mis'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036

Thanks for your supports. You can email me via buihuynhmai@yahoo.com directly.

View 3 Replies View Related

Looking For Suggestions Cluster Or Mirror

Oct 2, 2006

Hello,

We currently have one instance of SQL2k5 SP1. We have a couple of publications, and 30 subscribers, on the instance and are considering going to either a cluster environment or db mirroring. Currently our instance seems to be busy and I am wondering if clustering really gives it a performance boost. What are your thoughts/suggestions on going to a cluster environment versus just db mirroring? Can mirroring be used for real-time failover as we need to add that as well? Thanks in advance.

John

View 2 Replies View Related