Can I Mirror A Sql Server 2000 DB In Sql Server 2005?

Aug 2, 2007

Can I use Databse Mirroring to mirror a DB from 2000 in to Sql server 2005?

How Can I Set Up A SQL Server 2000 Backup / Mirror Server?

Oct 25, 2004

Hi All

Hope someone can help.

I have an urgent requirement to setup a backup SQL server 2000. This will run as a mirror of the live SQL server 2000 which processes about 5000+ statements per minute. I will need to keep the mirror server as close to the live as possible, so if we need to take down the live server we can switch users to the backup server (it�s ok I can do this in code � client side.) and I may also be reporting from this server to take some of the strain from the live server (if that�s wise � not sure yet).

Can someone please point me in the right direction? What other software (apart from MS SQL server 2000) will I need?
What sort of link do I need between both servers? (note: I don't want to do this link using triggers for each table if possible)
Are there any pitfalls to look out for (i.e. I seem to remember something about auto id fields in tables but this was a long time ago so I could be wrong).

Please note I am a programmer with a lot of experience, but I don�t know that much about setting up SQL servers (well I can install one locally on my dev machine, but that�s really as far as I go) but I am willing to learn and investigate anything, so just giving me sites that I can look at will be enough.

Thank you in advance.

Eamon.

View 2 Replies View Related

Mirroring :: How To Reduce LDF Size While Mirror Enabled With Mirror And Witness Server

Jun 14, 2015

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 Related

SQL Server 2005 Failovery From Only Mirror DB

Nov 20, 2006

Hi Everyone;

I have a SQL Server 2005 DB server (SRV1) and a mirror DB server (SRV2) without witness server. I saw that there is a "Failover" button on Mirror window of SRV1. When click it servers changes roles.

My question is that: If my mirrored DB server crashs and I can not open windows or if I lost all data on SRV1, how can I failover from only mirror DB (SRV2)?

Thanks in advance.

Senol Akbulak

View 6 Replies View Related

What Licensing Is Needed To Mirror A SQL 2005 Server?

May 7, 2008

Hello,

I hope this is in the right forum, I'm new to MSDN. I'm also not an expert in SQL, so please bear with me as I was unable to find an answer by searching the archives.

We have a single SQL 2005 Standard installation with 30 user CALs on a Server2003R2 box. We have another Server 2003 box in a remote but connected location that we previously used for backup replication. What we'd like to do is use some of the replication features in SQL 2005 to replicate the data over every night to the other box, onto which we would install another copy of SQL 2005. This would not be a server accessed by clients, but simply a "live backup", that in the event of a catastrophic failure, could be manually set up to operate as a SQL server in place of the failed primary server. I don't need any kind of automatic "failover", just the ability to replicate the data over to the other system so I have two SQL Servers that synchronize from one to the other overnight.

I was told that I wouldn't need to buy the user CALs again, that the 30 user cals would translate over if the primary server fails and has to be replaced by the cloned server. But what do I buy for the SQL server itself? A boxed version is a bit pricy and comes with 5 CALs which apparently I wouldn't need. Then I noticed that on softwaremedia.com the open license version, which can be bought a la carte, lists a requirement of buying at least 5 cals or a processor license. What exactly do I need to do this, and how can I get it for the least amount of further spending?

Thanks!

View 3 Replies View Related

Why SQL Server 2005 Database Mirror Can't Failover?

Jun 28, 2006

Hello All,

I've tried to replicated exactly what it says in the online documentation. At the end of the post are the SQL statements issued.

1. Enable encrypted outbound connections on the primary server
2. Enable encrypted outbound connections on the mirror server
3. Enable encrypted outbound connections on the witness server
4. Enable encrypted inbound connections on the primary server
5. Enable encrypted inbound connections on the mirror server
6. Enable encrypted inbound connections on the witness server
7. Set mirror''s partner to the primary
8. Set primary''s partner the mirror
9. Set principal��s witness.
Every step run''s normally,but why can''t auto failover.

I used the following Script:

/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO

Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop

create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go

Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO

ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO

ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO

select * from sys.certificates

/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */
--On Mirror
--Remove the database
--Connect to either partner.
--Issue the following Transact-SQL statement:

ALTER DATABASE Mirrortest SET PARTNER OFF

--Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:

--RESTORE DATABASE Mirrortest WITH RECOVERY;

RESTORE DATABASE [Mirrortest] FROM
DISK = N'D:BackupCertMirrortest.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
select * from sys.endpoints

drop endpoint Mirror_Endpoint_Mirroring

select * from sys.certificates

Create certificate MirrorCertForPartner
with subject ='this is the certificate for mirror',
start_date='06/25/2006'

backup certificate MirrorCertForPartner
to file='d:ackupcertMirrorCertForPartner.cer'

CREATE ENDPOINT Mirror_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (
Authentication=Certificate MirrorCertForPartner
,ROLE=ALL)
GO

/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */

--On Witness

USE [master]
GO

select * from sys.certificates

drop certificate CertForDBRole
GO

create master key
encryption by password='asdf'

Create certificate CertForDBRole
with subject='this is a certificate for pricipal role',
start_date='06/25/2006'

backup certificate CertForDBrole
to file='c:CertForDBRole.cer'

select * from sys.endpoints

drop endpoint Witness_Endpoint_Mirroring

CREATE ENDPOINT Witness_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7000)
FOR DATABASE_MIRRORING (
Authentication=Certificate CertForDBRole
,ROLE=ALL)
GO

