SQL 2005 Log-shipping/replication Linked Servers
Aug 9, 2007
I am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
View 3 Replies
ADVERTISEMENT
Jun 9, 2000
Can some one please give me a quick,brief description on the "Major diffrences" between these to features? Thank You, JJ
View 1 Replies
View Related
Mar 23, 2004
Hi There Gurus - Another Replication question for you all:
Can anyone point me in the right direction to get info on using Linked Servers in Snapshot Replication?
We have Replication all scripted & working between Registered servers, but one of our divisions is not allowing us to register their box on the Parent - ONLY a Linked server.
Thanks in Advance Everyone...
RobbieD
View 3 Replies
View Related
Jul 29, 2015
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
View 2 Replies
View Related
Jul 26, 2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
View 29 Replies
View Related
Mar 25, 2008
Hi,
I have:
- SQL 2005 Dev on machine A
- SQL 2005 Dev on machine B
I've made a linked server from A to B using:
- Windows authentication
- SQL Server authentication
However, I ran into the following "problems":
- When creating a linked server from A to B, when I do a "new linked server" I have to give it the same name as machine B.
- Therefore, I can only make one linked server to B (not f.ex. make two, one Windows authentication and one SQL Server authentication).
Is it only possable to make one linked server to a machine running SQL Server 2005?
Thank you.
View 6 Replies
View Related
Jun 2, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a swl statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 2 Replies
View Related
Jul 25, 2006
We have two sql servers using Windows authentication. One is sql 2000 the other sql 2005. When logged in as "sa", I can link these servers and run a SELECT distributed query without any problem.
But when one of our developers runs the same query (he is "dbo" of each database on each of the servers) he gets the error:
"OLE DB Provider "SQLNCLI" for linked server <Servername> returned message "Communication Link Failure"
...
Login failed for user '(null'). reason: Not associated with a trusted SQL Server connection"
Despite that, I, as "sa" equivalent, have no problem so it appears to be a permisisons issue. Any ideas what is going on here?
TIA,
Barkingdog
View 3 Replies
View Related
Jun 3, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 4 Replies
View Related
Dec 5, 2006
I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')
After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".
The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.
Any help with this is greatly appreciated.
Mark.
P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.
View 22 Replies
View Related
Mar 6, 2007
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1. After applying SP2, we get the following msg when running a query against the linked server. I cannot find anything in the SP2 doc that indicates a change of behavior for linked servers. Any ideas ?
Bill
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
View 4 Replies
View Related
Apr 4, 2007
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Toby
View 7 Replies
View Related
Nov 14, 2006
I have installed SQL 2005 (X64) on 64-bit Windows 2003 server (AMD). One of my databases need to connect several Oracle databases on 32 bit servers.
I managed to install Oracle 32 bit Client (was unsuccessful with 64 bit installation) and could set up the odbc (32). But I coul'nt find Oracle under linked servers!
Does anyone know what I need to do?
View 3 Replies
View Related
May 2, 2003
Currently we run log shipping to a standby server located at the same site as the production. We currently move the 2 most important databases every 5 minutes. Everything works fine. We are now being asked to also create a standby server at remote site (we should have a high speed connection to this site). Is it possible or has anyone tried to do log shipping to 2 different servers? Can you log ship from a prod server - to a standby server - and then from there to another stand-by server?
View 3 Replies
View Related
Aug 8, 2003
I have a solution where I have 3 servers to setup in a log shipping environment. The primary and first failover are located at the same location. The 3rd failover is across a slow LAN link. Haven't done it, but inside the wizard can you designate more than 1 server. Also, I have seen back and forth arguments over where the log shipping monitor should reside. With the budget I can't afford a box to just sit there and run the log shipping. Would i be better off putting the log shipping monitor on the standby server that is located at the facility? We have one client where I have the log shipping running on the primary, and the performance impact is non-exsistent.
View 3 Replies
View Related
Sep 15, 2015
I have to set up log shipping from Prod server "A" to 2 different DR servers ("B" and "C")...What do i have to do differently (or additional) using the GUI (ie not using Tsql Scripts) to accomplish this, in addition to the steps that are done to log ship to just one DR server?
View 0 Replies
View Related
Jan 11, 2005
I need some help on SQL server log shipping.
we have about 4 different SQL Servers (sql 2000 standard) where each server has about 7 databases. We wanted to have a disaster recovery plan where there will be a single sql2000 standard server as a standby server. we wanted to have all other sqlserver databases
(about 7x4) replicated or logshipped to the remote standby server.
We thought about to have all the databases logshipped to the standby server. But since the sql server we have are Standard servers, we thought about using Simple Logshipping tool in the resource kit.
Each server databases are having full backup in the morining and there will be transaction log backup every hour. and then it will be overwritten on the following morings full backup and then the transaction log.
1) the problem we have now is if we go with the Stand by server, we need to modify the daily backup plan to have just one full backup and then the incremental transaction log backup(otherwise standby won't be syncronized). we need to have the backups done as per the backup plan we currently have. Can you please advice me on how can we achive this?
2) If you think the replication is the better one, please direct me on which replication is best and can you also please advice me if we do replication how hard it is to administer replication as well as other adminitration task???
I am little bit hesistant to go with replication as it might create more problem when there is any other problems. (Note: these servers are all Prductions servers)
Thanks for your help.
--Ragulan
View 4 Replies
View Related
May 31, 2005
As a finger in the air generalisation would log shipping or replication provide the least impact on a network?
We are moving some servers from our local LAN to a remote location on the other side of an MPLS cloud (anyone understand those network guys?!) and the replication that is currently running will need to move or be replaced. Obviously this traffic is going to have to fit in amongst the general office to internet traffic now where previously it was only on the LAN so we want to reduce the traffic as much as possible.
Many thanks for any thoughts that you might have ...
View 14 Replies
View Related
May 29, 2002
Are there any articles on the pro's and con's for creating a warm stand-by using log shipping vs. replication? Other than the obvious latency issues, is one better than the other?
Colleen
View 1 Replies
View Related
Jul 20, 2000
I recently devised a way to implement Log Shipping on my standby server. It presently working well. We are however a 24x7 shop, and since we backup nightly, we have about a 5 hour window of vulnerabilty (2 hours backup of Prod, followed by about 3 hours Restore on Standby - then apply logs every hour) It has been suggested to only back up once weekly, but that makes me very uncomfortable, even though we have to standby up to date.
Now, the debate is whether Replication for a standby is a better method. I'm not so sure. Isn't it really more for distributed data, OLAP, etc, rather than a reliable method of disaster recovery? And also, doesn't replication have more overhead cost on the server than log shipping (even if dist db is remote) Seems to me there is more chance and places of failure with REPL than LOG SHIPPING.
Any experience or input is greatly appreciated. Thank you.
View 4 Replies
View Related
Oct 8, 2004
We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?
Using:
SQL2000
Down time max of 4 hours.
My concerns are once a failure occurs and we are running production at the offsite facility how quickly could we get back to the primary cluster?
Thanks for any input... :D
View 13 Replies
View Related
Sep 20, 2005
which enables schema changes like new columns, replication or log shipping ? i can't remember which one it was ?
View 1 Replies
View Related
Apr 30, 2008
I just had a general question...
We currently have a production box that log ships several databases to multiple servers. One of these servers acts as our "reporting" server. One of the things that I'm not satisfied with is the fact that if we need to make a change to any underlying stored procedure that is used for reporting, we need to deploy that stored procedure to our production database and then wait for it to be log shipped to our reporting server.
Because of this, we have a ton of stored procedures in production that are never actually used for production.
I'd like to implement replication to get data from production to our reporting box. This way we can update stored procedures as necessary.
Is anyone aware of any gotch's / caveats / issues with having replication and log shipping configured on the same "publisher"? Also, if I recall correctly, now with SQL2K5, any DDL is automatically replicated to any and all subscribers...
Thank You!
View 4 Replies
View Related
Apr 4, 2008
In a SQL 2005 environment, is it possible to replicate AND log ship from one production server to two different backup servers?
Thanks,
Terry
View 4 Replies
View Related
Jul 20, 2005
I need to implement a dirt cheap replication method for some dirtcheap servers. We are using the SQL Server workgroup license. Isthere anything in this that prevents using log shipping forreplication in Workgroup versus Enterprise?ThanksTravis
View 1 Replies
View Related
Aug 17, 2006
Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?
View 1 Replies
View Related
Apr 5, 2008
can anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005.
any help on this would be highly appreciated.
View 3 Replies
View Related
Jun 10, 2004
Hello All,
I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?
Thanks
View 2 Replies
View Related
Jan 22, 2002
I currently have a main SQL Server which had a column on the majority of the tables. This column also had a check contraint on it. I dropped the column and the constraint and I now get the following message when trying to query the tables through a linked server 'OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.'.
Can anyone help
View 1 Replies
View Related
May 10, 2001
Hi All,
I have successfully linked a server and had SQL queries running
OK for a few weeks, but today I get the following message....
"OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time."
(Server: Msg 7353, Level 16, State 1, Line 1)
I've dropped the link and recreated it but I still get the same error
message. Can anyone help???
Thanks
David.
View 2 Replies
View Related
Jun 25, 2001
Hi,
I'm using SQL Server 7.0 SP1.
I have a DTS package that requires me to link 2 servers
and query both in order to get my set of records that I
want to Transform. My query runs fine in QA. I copy and
paste it into the SQL Query window of the transformation.
I click Preview and all that happens is a quick flicker of
the DTS window. When I go to Transformations, there are
no source columns.
Here is a copy of my code that I'm trying to use:
-------------------------------------------------------
declare@start_dteas datetime,
@end_dteas datetime
SELECT@start_dte = date_data_range_start_dte,
@END_DTE = date_data_range_END_dte
FROMSIDDEV.SID_DEVELOPMENT.DBO.T901_RUN
WHERERUN_ID = (selectmax(run_id)
from
SIDDEV.SID_DEVELOPMENT.DBO.t901_run
whererun_type_cd = 'M')
SELECTRUN_ID
INTO#RUNS
FROMCRMDEV02.MDCORE.DBO.T901_RUN
WHEREDATE_DATA_RANGE_START_DTE >= @START_DTE
ANDDATE_DATA_RANGE_END_DTE <= @END_DTE
SELECTT70.*
FROM
CRMDEV02.MDCORE.DBO.T70_MD_UNIVERSE_RELATIONSHIP_M A
NGT AS T70 JOIN #RUNS
ONT70.RUN_ID = #RUNS.RUN_ID
DROP TABLE #RUNS
------------------------------------------------------
Is there an issue with Linked Servers and DTS?
Any help would be greatly appreciated.
Thanks in advance,
Darrin
View 1 Replies
View Related
Aug 7, 2001
FOLKS
I GET THE FOLLOWING ERROR WHEN I RUN A SELECT FROM THE LINKED SERVER
THE LINKSERVER EXISTS AND THERE IS A RECORD IN SYSSERVERS TOO.
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'
I AM NOT SURE WHERE TO GO FROM HERE.
REGARDS
GIRISH
View 1 Replies
View Related
Aug 10, 2001
Hi Gurus,
I am looking for literature to find out Pros and Cons of Using DTS Verses
Linked Servers in SQL 7.0.
My requirement can be done by either DTS or Linked servers. But I would like to know more about resorce utilisation of these tecniques before making a decission.
Can somebody suggest where to look for.
Thanks
sekhar
View 1 Replies
View Related
