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
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 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 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?
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 Replies View RelatedI've got canned alerts set up on both my Principal and Mirror servers to monitor matters. One of these alerts is to send out an e-mail should the transaction log file size exceed 90% of capacity. No issues with this until last night. I had to make some configuration changes on the mirror machine which necessitated a reboot. So, I paused mirroring, cycled the machine, made sure SQL Server was back up and running and then re-started mirroring. All appears to be hunky-dory except for the fact that I am now getting spammed with alerts that the transaction log on the mirror is 100% full.
The T-log on the principal is well under the threshold (generally runs at 1-20% full between backups).
There are no space issues on the t-log drive.
Snapshot of the mirror DB looks normal and up to date.
I'm running a checkdb (on the snapshot) to see if that shows anything. Since the DB is 2.4 TB in size, though, even a PHYSICAL_ONLY takes a while.
Anyone have any clues?
We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.
We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.
How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.
When Create Mirror Database Server, Where need to store the Transaction Log backup file?I took FULL Backup of my Primary Database, and I restored at my Mirror Server also. When I try to create a Mirror Database."The remote copy of database "<db_name>" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:1412)".I am misplacing the Transaction Log backup file. Where I need to store that file?
View 10 Replies View RelatedI have a DB that has a DB filesize of 6 gb and a transaction log filesize of 66gb!!!! This DB has a potential for alot of growth because it holds scanned documents for retention and they are in the process of scanning in several years worth of documents from what I understand.
I have my maintenance plan setup to backup the DB full overnight and transaction log backups hourly during the day. usually my transaction log backups are small - but on friday, the transction log backup was 66gb and it filled up the disk. after doing some cleanup, I ran a full and then a transaction log backup and everything seems OK. But the transaction log file itself remains at 66gb!
I'm just wondering how I can optimize my Database AND especially my maintenance plans so that the transaction log gets back down to a manageable size?? Can I or should shrink the transaction log?? Should my DB be set to 'autoshrink'???????
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies View RelatedHi,
Can we include a Send Mail Task inside a transaction?
We intend to callback a sent email in case there is an error in the subsequent task, if it is possible.
Thanks and Regards,
B@ns.
When I do a data flow task like following, always blocking
1.get data from data source
2.conditional spit by column='Y' or 'N'
3.when column='Y' insert to table a
4 when column='N', ole db command, update table a
but when I run this package, I see there are some rows are 'Y', some rows are 'N',but they can't run together.
Why? I've took long time for the issues.
When I change this data flow to a data flow plus t-sql task, and change ole db command( update table a) to execute tsql task.
The issue can be solved. But I want to know why i can't put these in one data flow?
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @@error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @@error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Help
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
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 RelatedHi,
I have a string almost 11006 length.. when i try to send back from SQLCLR procedure
it says cannot send ..
here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."
Max limit to send a string using pipe is 4000
How I can send a string which is large in size than 4000.
Thanks
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
With Thanks
M.Mahendra
does any one have and example of how to embedd a flash swf file onto a report.??? Is it possable? any examples would be helpful.
View 1 Replies View RelatedI have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'
[code]....
Hello,
I am starting to use SQL Server 2005. I am having trouble connecting remotely. I tried to enable the 'sa' user and got this error:
Cannot use the special principal 'sa'
Is the 'sa' user no longer valid for remote connections?
Thank you
I have deleted a windows login user under security in SQL 2012 Management Studio. The users have also been removed from the associated databases. When I try to add back the login, I receive the message 'The server principal 'xxxx' already exists'. What do I need to do so that I can re-add the login ?
View 1 Replies View Related
I am trying to grant various users permissions and am using the following sql command
GRANT Delete ON Resident TO hector AS barbara
barbara is a member of the fixed server role sysadmin and has Delete permission on the Resident table.
I log in to the server as barbara and try and execute this command and get:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'Resident', because it does not exist or you do not have permission.
If I just run
GRANT Delete ON Resident TO hector
then everything works fine but the grantor is then dbo. I want to be able to have it recorded that barbara was the one that actually gave hector permissino to delete on the Resident table. Is there a way to do this?
I can successfully execute my package via Business Studio but when I schedule it in SQL Agent I get the error message, 'could not get proxy data for proxy id = 2.' I am trying to execute the scheduled job with that proxy (call it abcd_proxy) because I have a logon id (call it abcd) with access to a specific network drive folder, which holds a required source flat file. The abcd logon is defined as a sysadmin account. I am unable to see that the abcd logon id is an added principal within the abcd_proxy, which I think is because the abcd logon principal has a sysadmin role. My proxy id = 2 definition looks to be defined the same way that my first proxy id was.
Does anyone have an idea about what the message means and how I can fix it?
I am using asynchronous mirroring. When I try to failover on the mirror server using €śalter database xxx set partner force_service_allow_data_loss€? (having disconnected the primary server from the network) it sometimes fails to failover (it reports that the database is not in the correct state to failover). I can re-connect the primary server, get them sync€™d again, then unplug the primary and it will failover ok.
Any ideas... or is this just an issue that will be fixed in the next SP?
Hello,
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Sorry for my poor english.
I shall appreciate any help about this problem
Have a nice day
Hi all,
I've got two VLDBs on 64bit hardware platform + 64Bit-W2003K Std R2 + 64Bit SQL2005 Std Edition.
If I set this guy up as the Principal, can I have the standby/member on 32Bit HW + 32Bit W2003 Std + 32Bit SQL2005?
Will this actually work?
Regards,
Uday Shivamurthy
Hi,
Here is my testing environment:
Database server: SQL Server 2005 Developer Edition with SP1
Application: An application developed by Visual Studio 2005 using C# (.Net framework 2.0) and ADO .Net 2.0.
Principal server: computerA
Mirror server: computerB
Witness server: computerC
Mirroring mode: High availability with auto failover
Connection String:
Data Source=computerA;Failover Partner=computerB;Initial Catalog=test_mirroring;Persist Security Info=True;User ID=sa;
Code used for failover:
while (true)
{
thisDataSet = new DataSet();
try
{
thisConnection = new SqlConnection(connectionString);
thisAdapter = new SqlDataAdapter(SQL, thisConnection);
thisAdapter.Fill(thisDataSet, name);
break;
}
catch (SqlException e)
{
thisConnection = null;
thisAdapter = null;
thisDataSet = null;
}
}
Test Case:
(Part A) At the beginning of the test, computerA was in principal role. I started my testing application and connected to computerA without any problem. Then I disconnected the connection of computerA to the network by unplugging the network cable of computerA. The failover of database from computerA to computerB was carried out without problem. computerB was in principal role at that time. The application was pending for about 45 seconds and running again without problem. Then I re-connected computerA to network and it became the mirror server. computerB was still in principal role. Up to this point, all works fine, but the problem was coming next. (Part B) I disconnected computerB from network, database failover occurred, computerA became principal again. But my application cannot switch the database connection to computerA and then kept pending. Then, I re-connected computerB to network. It was surprised that the application switched the database connection to computerA successfully at that point.
My questions:
1. I think Part B is abnormal, isn€™t it? The application should be able to failover from computerB to computerA, because it works fine when failover from computerA to computerB.
2. Is there anything wrong in my code leads to the abnormal behavior of the application in Part B.
3. How can I achieve Part B? Any suggestion or idea?
Thanks for your attention.
Is it correct to say
in any case exept when data or log file not available pricipal db will be availabe
and manual failover could be done only from from principal?