/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO

Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop

create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go

Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO

ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO

ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO

select * from sys.certificates

/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
use master;
go
DROP USER MirrorLogin1
DROP LOGIN MirrorLogin1
DROP USER MirrorLogin2
DROP LOGIN MirrorLogin2
GO

Create login MirrorLogin1
with password='MirrorLogin'
go
Create user MirrorLogin1
from login MirrorLogin1
go

Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin1
go

--stop
create certificate PrincipalCertForPartnerPub
authorization MirrorLogin1
from file = 'd:BackupCertPrincipalCertForPartner.cer';
go

Create login MirrorLogin2
with password='MirrorLogin'
go
Create user MirrorLogin2
from login MirrorLogin2
go
create certificate WitnessCertForPartnerPub
authorization MirrorLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO

Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin2
go

ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.10.116.42:5022'
GO

ALTER DATABASE Mirrortest
SET WITNESS = 'TCP://w7mis02:5024'
GO

/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */

use master;
go
DROP USER WitnessLogin1
drop login WitnessLogin1
DROP USER WitnessLogin2
drop login WitnessLogin2

Create login WitnessLogin1
with password='WitnessLogi*n@'
go
create user WitnessLogin1
from login WitnessLogin1
go

create certificate PrincipalCertForPartnerPub
authorization WitnessLogin1
from file = 'c:ackupcertPrincipalCertForPartner.cer';
go
--***************************************
Create login WitnessLogin2
with password='WitnessLogi*n@'
go
create user WitnessLogin2
from login WitnessLogin2
go

create certificate MirrorCertForPartnerPub
authorization WitnessLogin2
from file = 'c:ackupcertMirrorCertForPartner.cer';
go

Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin1
go
Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin2
go

All runs normally,But how can't be auto failover?

View 5 Replies View Related

Mirror And Witness Connection In A Disconnected State Immediately After Adding Witness Server To Mirror Session.

Jan 31, 2008

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,

View 9 Replies View Related

Memory Configurations For SQL Server 2005 EE X64 With SQL Server 2000 EE X32 On Windows Server EE X64 Server

Apr 20, 2007

My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.

I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?

View 8 Replies View Related

SQL Server 2000 Databases To SQL Server 2005? Enterprise Manager And Management Stuio At The Same Server?

Feb 13, 2008

When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.

Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?

TIA,
Jeffrey

View 4 Replies View Related

Install Sql 2005 Instance With Reporting Service 2005 On Sql 2000 With RS 2000 Server

Aug 18, 2006

Hi

We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.

If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?

Please help us.

Regards,

Sankar N

View 1 Replies View Related

Problem Occurs When Migrating A SQL Server 2000 Database To A SQL Server 2005 Server

Sep 20, 2006

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->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)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

View 9 Replies View Related

How To Move Sql Server 2000 Database From One Server Box To Server 2005 On Another Server Box Sql

Aug 10, 2007

Hi,

We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.

When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?

Thanks.

View 1 Replies View Related

Feb 6, 2008

Our software vendors upgraded their software and our environment - and applications do not interface as they ought. The vendors do not appear to have any further ideas (to those listed below) and at this stage neither do I. It is now a week since the *upgrade* went live. Looking for new suggestions....

Full story (epic) below. Object names have been changed to protect the *innocent*.

Our organisation had 2 applications:-

appBank
appHouse

both running on the respective platforms:

Server name svrCAT:
Windows Server 2000 sp 4 / SQL Server 2000

Server name svrDOG:
Windows Server 2000 sp 4 / SQL Server 2000

Transactions were "posted" from svrDOG to svrCAT via a linked SQL Server, using login dbL1nkLogin.

dbL1nkLogin was made a user of (with relevant table permissions) of the respective databases:

dbForBank
dbForHouse

*** At this point all was working ***

Upgrading appBank to appBigBank required SQL Server 2005 (and more diskspace).

A new server was purchased:

Windows Server 2003 R2 sp 2 / SQL Server 2005 (Server name svrHORSE)

A linked server was created from the SQL instance on svrHORSE to svrDOG using login name dbL1nkLogin.
dbL1nkLogin was made a user (with relevant table permissions) on database:

dbForBigBank

Attempts to "post" transactions were "posted" from svrDOG to svrHORSE via the linked SQL Server fail with
the appBank application generating the following errors:

On first attempt:

