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 RepliesSP2 refuses to install for the Database Engine because €œthe previous upgrade did not complete€?, i.e. it detects a suspended installation. The same thing happened to me when I installed SP1. I then remember opening the maintenance dialog and completing the setup, after which SP1 installed just fine.
When I try to do the same thing this time, I€™m presented with the €?complete the suspended installation€? option, which when I run it reports the installation completed successfully. However, in reality the installation still remains in suspended mode for whatever reason. If I reboot and open the maintenance dialog, I€™m still presented with the €?complete the suspended installation€? option. I€™ve even tried to run it more than once, but it doesn€™t help. Needless to say, SP2 still refuses to install.
I don't know why the installation is in a suspended state. I know every part of SP1 installed successfully in the end, and the computer never crashed during the installation. I've been using it every day since then, without any problems. All other parts of SP2 did install successfully.
I created a feedback entry on Microsoft Connect, where you can find the log files created by Windows Installer. The URL is:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259606
Any ideas what I can do to resolve this problem?
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
Thanks in advance.
I have a Server Agent Job that does a full backup of my principal database. I then have a separate job which does an incremental backup of the log files every 20 mins throughout the working day - the incremental backup sends me an email if it fails.
If I establish a failover to the mirror server, the backups continually try to run, sending me an email every 20 mins telling me that it can't back up the database as it's a Mirror.
Is there any way to script a method in the job that checks to see if the DB is a principal or mirror. if it's a principal I want the backup to happen, if it's a mirror I want it to skip the job.
Thanks in advance to anyone that can help
Niels
Hi,
I have a database shown a SUSPENDED mode on both side of Principal and Mirror server. What should I do now?
1). Is the breaking the Mirror sesssion my only choice?
2). If I break it, how do I reestablish the Mirror again quickly. What is my potential data loss?
Thanks,
Hi guys, right now I am facing this scenario several times and I wonder why it can be happen. Both SQL servers are in Enterprise SP1 edition. The mirroring setup can be work fine to me. Once my principal get attacking by DDOS, then mirroring session change the status to 'DISCONNECTED'. However, after DDOS attack solved and line connection gets back on the principal server, the mirroring session still remain to the status ('DISCONNECTED'). If I restart the SQL services on principal server or mirror server, then only the mirroring session change to 'SYNCHRONIZING' status. Does anyone faced this happen before? I need some assistance on it and hope able to get any explanation or solution from you guys. Thanx and have a nice day.
Best Regards,
Hans
Hi,
can we control how transaction send from principal to mirror ?
If application inserting 10000000 rows in one transaction to principal database
how infomation will be transfered once it is commited and where it will be stored before it is replayed on mirror database?
1. Is it going to be 1 big data packet ?
2.is it going to be split on many packets (of what size ?)
Thanks
Alex
Hi there.
Imagine this scenario in High Availability mode :
The principal sends a transaction log to the mirror, then the mirror saves the log on disk and then notify the principal. What happen if the acknowledge is lost, because a network failure(back on track in 1 or 2 seconds) and the principal dont receive it ?
In this case an automatic failover will not occurr, and i dont really know what happen next...
Will the mirror resend the ack ?
The heartbeat mechanism plays an important role in solving this scenario ?
The principal must receive the ack to commit the transaction, and i´m not seeing how the problem is solved.
I have one other question. In "Database Mirroring in SQL Server 2005" document, is said that "...unlike a distributed transaction, failures to commit on the mirror will not cause a transaction rollback on the principal".
But in this case the mirror will be inconsistent with the principal. How is this problem solved ?
Thanks in advance.
Hi!
Is it possible to use teamed network adapters for Principal and Mirror in a quorum?
I need to use a VLAN networkconnection in order to setup a Synchronized Database Mirror with Witness session.
Thanks in advance!
In documentation about SQL mirroring, the only thing about version requirement are that: 1) enterprise, standard, developer edition; 2) SP1 for official support
Is there any requirement for the Principal and Mirror SQL instances? For example, can one be Enterprise, the other one be Standard? Can one be SP1 and the other one be SP2?
It seems we are having some problem with the above scenario, not sure if that's supported..
Thanks!
We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror.
So if A is the principal and B is the mirror, A to B fails but B to A works.
If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works.
Any suggestions?
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.
I want to restore my SQL Server 2000 database backup to SQL Server 2014.
View 4 Replies View RelatedWhen I'm trying to create a database diagram for my tables., First it prompts me saying that - "This database does not have one or more of the supported objects... Do you wish to create them? I know this is normal when you don't have any diagrams. But after clicking "Yes", I get the following error which is attached.
I tried changing the database Owner to "sa". But, still the same error. Interestingly, other databases that are already having the database diagrams (created by others), are allowing me to create new diagrams in those databases. The only difference is, I created this database where I'm trying to create diagrams on. How do I get this working.
Below is Error Message:
I need to be able to create & connect to temporary databases programmatically, and NOT using the command-line, within a C# program. How would one go about doing this?
View 7 Replies View RelatedI can't install the SQL Server 2012 Express version with tools downloaded from [URL]
I'm getting the following error:Â
Error installing SQL Server Database Engine Services Instance Features
Could not find the Database Engine startup handle.
Error code: 0x851A0019
I'm trying to install a new stand-alone version with a default instance but always getting this error. From the features I'm installing the database engine services (not the replication), all the tools, only the basic management toolds and the SQL Client connectivity SDK.
Further in the installation I'm choosing the Windows authentication and in the list of SQL administrators I've already tried to install as current user but also as System. Both with the same error result.
[URL]
Note: Visual studio is not installed.
I tried to open mdf file without any success. Then, all database files icon were changed or grayed out. How can i repair or replace the file to it's original file? ex... the file named "event.mdf" has now a wrong icon not a database icon like before.
View 11 Replies View RelatedI downloaded the AdventureWorks2012 OLTP script. When I ran it it gave me some errors. I also noticed that it tried to install into the bult-in master database, it created a lot of tables and objects. Some questions :
1) How can I remove everything that the script installed into master database?
2) Do I need to create 1st an empty database and then run the script there?
3) I am running the script from a custom path (C:DownloadsMicrosoft SQL Server 2012AdventureWorks 2012 OLTP Script.zipAdventureWorks 2012 OLTP Script) not sure if this is causing some of the errors as the script, in the beggining has a path to other folders :
:setvar SqlSamplesDatabasePath  "C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA"
-- NOTE: Change this path if you copied the script source to another path
:setvar SqlSamplesSourceDataPath "C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERToolsSamplesAdventureWorks 2012 OLTP Script"
4) Final question, I also downloaded the AdventureWorks 2012 simple database (AdventureWorks2012_Data.mdf). Is this file the same database as the one created by the OLTP script?
We have installed 2014 sql server on a stand alone VM and can create alerts without issue. We recently did a cluster install of 2014, and when trying to create alerts get the following error:
Msg 14262, Level 16, State 1, Procedure sp_verify_performance_condition, Line 52
The specified object_name ('Databases') does not exist.
Specifically, we are trying to create a database alert on percent log used that will kick off a job to back up the log.
We are on Windows 2012R2 o/s. In the drop downs in SSMS under alerts, we do not have all the options we have in the stand alone instance - they are not there. Is this a bug? Is there a CU?
How does one enable sql connections limits for user connections per new and existing databases? how to do it on whole server per database but not set a limit per user.Looks like this must be run on each databases but what if you have 100s of databases:
USE AdventureWorks2012 ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'user connections', 325 ;
GO
RECONFIGURE;
GO
I have an existing, licensed installation of SQL 2008 R2, with one database instance.  I need to add a second instance, but do not currently have access to the installation media.  Can I use the Microsoft® SQL Server® 2008 Enterprise
Evaluation: Trial Experience for IT Professionals installer that's available for download from the Microsoft download site?
I am unable to change the mirror operating mode on a test database mirror I have set up. I installed (from MSDN) the RTM SQL 2005 Std Edition and applied SP1, then set up a mirror, which works fine but only on High Safety mode. I want to use High Performance mode, but the operation mode radio buttons are greyed out for me in the mirror properties page. Also, I tried running:
alter database TestDB set safety off
and recieve the error:
Msg 1473, Level 16, State 6, Line 1
This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed.
Are the various mirror modes not supported on the Std edition or am I overlooking something?
Thanks.
From the db engine server properties:
Product: Microsoft SQL Server Standard Edition
OS: Microsoft Windows NT 5.2 (3790)
Platform: NT INTEL X86
Version: 9.00.2047.00
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 RelatedI've been asked to create a new SQL server and restore an old server's DBs to the new server.
I'd like to generate a list of the DB names using powershell to distribute to their creators, in case some of these DBs are no longer needed.
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
We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:
- Doing Principal and Mirror (no witness)
- Using Management Studio
- Both servers are SQL Server 2005 SP1 Standard Edition
- Same domain account, same user id
- We can telnet to both and connect to both from Stuio.
We were originally getting error 1418, but now we're getting:
"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"
This is what the log file looks like:
------------------------------
2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.
2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.
2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.
2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.
2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.
2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].
2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.
2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.
2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].
2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.
2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.
2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.
2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.
2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.
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.
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.
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.
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.
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 RelatedI recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View RelatedTrying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master]
go
create database [test]
go
USE [test]
GO
CREATE ROLE [ReadOnlyRole]
GO
USE [test]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole]
GO
drop role [ReadOnlyRole]
go