The database is in high-safety mode.If off Mirror and Witness both the Principal goes into Disconnected / In Recovery. How turn online this database? Similarly, if off and Principal Witness both the Mirror goes into Disconnected / In Recovery. Is it possible to turn online database?
View 7 RepliesI've read here at [URL] that with 32 bit it's recommended to only have 10 database mirrors per instance.  However, is there a limit for 64 bit SQL Server 2012?
We're having a problem where we have about 300+ databases and now any new db's we add are timing out when it comes to setting up mirroring and am wondering if this is limited by the instance.
Some how SQL Server 2005 database mirroring was partialy set up on one of my servers. I know this because there is traffic from the server to port 5022 for IP addresses 1.1.1.[234] (which means the last octect is either 2, 3, or 4).
I'm getting complaints from the network manager about the traffic and I want to turn this off, but I can't find any documentation on this topic. I've checked the database configuration and I can't find any configuration for mirroring, just the traffic on the TCP port.
Does this mean a background processes (service) is running that needs to be killed or is it more complex than that...
Hi
Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does
1. Automatic failover occur to Secondary server?
2. What does the Database state show as. Primary, disconnected?.
3. what happens to my transactions. Are they lost?
4. Does any data loss occur?
If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?
Any information is appreciated,
Thank you
AK
Hi Everybody,
I need URGENT help with this. Because of space problem my collegue create a second log file on another drive. I wanted to clean the thing, made a detach, renamed the ldf into old, but now it won't attach anymore, I get the following messages:
EXEC sp_attach_db @dbname='TARIFINFO_GEN',
@filename1 = 'F:DataTARIFINFO_GEN_Data.mdf'
Server: Msg 5171 F:DataTARIFINFO_GEN_log.ldf is not a primary database file
Server: Msg 1813 Device activation error the physical file name 'F:DataTARIFINFO_GEN_log.ldf' may be incorrect
Do someone know what to do or where to look for a solution. Many thanks in advance, Isabelle.
We have an architecture of two production SQL 2005 Servers and two mirrors. The traffic has spiked on the two front servers, so the decision was made to add a new server to the top tier. Why the powers that be won't pop for the additional mirror server is another story. The question at hand is whether or not it is possible to mirror the 3 tier I servers to the 2 tier II servers?
Brandon Forest
SQL DBA
bforest@directapps.com
Various posts have noted that mirroring over distance is not advisable or that either async connections should be used.
Are there any limits/recommendations i.e. if two datacenters are a couple of files part with 10GBs fibre links and <50ms response times would this be acceptable for high-availability mirroring?
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
Is there anyway I can turn off delete in SQL server? I want to prevent anyone inadvertently deleting rows in tables. I thought worse case I could have triggers on tables to perform roll back.
View 3 Replies View RelatedHi everyone.
I've just installed sql server 2005 (evaluation) and i need to turn the option of using sp_send_dbmail on. I tried books on line but didnt manage to understand how it is done.
Thanks
Somehow someone turned on a audit on the sql server and it is filling up our hard drive and shutting down sql server eventually. Been trying to google how to shut this audit off but coming up with no via soolution yet. how can I turn this trace off. Each fiel says AuditTrace and date and they happen every other minute. I went into the sql profiler and can pull up the files but how to shut the trace off, it does not say.
View 9 Replies View RelatedI made B server which get logs from primary server A as a secondary server in the log shipping solution.
it always shows RESTORING in B server, it seems not to accessible.
my question is <if A failed down , how to revoke the B server as the primary one>
I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.
Example 1:
field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g
and I would like to get it in the form
Example 2:
field1_field3
1_____a,b,c
2_____f,g
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
View 5 Replies View RelatedI need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Operating system is Windows 2012.
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies View RelatedDo you know if the following excerpt from technet is still true? I want to mirror a device over the net to another server. Has anyone tried this with sql server 6.5 or is it still not a feature?
----------------------
PSS ID Number: Q115043
Article last modified on 04-29-1997
4.2 | 4.2
OS/2 WINDOWS
================================================== ====================
----------------------------------------------------------------------
The information in this article applies to:
- Microsoft SQL Server version 4.2 for OS/2
- Microsoft SQL Server version 4.2
----------------------------------------------------------------------
The use of database devices accessed over the network is not a supported
configuration for either the OS/2 or Windows NT versions of Microsoft SQL
Server. All database devices, including mirrors, should be configured to
use local drives. Database dump devices are an exception to this
restriction.
----------------
Thanks.
Gail Wade
Raymond James and Associates
Clearwater, FL
gwade@is.rjf.com
Hi,
We have 2 servers each having a domain (i.e. 2 domains) running
Windows Server 2003
Standard Edition
Service Pack 1
We would like to have one of the servers replicate/mirror the data on to the other but we are getting this error
The error produced from the mirroring part is:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'CTSM'. (Microsoft.SqlServer.Smo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://UM_db_live.UMlive.local:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
Thank you.
I'm looking at a setup where they have server1 and server2 in a mirroring relationship with automatic failover.Â
Server1 is the principalThey are using transactional replication to replicate asingle databse to server3 is AWS.Distribution database is on Server1All Agents (log reader, snapshot, distributor) run on Server1Server2 has not been set up for replication...My understanding is that in this set up you would normallly place the distribution database on a separate server and enable publication on the mirror, Server2.
What happens if they failover? Replication would stop, and presumably records added while the mirror is the active database would not be marked for replication?How would they recover? Failback and reinintialize
This may sound silly, but I'm calculating how much someone owes over time verses how much they have paid for that period. So if they owe more than they have paid the result is an under payment amount like -$100. Then I need to add the -$100 to the new payment due, let's say $100. So the total Due is $200. But if I add -100 to 100 that equals 0.
Any suggestions?
How do you turn LOGGING OFF for a DTS package????
DB2 is a simple "LOAD DATA REPLACE NO LOG"... (can SQL Server make it any more difficult to find this option to turn on/off??!!!)
frustrated!!!!!!!!!!!!!!!!
thanks-
Hello,In SQL Server 2005 Enterprise, I can change the CPU affinity viaManagement Studio. Is there a way that I can change these settings viat-sql?I wish to use all my cpu's at night when my data warehouse builds andthen durning the day reduce the number of cpus for SQL so that theapplication can get more time.TIARob
View 2 Replies View RelatedEver since installing SQL Server 2005 Express Edition the XP Task Manager shows €œsqlservr.exe€? running normally, as a €œNETWORK SERVICE€? consuming 26MB of memory. This is true even after a cold reboot. I€™d like to turn SQL off since I am not currently planning on using it. I tried detaching a couple of projects I had started, as part of tutorials, but that did not make a difference.
So how do I turn SQL Server 2005 Express Edition off without removing it from the system, which I do not want to do?
Thanks.
Thomas
Can 1 witness server work for 4 Principal and mirror servers?
Needs expert advise.
I have setup database mirroring for one big database.But now Transaction log is full and server is down. What should i do? We do bulk-insert in that database.
View 3 Replies View RelatedI am working on a proof of concept to take to our Technical Board.
This POC is concearned with SQL server 2005 Mirroring for High Availability and Failover in a Highly Critical business sector.
The problem is that we use custom web pages to give the Operations Center an overview of the status of the system and that there are so many metrics available (not may are well documented). I have looked at the following
sys.database_mirroring_witnesses
is_suspended to show the state of the Sync
database_name to return the name of the mirrored database
safety_level_desc to show that the Safety level is at Full
Principal_server_name to show the name of the Principal server
Mirror_server_name to Show the Name of the Mirror Server.
I am at a loss as to what is the best approach as I do not want to display too much Information and confuse.
The Servers are locked down to remove any permissions not required so I have to create a SQL account with only acces to the required sys views. one issue is that the following SQL only works on the Principal
Code SnippetSelect * from sys.database_mirroring where mirroring_guid is not null
The Principal returns
Code Snippet
7 267CF8D3-8066-44EA-84BD-B7EA25C3F779 4 SYNCHRONIZED 2 MIRROR 20 2 FULL 1 TCP://DNGLHRSQL051.XX.XX.XXX:5022 DNGLHRSQL051 TCP://LHRSQL029.XX.XXX.XXX:5023 1 CONNECTED 40000000010300001 10 NULL UNLIMITED
where the Mirror will return nothing..
The configuration of the User is identical on both Principal and Mirror and is as follows
Master Public
MSDB Public, dbm_monitor
I have read that the User also requires "view any Database" but on the Mirror this database is not available on the Mirror as it is in Sync.
Is this an undocumented Feature?
Any help greatly appreciated
Regards
Andy
i have two computers with Sql server 2000 std edition(SP4) and windows 2003 std edition(SP1).
how should i do to balance the loading of sql server and sync these two servers?
The sql server mirroring means that if one server is down, then the other one will replace it?
Hi,
1.) We have a sql server 2000 enterprise edition. Can we implement mirroing on that
2.) If the answer to the above is yes, Can I have mirror and witness as sql server 2005
Regards
skrcmr
I am trying to set up a SQL mirroring senario - I have some questions
Do I need to purchase an extra SQL Server license for the SQL server instance on the Mirror? what about the Witness does that also need a separate license/
Can the witness be a virtual server on the Mirror box itself? Do I still require a license if the witness is on a virtual server?
Thanks
jawahar
My site works fine in VWD2008 express, but I get this error when I try to use it on my live website.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
According to this article: http://support.microsoft.com/kb/914277 I am supposed to:
1.
Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"...
The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.
Can anyone help me with this problem. I have old data that I need to insert into a new table. I need to keep the old identity values and then turn them on again for the new data.
I'd be very grateful for any help.
Thanks
A.
I am running sqlserver 2000 Enterprise edition on Windows NT 4.0 service pak 6.
When ever I run a long query, the transaction log seems to fill up. How do you turn this transaction log off? Please let me know ASAP.
Thanks,
Ranjan
I have this table:
ProductionID ResourceID Time
------------ ---------- -----
WO001 200012 5
WO001 200011 1
WO002 200013 2
WO003 200012 3
WO003 200032 4
and I want it to look like
ProductionID 200011 200012 200013 200032
------------ ------ ------ ------ ------
WO001 1 5 NULL NULL
WO002 NULL NULL 2 NULL
WO003 NULL 3 NULL 4
I am looking for a smart solution without hardcoding the column names (e.g. CASE WHEN ResourceID = '200012' THEN Time END as ['200012'], etc...), as there can be up to 15 different resources involved; usually they aren't but I want to keep the result as slim as possible without tons of columns containing nulls.