Mark - You need to get the word out to get more people on this forum. I'm sure that there are alot of people who'd like to get this up and running and myself personally think this is a great step forward for sql server. I'm in charge of a few oracle databases and to get the Oracle Dataguard up and running is a royal pain in the you know what.
1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
Are we missing a service pack on the client?
Thanks,
Kimball
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
Hi,
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
TITLE: Distributor Properties
------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.
Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
need a clue about how to migrate the data from an Oracle applications 11.03 and underlying Oracle 8.05 database to navision 4.0 running sql server 2000
tia
My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference:
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object."
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0
We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side.
I would welcome and appreciate any input.
fudata
Hi,
This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?
Any caveats houghtscomments on doing this?
Thanks,
JGP
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn’t I change the connection string to point the application to the new server?
Hi I just set up a new cluster and everything was working. Now when I try to test failovers like unplug the public network card etc...
The cluster group fails over correctly but my data and log array's dont failover.
So I unplug the network cable from node 1 (active) the cluster group with the IP, name, and Quorum disk fails over to node 2 but the other groups sql data and sql log stay on node1 and dont failover.
Any ideas?
TIA
I have 4 servers, the first two are web app and load balanced the second two are to be configured for failover. Let us call them SvrA and SvrB. SvrA will have active SQL (2000EE) and passive Content (large directory of courses offered) SvrB will have passive SQL(2000EE) and active Content. Both SvrA and SvrB will be connected to a redundant SCSI 18 drive Drive bay (cheap SAN) which will be configured with a) mirrored OS drives, b) a RAID 5 partition for SQL and c) a RAID 5 partition for the content folders.
SvrA & SvrB will be quad processor machines. EE license for 4 processors as 2nd svr is failover for SQL.
My concern is the failover set up for the content portion, or is it the failure of the server that causes the failover? This is quite confusing to me.
Any thoughts would be most helpful.
Hi there,
I am testing the db mirroring, making sure it will auto failover. I've stopped the SQL services on my principal and then I looked at the mirror db is says it's restoring. It stayed like that for 10 min before I enabled the mirroring again. Anyone knows why it's not failing over??????
Here's my setup: SQL 2005 Standard, Server 1 Principal, Server 2 Mirror & Witness.
Hi all:
I was wondering what will happend to the transaction when the cluster failover from one node to another?
In my case the database is in node-001 and I need to restart the server on node-001. So I need to have the database failover to node-002, so I can work on node-001. During this process, will it effect to end-user?
Thanks
vua
Is it possible to failover a single or a selected number of databasesonto a standby server? We're currently log shipping about 12 databasesto a standby and I was wondering if it was possible to randomlyfailover onto selected dbs.My understanding of log shipping is that when the primary server goesdown, the standby takes over. But, does that mean that all of the db'son the standby server now become the primary? Or, can failover occur onselected dbs?The reason I ask is because if one db on the primary, for example, wasto go down, I'd like to be able to bring the particular db on thestandby server online while I fix or restore what's on the primaryserver. Can it be done individually or is it an all or nothingscenario?Your personal advice or links is greatly appreciated.Thanks,Peps
View 1 Replies View RelatedI have a 2 node Microsoft 2000 cluster with a shared storage device. Iwant to create automatic failover for MS SQL 2000 server. I can dothat wit the following options:1. Active/Pasive (one instance installed)2. Active/Active (More then one instance/virtual sql serversinstalled)I have a question about option 2.Is it possible to have more then one instance/virtual SQL server,service only one database for example loadbalancing. ?, or when youcreate multiple instances/virtual SQL servers, does that mean you havemultiple databases ? If not why does a Virtual SQL server has to betaken over by a cluster node that didnt fail. Then you can actuallyuse option 1. Please advise!The documentation is not very clear on this issues.Regards,Nico de Groot
View 1 Replies View RelatedCould not able to connect secondary replica below is the error message I am getting when I tried to failover. Cannot connect to VLDBATEAM.
The secondary replica that you selected to become the new primary replica does not belong to the specified availability group. A possible explanation is that the replica has not been joined the availability group. Â (Microsoft.SqlServer.Management.HadrTasks)
Hi there!
I use different udls for a website which is partly asp pages and partly aspx pages.
Every database is mirrored, so each udl indicates a "failover partner". However we decided recently that one of the database would not be mirrored anymore. For this database we still use in the udl a "failover partner" to redirect the connection to the second database in case of a failover. For the asp pages the failover works fine even if the database is not mirrored, but it doesn't work for the aspx pages.
It would be great if anyone had an idea why the behaviour is not the same for the aspx pages, and if we can still use a "failover partner" in udls even if the database is not mirrored.. if not, what should we use to connect to another database?
Any help is welcome!
idel
How do I test failover to point to the mirrored database instead of the principal without having to stop the primary instance? I would like to just bring the mirrored database on the primary instance to an offline status and see that the same database on the mirrored instance getting activated as the primary. This does not happen if I try to put the database on the primary server "offline" as it complains that any database that is being mirrored cannot be taken offline.
View 1 Replies View RelatedOur reports are connecting to a database snapshot for their data. Periodically this snapshot loads, which means for a minute or two the reports go down. There is however another snapshot available, and between the two one will always be up. Is there a way with Reporting Services to dynamically switch between the two, depending on which one is up? These snapshots are on a constant schedule, so it would also acceptible (but not as nice), to have the database connection automatically switch at certain times of the day.
These snapshots are on the same server, or I would try using a failover partner in the connection string. To the best of my knowledge, this only switches servers, not the database instances.
We're planning on moving some applications to a SQL server. We're likely going to go with 64-bit for obvious reasons. Since these are critical apps, we plan on having a failover cluster set up. We're aware that all nodes in the cluster have to be 64-bit, if that's the way we decide to go.
The question is this: is there any "non-standard" way to have a 32-bit failover solution, perhaps via a 3rd party application? The only reason we're considering this is from a budget standpoint.
"Hurricane" Andrew
Milford, DE
Hi,
I have successfully set-up a DB mirroring. When the pricipal goes down, the mirror picksup and the other way around too. So the mirroring part is working great. However, when I use my web applicaiton (dot net 2.0), it is not able to connect to Mirror server. I get the following error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
My connection string in web app:
"Server=serverAAA;Failover Partner=serverBBB;Database=TestDB;User Id=testUser;Password=testPassword;"
I have checked the surface are config on "serverBBB" and it is set to use tcpip only for remote connections.
Any suggestions are higly appreciated. TIA.
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn€™t I change the connection string to point the application to the new server?
Hi there,
We've recently set up a Principle, Mirror and Witness configuration with the Mirror and Witness in a separate building to the Principle. All three are part of the same domain (DMZ) and are different servers, the buildings are connected via a fiber optic cable. All servers and SQL Server instances are logged in with the same domain admin account DMZesAdmin.
Mirroring is all set-up and the databases are synchronized. Every once in a while some (not all, normally 6 out of 15) databases will switch roles and become active on the mirror. The SQL Server mirroring monitor job then reports:
Date 25/01/2007 12:37:01
Log Job History (Database Mirroring Monitor Job)
Step ID 1
Server DMZSQL01
Job Name Database Mirroring Monitor Job
Step Name
Duration 00:00:02
Sql Severity 16
Sql Message ID 32038
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: DMZesadmin. An internal error has occurred in the database mirroring monitor. [SQLSTATE 42000] (Error 32038). The step failed.
I have no idea, what causes the failover, it could be a slow network or a bad set-up, can anyone give me some ideas of what to do to track down the problem or any experience of what could be causing this, it happens randomly every day or three. No warning and if I go to the mirror and failover back to the principle again then it's all just fine. However I don't want half my databases working on 1 server and half on the other.
Any ideas?
Thanks
Ed
UPDATE:
I've just been looking at the logs on my Mirror and at the same time it reports in this order
Error: 1479, Severity: 16, State: 1.
The mirroring connection to "TCP://DMZSQL01.dmz.local:5022" has timed out for database "WARCMedia" after 10 seconds without a response. Check the service and network connections.
Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database 'WARCMedia' (41). This is an informational message only. No user action is required.
The mirrored database "WARCMedia" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.
Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.
...
This looks like a time out, is there any way to set the TimeOut threashold for Database mirroring or set retry intervals??
We need to start testing our Microsoft cluster servers.
Has anyone heard of any plan or products one can use to test
the failovers?
Please advise
Thanks
Susan