************* FILE ERROR *************
Error Code...... 3902-
Error Message...The COMMIT TRANSACTION request has no co
.rresponding BEGIN TRANSACTION.
Table Name......** NOT APPLICABLE **
Description.....** NOT APPLICABLE **
Function....... COMMIT
sys991: Program already in use
COBOL error at 000444 in sys500
Called from 00076A in sys990ssv
Called from 000457 in sys991
Called from 01177D in gls489
Called from 000DFA in gls396
Called from 003961 in gls394
Called from 000258 in glp900
Called from 003DC1 in syp250

On subsequent attempts:

************* FILE ERROR *************
Error Code...... 1206-
Error Message...The Microsoft Distributed Transaction Co
.ordinator (MS DTC) has cancell
Table Name......tblHouseFinancials
Description.....DYNAMIC SQL CALL INTERLUDE
Function....... UPDATE
*FAILED* Processing Job 'WIN/'

* Parameters and controls within the appBank application were verified.

* Password on dbL1nkLogin was reset in the SQL instances on svrHORSE and svrDOG

* Compared the link server settings on svrHORSE to svrCAT:

Security tab: Be made using security context - Remote Logging: dbL1nkLogin / password

Server Options: Collation Compatible checked (on svrHORSE only)
Data Access checked
RPC checked
RPC Out checked

* Made dbL1nkLogin dbo on databases dbForBigBank and dbForHouse.
* Changed default database from master to dbForBigBank on svrHORSE
* Rebooted svrHORSE and svrDOG (multiple times)
* Verified that svrHORSE could ping svrDOG, and vice versa
* Using DTCping.exe, Verified that the SQL instance on svrHORSE could ping svrDOG, and vice versa
* On svrHORSE, Administrative Tools -> Component Services -> Computers has the following settings for "My Computer" Network DTC Access:

Network DTC Access - checked
Allow Remote Clients - checked
Allow Remote Administration - checked
Allow Inbound - checked
Allow Outbound - checked
No Authentication required - checked
Enable XA Transactions - checked
DTC Logon Account - NT AuthorityNetworkService

These settings were already set. My understanding of KB329332 suggest they are correct. As I could not establish the configuration date of these settings rebooted the server to ensure they'd taken effect.

* Loaded SQL Server 2000 sp4 on svrDOG (Rebooted afterwards in case services not stopped and started correctly)
srvDOG now has MDAC 2.8 sp1 (2.81.1117.6) while srvHORSE has a later version (2.82.3959)- I assume sp2. While they are not the same version, MDAC 2.8 sp2 KB 231943 advises that 2.8 sp2 is not available via the web - so I think it's the best I can do).

* Stopped and manually started MSDTC and SQL Server (via Administrative Tools - Services) in case MSDTC was not being started first.

* KB873160 suggests opening port 135 and adding msdtc.exe as an exception in the Windows Firewall. At present there is no firewall on svrHORSE - which I would assume negates this necessity (at present). In Administration Tools - Services there was a service for the firewall running. Stopping this service has no effect on this issue.

* ( The svrHORSE machine was not created from an ghosted image ).

** None of these things have resolved the issue. **

.... to be continued...

KD

View 1 Replies View Related

Can SQL Server 2005 Express Edition Coexist With SQL Server 2000 Enterprise?

Jan 4, 2008

Not sure if this was posted but having a hard time finding an answer.
I'm currently running sql server 2000 enterprise edition on my machine and was wondering what would happen if i installed 2005 express edition and vice versa? I've found many blogs and post about compatibility issues with the 2005 Express Beta version but couldn't find any news with full version. It would be much appreciated if someone can help me out with this problem.
Thanks!

View 1 Replies View Related

Unable To Create Subscriber On A 2000 Server From A 2005 Server Publisher

Dec 1, 2006

Hi,

I'm trying to set up a transactional replication from SQl Server 2005 to SQl server 2000. The Distributor and the publisher are on our server and the subscription is supposed to be on a SQL 2000 server on a different location. Before upgrading to 2005 I didn't have problems - the replication from 2000 to 2000 was working perfectly.

After I succesfully created the distributor and the publication the first problem that I encountered was that when I tried to create the subscription it was giving me an error that I cannot use an IP to acces the server. I realized to fix that issue by creating an Alias in the SQL Server Configuration Manager for the server where I wanted to create the subscription (a push subscription).

Now when I try to add the subscriber it gives me another message "Execute permission denied on object 'sp_MS_replication_installed', database 'master', owner 'dbo'" - so I cannot create the subscriber.

The user that I use to create the subscriber on the 2000 server is dbo for the subscriber database but it doesn't have rights on the master database. Also I realized that on the 2000 server I still have the old subscription but I cannot delete it - for the same reason - no access on the master database. Before upgrading to 2005 I had the exact same rights on the 2000 server.

Any help would be appreciated.

Thanks.

View 4 Replies View Related