Some Clarifications Need In Sql Server For Mirroring
Mar 31, 2008
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
Hi, we have an existing asp.net website with sql server 2000 database, and are looking into upgrading it to sql server express 2005 edition.1. could you please detail the limitations of sql server express edition? if I compare it to sql server 2000 database, will it allow less users to connect to the database? will there be any other limitations compared to sql server 2000 that I should be aware of?2. could you please clarify if sql server express is completely free to use for commercial use?thanks,
When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. This gives a Dirty Read in a result set. The data retrieved in the select statement may not be correct since some other process might have updated the data while it was taken in the select statement.
Now, Can I know what is the use of NOLOCK? In which way we can make efficient use of NOLOCK ???
I'm currently developing an Integration Service Solution using Visual Studio 2005. In this solution I have 4 packages. The first one call the second one that call the third one and so on.
To be able to debug those packages, the child packages are executed by Execute Package tasks, using a file system connection that point to the .dtsx file located in my project folder. It works great.
My problem is that I now need to deploy my packages on SQL Server. Since the deployment server is not the same as the development one, my Execute Package tasks fail - this is logical, they are looking for a file that doesn't exist on the deployment server. I'd like my parent package to execute the child packages located on SQL Server.
Of course, I could modify all my Execute Package tasks to use SQL Server instead of a file system connection before deployment but this is very painful (mainly because I have to do the opposite when I need to debug my solution).
Is there a way to streamline that process so that my Execute Package tasks automatically use SQL Server when I set Deployment configuration and file system connection when I set Development configuration ?
Hello, This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A. When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back? 1. Message is left on the Queue. 2. is the worker procedure triggered again for the same message by the queue? 3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B. For my scenario messages are independent and ordering does not matter. Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
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....
I 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.
Do 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)
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
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.
I 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
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?
Hi, im trying to mirror a database with SQL server 2005. I have completed all the setup for this, including attacthing a copy of the principal database and all the other settings. When I completed the Mirroring wizard, it prompted me to start mirroring and when I accepted, it gave to me the following error:
TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'CTM'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476 ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://DBNAME.domain1.local:5022" can not 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)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
I am unable to add witness server to my mirror configuration. my error log is throwing error'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89'.
Hello, Is SQL Server 2005 mirroring production ready yet. We have two servers and plan to set up mirroring between them. We have the Standard Editon installed on them. Is Standard Edition sufficient or does it need Enterprise Edition?
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...
I have sql server 2005 evaluation edition. And This version doesn't provides Mirroring option.Even after installation of service Pack1. Is there any other edition (Freeware or Evaluation) which provides the Database Mirroring.
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?
But When I deploy the web to IIS server. The I always can't work normally. The system cannot find the file specified..An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ComponentModel.Win32Exception: The system cannot find the file specified
Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
[Win32Exception (0x80004005): The system cannot find the file specified].[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 52 - Unable to locate a Local Database Runtime installation. Verify that SQL Server Express is properly installed and that the Local Database Runtime feature is enabled.)]
Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
I am developing an enterprise class solution using SQL Server 2005 and MS .NET v2 and am tying determine if SQL Server 2005 (which edition and if so how) would be adequate for my proposed solution. Any feedback, tips, comments would be greatly appreciated.
As a background the solution I am developing will be web services based and used by multiple offices around the globe by over 500 users. I have already developed a prototype using a single SQL Server 2005 instance but as this solution is going to be used by offices around the world I want to have an IIS Server and SQL Server 2005 server instance in each office with "links" back to the primary SQL Server 2005 cluster in Australia.
One of my thoughts was to set up replication between the offices that would happen at midnight remote office local time and then set up triggers to update the primary cluster when assoociated data was changed on the remote sites or on the primary cluster. Does anyone know or can anyone suggest alternatives to this strategy?
I effectively need some sort of inter site caching functionality with store and foreward capabilities ...
I´m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.
The tests i´m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.
My question here is: What if i use Sql Server failover mechanism?
I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?
I get a general question about the feacture "Database Mirroring". Is it support by SQL Server Standard. Edition without SP1?? Of i still need to upgrade the server from SQL Server 2005 to SQL Server 2005 with SP1?
Another question: are there any problem if the one machine(server) has installed SQL server 2005 Standard edition and the other with "Developer Editon".
I hope some one can answer my question. Thanks anyway!
I have setup database mirroring with witness server. To prevent unnecessary failover because of network slow or other issue, I changed the timeout setting as
ALTER DATABASE <Database Name> SET PARTNER TIMEOUT 120
Which I understand if connection is broken between principal and mirror, principal database will wait for 120 second and after that only automatic failover will happen.
If this is true, it does not happen in my case. Failover happens before120 second.
I was told that if I wanted to setup mirroring between multiple instance on the same server that each endpoint had to be different number. Is that true? Or can I use the default 5022 for each instance?
I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.
Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.