I had a website that ran with one server that had SQL 2000 as the DB. I have upgraded to two webservers with MS SQL 2000 on both machines and a load balancer in front of the two servers to handle sending requests to the server with the least load.
My problem is, I am not sure how to start to work my second server with the MS SQL. I need to make sure both DB's are exact duplicates of each other so if a user hits one server they are pulling up the same info as another user hits the other server.
I have a lot of queries running on the ASP pages I use, several pages use search forms for many various processes, and I have several pages that are for users to input information that is to be stored and sent out via email to all the other members.
Not by any means am I a SQL guru of any kind, I am lucky I know what it is
So, can anyone point me in the right direction as to what I should look for or into to work the best possible process to keep these DB's mirrored?
Now these servers are on their own private network using a second NIC card, I did this so the SQL can talk between the servers without using the public network bandwidth.
How do I keep these SQL DB's duplicated?
What are some great suggestions?
Have you done this before? if so, what has worked best for you? Is it hard to do for someone with limited DB experience? (I learn really quick if I know what I need to learn)
At this point I am confused as to what the best practice would be. Help?
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server. 2) Linking x64 or IA64 servers to x86 servers. 3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are: ------------------------------ TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054) ------------------------------ And ------------------------------ The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB". OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
I want to keep applications off of my database server so I have set up an application server (APPServer1). On APPServer1 I have a batch file that bcp’s data from DBServer1 into DBServer2 and is being passed the server name of DBServer2. On DBServer1 I have mapped a drive to the directory of APPServer1 and have created a task to run the batch job and pass the server name. So here’s my problem: when the scheduler runs the job, the bcp to DBServer2 fails, because it can not find DBServer2. When I execute the exact same command line in a DOS Box on DBServer1, the bcp works fine. I have verified that the server name is being passed correctly to the batch job in both methods.
I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.
Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.
I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.
I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?
I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.
What is the best way to run SSIS scripts on different servers without changing connection information. Our test server is ppntt140 and our production server is ppntd110. If I create a script on server ppntt140 what can I do so I can move it to server ppntd110 without changing any connection information? Database names are the same, it is just the server that changes. What is the best way to handle this? Thanks in advance.
We have 1 machine running SQL Server 2005 x64 (64 bit). The other machine is backing this SQL Server up. It is a 32-bit machine, so it requires (?) a 32-bit version of SQL Server 2005.
Would this back-up machine work correctly on a database previously managed by a 64-bit machine and vice versa.
If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.
In SQL server 2005
I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.
Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.
Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Hi. While hardening a ms-sql2000 , I faced with a problem and I`m completely lost ! few days of reading and google searchs didn't gave me any hint...
Here's the scenario : Ms-sql is connected to Oracle , through "MS OLE DB provider for Oracle" . By default MS-SQL runs as SYSTEM , but even if we change it to a "local admin" account , everything works fine . The problem is that it's not wise to let sqlservice to run under privilaged accounts such as system or a member of 'local administrators' . So I tried a normal local user on the host running sql . I fixed every related problem appearing because of using a limited user account and ms-sql works fine in all aspects but one ! While using normal-user account , sql-server fails to load linked-servers and this error pops up in enterprise-manager :
"OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error."
I've tried much to find root of this error ( including any comments from related KB articles... ) but no luck . My guess is that , using OLE requires administrative privileges on host , and as I'm running SqlService with normal user, it fails to use OLE. So I should give requried permissions to the user running SqlService . But the problem is that I've no idea where/how I should do that. I've already tried some registry/file permissions but non of them helped me. Some where I red that using ODBC instead of OLE may help , but that seems fail too !
*Note that I'm almost sure it`s a problem OUT of circle of ms-sql , meaning any modifications should apply OUT of ms-sql , because simply giving local administrative privileges to the user, fix the problem.
Hi, is there a way of how to find all the SQL 2000 servers on our local Network. We have a lot of development servers around the company and I need a tool (or somthing) that could help me find the server name and the version of it.
I'm working with SQL Server 2000 installed in Windows 2000. When i tried to add the 'servername' as a linked server , i got the following message:
" Error: 15038. The Server 'servername' already exists."
When i execute "sp_linkedservers" , i could see the name "servername" in the list. But before doing this i had Restored the "MSDB" database on this server. Should i use "sp_droplinkedservers" and add them again.
Just wondering before I do it, I am in the UK and studying for an mcdba which i require sql server 2000 on my laptop but I'm also doing the sql server 2005 exams aswell at the same time,
Now I know that you can have multiple instances on sql server on one machine but would I be able to have 2000 and 2005 running on the same machine or would it not be advisable
Thanks
-------------------------- You only learn by practicing
HelloI have 2 locations, that each has an MSSQL 2000 server.Since the locations are to far from each other and the connection isvery slow.My thought was to have a database on both servers that should replicate“on the fly” or as asynchronous sync.The problem is that both locations work in the database frequently. Soit’s very important that there are no duplicate IDs.Is that possibly and how?~Peter
These are the steps i plan on taking. 1. sp_help_revlogin gets logins sql 2000 2. Backup from SQL 2000 to SQL 205 3. Change database to 90 mode 4. Change database to check sum and auto stats sync 5. Rebuild Indexes on databases 6. Jobs (i will manually move over) Anything else i should run after the rebuilds
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?
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?
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?
We shall be taking a bunch of 7.0 instances and moving/upgrading to a SQL 2000 cluster server. I was thinking of creating new named instances on the 2000 cluster and upgrading each 7.0 server to it's respective named instance. Also thought of using the 2000 copy database wizard; I was told this didn't always work. Anyone hear of problems with this? Thanks
I was wondering whether it is possible to query linked servers in SQL Server Management Studio Express 2005 if those servers linked to are SQL Server 2000 machines?
I have added some linked servers to express and queried them, however the login timeout is expiring. I thought it might be because of an incompatability.
I know this is an easy one for most of you but this is new territory for me. I am trying to link a production application database to an existing SMS database. Both of these databases are SQL 2000. I have sa access to both of them. I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.
For simplicity I will define them both. Application Database we will call €œA-Database€? SMS Database we will call €œB-Database€?
I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab. In the Security Tab I selected the €œBe made using the security context€? and used €œsa€? for the Remote login and the CORRECT sa password for the With password option. The server options tab remain the same with €?RPC, RPC Out, & Use Remote Collation€? selected.
It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?
We are planning to upgrade vom SQL 6.5 to SQL 2000 and SMS 1.2 to SMS 2.0. We want to first migrate SMS 1.2 to 2.0 with the SQL 6.5 database. Afterwards we want to install SQL 2000 as a second instance on the sql 6.5 server and migrate from there our databases. Is this the best way to do this? Does anybody have experience with running two instances of different SQL-Versions on one server? and what kind of trouble can I expect to run into?
Has anyone been able to run a DTS package from DOS. When I execute the dtsrun command it fires the process off in another Dos session and quickly closes. I know that SQL 7.0 worked ok. When it runs from dos it tell you when each step runs successfully or fails. I am Running Windows 2000 Server SP2 and SQL 2000 SP1. So the question is, is there any way to run the dtsrun utility from dos so you can see what is going on?
I've installed sql2005 on a windows 200 server. now the requirement is to install sql2000 also on the same server.
Would like to know if any have experienced and type of problem on having both sql on same server. and if there is any impact on installing sql2000 after sql2005.
Hi, we have a SqlServer instance in production containing around 10databases.It has just been realised that all the db's are held on the small c:partition with only a gig or so of space left.On the server there is another partition and another hard disk bothwill ample space (few hundred gigs).What would be the best way of getting the data onto the otherpartitions with minimal impact on the applications. Can we move theprimary data files for each db? Should we just create secondary datafiles on the big partitions for all the db's? Is there a method ofmoving all the data at once?Any ideas on how we should approach this?(ps we dont have control of the sqlserver its outsourced, so simplerthe better).Thanks,Jim
I have seen information on the web about running two versions of SQL at the same time on the same server but was wondering any tricks of the trade regarding it. The main recommendation that I have seen is to have SQL 2000 SP4 running first as the default instance (which is my current situation on a development server) and then install SQL 2005 as a named instance on it. Should the SQL 2005 installation place its files in a dedicated directory (ie, C:SQL2005) as opposed to the default directory that the installation would normally write to, thus not overwriting any SQL 2000 system files? Any other recommendations?
When you are inserting/altering a table and you expect values to be added, should you see the number of rows affected at the bottom of the query screen as the time goes by or not?
