We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:
Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step
failed.
Both SQL Servers are running SP2 with the latest patches.
Can anyone help with the resolutionto this this issue?
I've some trouble with monitoring a database mirroring session. I am testing a lot configuration possiblities, and i use the database mirroring monitor to view the mirroring states.
But in some (most) of my configurations, i only get following informations in the database mirroring monitor:
Oldest unsend transaction, Unrestored Log, Current Restore rate and the Witness Adress. I am working in High safety mode with automatic failover.
Question 1: What are the reasons that all the other indicators (Unsend log, Current send rate, Mirror commit overhead) are not shown?
Question 2: In the moment, i am using certificates for the mirroring communication. I think, my client (who the database mirroring monitor is running) connects with the usual windows authentication to the server and only selects from sys.database_mirroring or something else. So there is no need to create inbound certificates on the partners for the client who is connecting with the mirroring monitor?
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.
So I successfully got the mirroring to run and now I'd like to see what's going on between the two servers ( i'm not using a witness). I launch the Database Mirroring Monitor and its showing the two servers but when I go to the history column I'm not seeing any data movement. I've done a an update on a db with 1500 rows so I was expecting to see this show up.
Also, is there anyway to query the database thats acting as the mirrored db?
Not sure if this question makes sense, but is it necessary to purge old data in msdb tables used by the db mirroring monitor job?
I'm just wondering if an insert into the data table every minute of the day would still be needed a month from now. I'm thinking this data would be useful for the purpose of "alerts" and to have access to its recent history, but other than that, is it recommended (or necessary)? Would these records keep accumulating until manually purged?
when I tried to mirror database giving principal and mirror server names every details I am getting the error
'Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments.To enable database mirroring for evaluation purposes, use trace flag 1400 during startup.'
How to user trace flag 1400 during startup?
Since I am newbie Kindly guide me with step by step procedure.
When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':
The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)
Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.
DATABASE MIRRORING TEST CONFIGURATION SQL Server 2005 STD Principal: AA-AAA-AA01 Mirror: BB-BBB-BB07
A. Create certificates for outbound connection on principal server. Refer to http://msdn2.microsoft.com/en-us/library/ms186384.aspx. 1. In the master database, create a database Master Key. M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>'; GO
2. In the master database, create an encrypted certificate on the server instance. M:DBADMMIRRORINGCREATE_AA01_MIRRORING_CERT USE master; CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring'; GO SELECT * FROM SYS.CERTIFICATES GO
3. Create an endpoint for the server instance using its certificate. M:DBADMMIRRORINGCREATE_AA01_ENDPOINT CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5999 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO
4. Back up the certificate to a file and securely copy it to the other system or systems. M:DBADMMIRRORINGCREATE_AA01_CERT_BKUP BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT TO FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer'; GO
B. Create certificates for outbound connection on mirror server. Refer to http://msdn2.microsoft.com/en-us/library/ms186384.aspx. 1. In the master database, create a database Master Key. M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>'; GO
2. In the master database, create an encrypted certificate on the server instance. M:DBADMMIRRORINGCREATE_BB07_MIRRORING_CERT USE master; CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring'; GO SELECT * FROM SYS.CERTIFICATES GO
3. Create an endpoint for the server instance using its certificate. M:DBADMMIRRORINGCREATE_BB07_ENDPOINT CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5999 , LISTENER_IP = PARNTER ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO
4. Back up the certificate to a file and securely copy it to the other system or systems. M:DBADMMIRRORINGCREATE_BB07_CERT_BKUP BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT TO FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer'; GO
C. Configure server instances inbound mirroring connections on principal server. Refer to http://msdn2.microsoft.com/en-us/library/ms187671.aspx.
1. Create a login for other system. M:DBADMMIRRORINGCREATE_BB07_LOGIN USE master; CREATE LOGIN BB07_MIRROR_ADMIN WITH PASSWORD = '1Sample_Strong_Password!@#'; GO
2. Create a user for that login. M:DBADMMIRRORINGCREATE_BB07_USER USE master; CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN; GO
3. Obtain the certificate for the mirroring endpoint of the other server instance.
4. Associate the certificate with the user created in step 2. M:DBADMMIRRORINGCREATE_BB07_USER_CERT_LINK USE master; CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT AUTHORIZATION BB07_MIRROR_ADMIN FROM FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer' GO
5. Grant CONNECT permission on the login for that mirroring endpoint. M:DBADMMIRRORINGGRANT_BB07_LOG_ACCESS USE master; GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN; GO
D. Configure server instances inbound mirroring connections on master server. Refer to http://msdn2.microsoft.com/en-us/library/ms187671.aspx.
1. Create a login for other system. M:DBADMMIRRORINGCREATE_AA01_LOGIN USE master; CREATE LOGIN AA01_MIRROR_ADMIN WITH PASSWORD = '1Sample_Strong_Password!@#'; GO
2. Create a user for that login. M:DBADMMIRRORINGCREATE_AA01_USER USE master; CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN; GO
3. Obtain the certificate for the mirroring endpoint of the other server instance.
4. Associate the certificate with the user created in step 2.
M:DBADMMIRRORINGCREATE_AA01_USER_CERT_LINK USE master; CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT AUTHORIZATION AA01_MIRROR_ADMIN FROM FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer' GO
5. Grant CONNECT permission on the login for that mirroring endpoint. M:DBADMMIRRORINGGRANT_AA01_LOG_ACCESS USE master; GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN; GO
E. Back up the Principal Database and copy backup files to mirror server.
F. Restore the Principal Database on the standby database server (use NORECOVERY).
G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.
1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).
M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_AA01 --At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server): ALTER DATABASE BESmgmt SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999'; GO
2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).
M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_BB07 --At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server). ALTER DATABASE BESmgmt SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999'; GO
3. Execute the following SELECT on both servers to review mirroring information. SELECT * FROM SYS.DATABASE_MIRRORING
OS:Windows XP Professional Edition SQLServer: 2005(DE).SP2(Have installed 2 instances of 2005 in my local system) I am trying to do database mirroring,via SQL Server Management Studio. 1)configure security setup,(showed success). 2)when i start mirroring,i am getting the following error.
Alter failed for Database 'MIRROR_DEMO'. (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://tp190.DELL.servers.corp: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. (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
------------------------------ BUTTONS:
OK ------------------------------
I have followed the microsoft link and i have enabled the port numbers aswell.
We have set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:
Incorrect syntax near 'Virtual'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause<c/> the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.,00:00:01,15,319,,,,0 Both SQL Servers are running SP2 with the latest patches.
Alter failed for Database 'databasename'. (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://Mirrorserver.domainname.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 trying to configure Database Mirroring. I had do set dbcc traceon(1400,-1) manually using tsql as the server does not start when configring the startup parameter using the advanced properties of sql server configuration using -T 1400.
After using the db-mirror properties and the wizard in management studio i get an error "database mirroring transport is disabled in the endpoint configuration". I also get this error when executing
Hello, I am trying to mirror two databases and i receive the following error to the principal error log. "Error: 1443, Severity: 16, State: 2 ".
I HAVE: 1) SQL server 2005 enterprise SP2 2)ServerA And ServerB in the domain. 3)I have installed both ServerA and ServerB sql server with network system account and the services run both as NT AUTHORITYNETWORK SECURITY. 4) The principal database "Test" is on ServerA, i create a full backup and log backup and restore to ServerB the Full backup and the Log with NORECOVERY.So the database "Test" in ServerB is Restoring...... 5)I Have created to both Servers Endpoint to Port 5022 with AUTHORIZATION=NT AUTHORITYNETWORK SECURITY. 6)The endpoint are both operational (I have tested through telnet command).... 7)I have added NT AUTHORITYNETWORK SECURITY user to both Security>logins and i gave full permittions sysadmin to the user. 8)I am executing the query "Alter database [TEST] set partner= N'TCP://xx.xx.xx.xx:5022'"; successfully from ServerB (Mirror) but from ServerA i receive that error!!!!
I believe it's network account security issue, but i cannot find a solution for that..... (I also tried to add DCServerA$ user to logins on ServerB AND DCServerB$ user to logins on ServerA cause previously i recaieved an login security error!!!)
I am getting the following error when creating a endpoint (that will be used for allwayson) The Database Mirroring endpoint cannot listen for connections due to the following error: '10049(The requested address is not valid in its context.)'.
My physical network card is configured with:
10.9.255.170
and iam creating my endpoint with 10.9.255.82
The creation of endpoint is being done with;
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.9.255.82) FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) GO IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0 BEGIN ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END GO use [master] GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [account] GO
I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!
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....
If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
When Replication monitor shows an error icon, I removed all the publication jobs completedly. After I reconfigured publishing, subscriber,Distribution, why the replication monitor still shows an error icon even though there is no replication job scheduled?
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
I am looking for a way to monitor the space remaining inside of a database and to receive an e-mail alert when that space gets below a certain threshold.
What I want to do is schedule a job to run every hour or so and check to see if available space in my databases is below about 300 megs. If it is I would then like to be sent an e-mail to my cell phone. I have jobs like this already running on my Oracle databases but am not sure of which tables to query for this in the SQL Server environment.
Does anyone know which table(s) would need to be queried to retrieve this info or, better yet, have a script which already written?
Thanks in Advance!
Trea Johnson Database Administrator Vulcan Materials
Greetings All, I was hoping that some of you might be aware of anyfree scripts that were written explicitly for the purpose ofmonitoring a SQL Server database. I want to be able to determineup/down events, Error Log updates, Long running Transactions,Deadlocks, etc. My plan is to use a central SQL Server database as arepository that will query (via t-sql) the target SQL Server boxes forthe events listed above. If one of the events falls outside of adefined range I will use xp_sendmail to alert me via my pager. I havebeen looking at third party tools like BMC Patrol, Quest Foglight, andEmbarcadero Performance Center and although they are very nice theyare also very EXPENSIVE. I am looing for a cheap solution. Any helpwould be greatly appreciated.Louis
Have two servers, one production server and one backup server. Both are Windows 2003 Server having SQL Server 2000 installed.
I have created replication on these servers using a wizard. I then generated SQL scripts to delete and create the replication. These scripts work well, but to test to remove the replication on both servers I used sp_removedbreplication 'DBNAME' on both servers. Not all were removed and on Replication Monitor and sub items plus Log Reader Agent there were added an icon, a red 'X' as in error. The replication seems to work.
Does anyone have any idea on why this happened and what I can do about it. It is not very nice to have errors in replication on a customers servers.
I am trying to mirror the databse with a witness server.
I have sql server 2005 with SP1 or both my mirror and principle and sql express on my witness.
The problem is when I click start mirroring, I get the following error
The server network address"TCP://serveraddress:7026" can not be reached or does not exist. Check the network address name and that the ports for the local and remote epoints are operational.
(Microsoft SQL server, error:1418)
I have pinged the address it works fine . The telnet comes up with a blank screen.
When I look at the the server log file viewer, I get the following message
An error occured while receiving data: 10054 (An existing connection was forcibly closed by the remote host)
I need to monitor my database growth, as few of databases are growing rapidly. My client wants the growth list of my databases. have report of database growth of specific databases, at least of one month.
I've been running SQL 2005 Enterprise SP1 for some time. After installing SP2, I miss the "Database Mirror Monitor"-Feature. Where is it? Did MS kill the feature?
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?
Trying to use WMI to monitor a SQLExpress DB and not getting anywhere. In fact, wmiprvse.exe croaks.
Here's the query I'm running from within CIM Studio: select * from Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases
(Just did a search for SQL and found this class)
When trying to get an instance, WMIPRVSE throws this exception: The exception unknown software exception (0xc000000d) occurred int he application at location 0x7814454d.
Ok to that, then another msgBox shows up with a title of
"Multiview Message" and text as below: Win32: The remote procedure call failed. An error occurred while attempting to retrieve the list of objects.
Any suggestions on this would be appreciated. Or perhaps another WMI class to use?
(XP Pro SP2, current with the patches; SQLExpress and MSDE both running)
Currently there are various teams accessing the database. For costing reasons, we need to track usage.Is there an efficient way to monitor User access to the database.Can we track which user has executed which query(SELECT,insert etc),the login time and such parammeters?
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?
