I'm currently using database mirroring as a hot-standby, without a witness. Everything appears to be running fine, but I'm receiving intermittent errors that appear to state that my servers are loosing connectivity, thus disabling (inactivating) the mirror until the connection has been reestablished.
The errors are listed below, basically, I note a block of log entries for each mirrored database stating that mirroring is inactive for the database(s), next Error 1474, next the connection error, then another block of log entries for each mirrored database stating that the mirroring session is once again active.
Database mirroring is inactive for database 'NetRS'. This is an informational message only. No user action is required.
Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://SQL01NRS:7024'.
Database mirroring is active with database 'Telephony' as the principal copy. This is an informational message only. No user action is required.
I have confirmed that the sessions are in sync, so am not concerned about the data - but would love to get rid of these errors / disable / enable. I'm assumming that if I had setup a Witness, that I would see the mirrored database flip-flopping back and forth between Principal and Mirror.
Please let me know if I can provide any additional details to assist ...
I configured a database mirroring on SQL server 2008 R2 on test environment. However It was not working, after some time. I deleted the mirroring configuration and also deleted database on both primary and mirror instance.
few days later I notices, On Primary, Continuous error events logged with message "database mirroring attempt by user 'domainservice account' failed with error 'connection handshake failed, 'domainservice account' does not have permission on the endpoint state 84.
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 am trying to set up db mirroring between 2 servers and I have everything pretty much set but only problem so far is connecting to mirrored server. When I go through the "Configure Security" setup I connect to the mirrored server using server name "sqlsvr2" and user/password that has access to the endpoint. After this is finished it automatically enters TCP://sqlsvr2:5022 in to the address bar, which it doesn't like, so I change it to TCP://sqlsvr2.domain.com:5022 but then I get no connection when I try to start the mirror. sqlsvr2.domain.com pings just fine and also responds to telnet on port 5022. I even tried using the IP address for tcp connection but it's not connecting. What am I missing here?
Hi, I have Mirroring setup for 3 databases without a witness. The database mirroring monitor shows me that both Principal and Mirror are "Synchronized". SSMS also shows that the both instances are "Synchronized".
However, the following error message appears couple of times in the event viewer: The mirroring connection to "TCP://abc.xyz.com:5022" has timed out for database "DB" after 10 seconds without a response. Check the service and network connections.
Also came across this article. http://support.microsoft.com/kb/947462
Greetings, I am trying to setup database mirroring on two seperate servers running SQL 2005 SP2. I would eventually like to add a witness. I have tried the SQL wizard with and without a witness to no avail. I am now trying to use certificates. I have changed both servers to use a domain administrator logon to run the services. I am getting the following message each time I try to alter the database on the principal after I have setup everything on the mirror server. Msg 1418, Level 16, State 1, Line 1 The server network address "TCP://10.99.99.02: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.
Here is the TSQL statements I am using on the principal server: -- Step 1 create master key encryption by password = 'xxxxxx'
--Step 8 use master grant connect on endpoint::Endpoint_Mirroring to [Mirror_login]
-- Step 9 alter database spot set partner = 'TCP://10.99.99.02:5022'
**** Getting message here **********
-- Checks
select * from sys.server_principals
select * from sys.sysusers
select * from sys.certificates
select * from sys.database_mirroring_endpoints
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
Here is the TSQL statements I am using on the mirror server. This all works fine.
-- Step2 create certificate Mirror_Cert authorization dbo with subject = 'Principal Certificate', EXPIRY_DATE = '12/31/9999' ACTIVE FOR BEGIN_DIALOG = ON
--Step 3 backup certificate Mirror_Cert to file = 'c:Mirror_cert.cer'
--Step4 create endpoint Endpoint_Mirroring state = started as TCP (listener_port = 5022, listener_ip = all) for database_mirroring (authentication = certificate Mirror_Cert, encryption = required algorithm AES, role = all)
-- Step 5
create login Principal_login with password = 'xxxxxxxx'
-- Step 6 create user Principal_user for login Principal_login
--Step 7 create certificate Principal_Cert authorization Principal_user from file = 'c:Principal_cert.cer' ACTIVE FOR BEGIN_DIALOG = ON
--Step 8 use master grant connect on endpoint::Endpoint_Mirroring to [Principal_login]
-- Restore Step
RESTORE DATABASE SPOT FROM DISK = 'C:SPOT.bak' WITH NORECOVERY
RESTORE log SPOT FROM DISK = 'C:SPOT_log.bak' WITH NORECOVERY
-- Step 9 alter database spot set partner = 'TCP://10.99.99.01:5022'
-- Checks
select * from sys.server_principals
select * from sys.sysusers
select * from sys.certificates
select * from sys.database_mirroring_endpoints
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
I 've done the mirroring in sql server 2005 sp1 now I 've to write the connection string for partner and failover server in tomcat root.xml file anyone know how to write the connectionstring?
my normal connection related stuffs which I am writing in root.xml is
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.)]
Hi everyone, I am a newbie on setting up database mirroring and right now I am facing the connection problem during mirroring which is Error 1418 which kinda common problem. I just view through all the threads regarding this problems and BOL also. However, I just based on all the guide to setting it up and the result are still the same.
I did tried on both instance names in my server for mirroring and displayed the same results as well. When I type this t-sql on mirror server :
ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://TSUYOSHI:5022' -- which is the principal server
It shows connection ok to me. However, when I type this t-sql on the principal server :
ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://HAN:5023' -- which is the Mirror server
It shows connection ok to me. However, when I type this t-sql on the principal server :
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://HAN:5023" 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.
I just wondering that why mirror can get a connection to principal but principal can't? Is that any extra configuration especially security settings that needed to be set within the database (MS sql server 2005) ?
I hope I am able to get any assistance from anyone at here and will appreciate on it. Thanx. Anyway, sorry for the inconvenience
this is the erro that I keep getting.... {"Cannot open database "mydb" requested by the login. The login failed. Login failed for user 'XLORDT\xlordt'."} now.. Im using sqlexpress the one that comes with visual studio.. what do i need to do to fix this?
I'm attempting to configure a new Microsoft OLE DB Provider for DB2 connection using the provided Data Access Tool and Wizard. I'm configured for DB2MVS TCP/IP and have my Catalog and other defaults set. When I go to connect I get the following:
Could not connect to data source 'New Data Source': An internal network library error has occurred. A network level conversational protocol error has occurred. SQLSTATE: HY000, SQLCODE: -343
The port I've configured is what is used when we use the IBM OLE DB and ODBC drivers.
I turned on tracing and tested the connection again and recieved only the following in a file that got named DB2MSG1.ATF. We have been struggling with this for awhile, any help is greatly appreicated.
I have a Merge-Pull subscription setup between two SQL 2005 databases. While watching the status messages display for the subscription, I am constantly seeing the following message being displayed:
"The merge process could not connect to the Publisher '{server}:{database}'. Check to ensure that the server is running."
It will display for anywhere between 15 seconds to a few minutes, then it will display the standard "Waiting 60 seconds..." message which is what I'm used to seeing when all is working well.
There doesn't seem to be any network issues that I can identify. I ran a quick script to run ping continuously against the publisher server and verified that the server was constantly "pingable", even when the above message was displayed (which, by the way, was being displayed by using SSMS while connected to the publisher).
Does anyone know what would cause this message to appear all the time - for extended amounts of time, with occasional glimpses of a non-error status?
When the databse file's "copy to output" property is set to "copy always" my data is not saved and when I change it to "do not copy" it gives me the error "cannot attach the database file", and does not open my connection. I dont know what to do.....
We just upgraded our accounting packages database from Pervasive 8.1 to V9.11. MY SSIS extract packages were working just fine. Now I am getting several erros stating that some columns cannot be found:
For example, in the [sysdtslog90] table, I have these errors:
Column "CreditLimitAmt" cannot be found at the datasource. or
Column "RsrvRptky" cannot be found at the datasource. (from a different pkg)
The table schemas did not change, the columns still exist. The SQL I use also runs just fine in the client interface.
Has anyone experienced this? The bottom line is that now the extract packages are broken.
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel source will fail.
I have it so that for each iteration if the transform was successful the file is moved to an archive directory, and if it fails the file is moved to a different directory.
But I don't want the package to be marked as failed. For the control flow tasks I have set the individual components to FailPackageonFailure = False, and for the Data Flow tasks I have set ValidateExternalMetadata = False.
It no use to set the MaxErrorCount higher because I can't guarantee how many files will be processed and how many might fail.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.
"Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
so I tried doing the following
SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSql.Assert();
and then I get this error
"Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
ALTER DATABASE foo set PARTNER = 'TCP://10.3.3.1:1234' I get this error message:
The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed. What does that mean, and how do I fix it?
Hello,I have a Win2K3 Server with SS2005 developers edition. I am working on aWindows XP Pro workstation which has SQL Server 2000 installed as well asthe SQL Native Client. I'm using an MS Access ADP to connect to the serverand for some reason it's extremely slow, even to the point of throwing timeout errors and "can't generate SSPI context" messages. I've hit the MSwebsite and found info on the SSPI error, but none of the items thatgenerate the error apply to my situation. I've tried using the surface areamanager to change the connection to name pipes, name pipes and tcpip etc,but no luck.Is there anything I should be looking at or any known issues that wouldaffect this kind of performance?Thanks!Rick
I have an reporting services (SSRS 2005) installed locally and I am trying to access it locally through : http://localhost/reportserver, but all I get back is this page.
localhost/ReportServer - /
Microsoft SQL Server Reporting Services Version 9.00.3054.00
Also when I try to connect to reporting services through SSMS I get the following error message.
Cannot Connect to Server ServerNameSQL2005
Unable to connect to the remove server (Microsoft.SQLServer.Management.UI.RSClient)
No connection could be made because the target machine actively refused it(System).
About my set up, I have SQL Server 2005 (SP2) installed in a named instance called SQL2005, I have reporting services set up to run under local system and I have gone through the RS config tool and everything in there appears to be correct. U double checked in IIS that the report server folder is "ReportServer" and not "ReportServer$SQL2005". I have set up full read write permissions to all the virtual directories, to my domain account which is also a local administrator. The user logs in under windows authentication (marked in the directory security tab in IIS, for the virtual directory & the default website).
Whenever I start the service however I get the following errors in the windows application log:
Report Server (SQL2005) cannot create the trace log C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__05_15_2008_10_27_52.log.
Also I am unable to deploy my SSRS 2005 project from BI studio with more errors, I get the following exception thrown in a windows. (I am trying to deploy to the root "ReportServer" folder, not a sub folder.
A connection could not be made to the report server http://localhost/reportserver.
System.Web.Services.Protocols.SoapException: Server was unable to process request ---> System.InvalidOperationException: Unable to generate a temporary class (result=1) error: CS2001 "Source File "C:WindowsTempc-zsc7th.0.cs" could not be found.....
I don't have sharepoint installed, I do have the dashboard designer for Performance Point Server 2007 installed though.
I have been on this thing for hours, I have tried appending "$SQL2005" on to the end, stopping and starting RS Service multiple times. Change all the permssions in IIS so that the user can full control over the IIS virutal directory. I am at a loss at what else I could try.
I setup a new ODBC connection to MS SQL Server 2000 on Windows XP and keep getting the following errors when I try to either register a new SQL Server 2000 group within Enterprise Manager or test the new ODBC connection:
Current configuration
- MS SQL Server 2000 -Windows XP -ODBC 3.85.1117
Errors:
I. Within SQL Server 2000 Enterprise Manager
Error: SQL Server connection open
II. When I test the ODBC connection:
Microsoft SQL Server ODBC Driver Version 03.85.1117
Running connectivity tests...
Attempting connection [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.
TESTS FAILED!
Now here is what has me totally confused:
I can use the command line ISQL to access the database on the server and IP is all correct and port is available.
For some reason, when I try to delete old DB connections from the connection manager, it causes compilation errors because it is not removing the references from the meta data. I get Object ID errors where it is still trying to find the connection after it has been deleted. I have tried going to the code behind and searching then deleting the invalid reference, but that still does not work. I have a connection for 2 totally different databases that are no longer being used in the SSIS package and I would like to get rid of them.
As I was developing my SSIS package, I created several variables and tasks ( FTP, WMI Reader Task ). I am now cleaning up, deleting unwanted variables and connections in the design window. I save and build the package and when I load the package, I get warnings that these variables are referenced but can't find them and errors that the WMI connection is not found.
When a package calls a sub-package, it stores the absolute path of the child package in its dtsx xml file in a Connection String property. How annoying !!! . When I deploy this to another machine with a different file structure, it becomes a problem. Why can't it store the path relative to the parent package, which would be typically in a sub-directory under the parent ?
These last 2 days have been nothing but frustration and my deadline is slipping. Any help is appreciated.
Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.
I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.
After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.
Creating a Foreach Loop Container:
1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:ClientsDep CommBEABEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.
I. Created Excel Connection:
Created excel connection called, €œDynamic Excel Connection Manager,€? that initially pointed to one of the excel workbooks. Went to the connection properties by right clicking the connection manager. Expanded Expressions and clicked the ellipsis button to bring up property expressions Chose Connection String in the Property. Clicked the Expression Ellipsis button. Put the following inside the Expression multi line text box: A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties="" + @[User::ExtProperties] + """
Clicked the Evaluate Expression button to get the following:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="" Clicked Ok button Inserted a Data flow task inside the foreach loop container.
II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:
Set the Foreach loop container Delay Validation to true. Set the Data Flow Task Container Delay Validation to true. Set the Dynamic Excel Connection Manager Delay Validation to true. Set the SQL Server Connection Manager Delay Validation to true. Set the Package Delay Validation to true. Package Locale ID set to English
Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.
Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".
I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile]
I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
I ran the package and received the following error in part: OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'E:ClientsDep CommBEABEA_Test_SourcePersonnelExpense_OCCs_051007.xls'."
I did not find anything helpful when I searched for the above errors and would very much appreciate anyone€™s assistance on this issue as this issue needs to be taken care of ASAP.
Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?
Your assistance in this matter is truly appreicated! Thanks!! Lee
I set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank
If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.
Both servers are SQL Server 9.0.2047 Enterprise Edition.
I am in the process of importing an Excel Spreadsheet using the natively connection manager in SSIS 2008. Â There is one column however that is causing me grief.
SSIS has natively chosen the problematic column to be a DT_WSTR(255). Â I have gone into the Excel connection manager's Advanced Editor and altered it to be a DT_WSTR(1000) (see image 1 attached).
I am still getting truncation issues though, as per image 2 attached. Â why this is?
I have read on the web that high protection mode not recommended, except in the event of replacing the existing witness server. But I can't find the reason why anywhere. Can anybody explain? Thanks.
1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?
2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?
for a database mirroring , which stretergy is good. Creating with witness server or without it.?
I am firsttime doing this , so i am posting it to the forum..
and also if we are creating the database mirroring with witness server for automatic failover , will the witness server need the same amount of harddisk space like the pricipal or mirror server.??
