Migrating A SQL 7 DB to SQL 2005

We are planning on upgrading a SQL 7 Server to 2005 SP1. I ran the Update Advisor and one of the issues listed was that the Database Maintenance Plan would not work in Server 2005. The Advisor mentions a tool to use but not which one, which is my first question.

I went online and found some documentation regarding copying the database etc., however, when attempting to copy from the SQL 2000 (DB in SQL 7) to 2005 (I have SQL 2000 and 2005 on my machine) when I try and connect to the 2005 box, its listed as one of the instances but will not connect.

For grins and kicks I backed the database up in the 2000 environment and restored it on a test machine which is in 2005. The Advisor was correct, I am unable to create a Database Maintenance Plan, which I find odd. The Database shows up under the databases but will not show up in the list of db's to create a plan for. So, I attempted to create the jobs and alerts manually.

There has to be an easier way to do this, can anyone help?

Carolyn,
Try the Setup and Upgrade forum instead. You'll have better luck there.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1|||Thread moved from SSIS forum...|||I figured out how to get around this issue. I copied the databases using the wizard in 2000 between sql 7 and 2000, than I migrated the sql 7 db's into 2000. Once the db's are in 2000, open the server management suite in 2005 create a move package and move the dbs into 2005. If the package is created in 2005, you can create a db maintenance plan. I had test servers set up in both the 2000 and 2005 environment so switching wasn't a hassle.

Migrate data from Oracle to SQL2005 64-bit

Hi,
I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
ran into some problems. First I tried using SSMA vith 32-bit Oracle
client got connection with both databases. I was able to create
objects (tables,index etc..) but when I tried to migrate data SSMA
stopped at 2%. I tried the same against SQL2005 32-bit everything
worked fine.
I then changed Oracle client from 32-bit to 64-bit so I was able to
create a linked server but moving data with insert into..Select * from
linked server did take some time, about 1 million rows/30-min this is
too slow!
SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
connected to the Oracle database.
So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
client should I use? Is SSIS a way out of the problem. Could I speed
up moving data via linked server?
Thanks in advance
Regards /PatrikPatrik,
Have you tried the import wizard from SQL Server 2005 Management Studio?
Right-click on the database that you want to import to, choose Tasks /
Import Data ... and go through the dialogs. One of the data sources is
"Microsoft OLE DB Provider for Oracle". Choose that, specify where you want
the data to come from, then where it will go on your server, etc.
(Disclaimer: I have never personally tried an import from Oracle.) This
import wizard will actually make an SSIS package to do the imports for you.
SSIS can use the Bulk Insert facilities so it can be about as fast as
possible, provided the mappings are simple.
RLF
<pama@.unitema.se> wrote in message
news:3d51377a-3f73-49c1-a7aa-9970106b79ad@.8g2000hse.googlegroups.com...
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik|||Hi,
I also have seen many problems connecting to Oracle from SQL Server 2005
64-bit. This version of the client and this patch worked fine for me:
Oracle Client 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
Hope this helps,
Ben Nevarez
"pama@.unitema.se" wrote:
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik
>|||BTW, it is the 32-bit client.
Ben Nevarez
"Ben Nevarez" wrote:
> Hi,
> I also have seen many problems connecting to Oracle from SQL Server 2005
> 64-bit. This version of the client and this patch worked fine for me:
> Oracle Client 10.2.0.1.0
> Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
> Hope this helps,
> Ben Nevarez
>
>
> "pama@.unitema.se" wrote:
> > Hi,
> >
> > I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> > ran into some problems. First I tried using SSMA vith 32-bit Oracle
> > client got connection with both databases. I was able to create
> > objects (tables,index etc..) but when I tried to migrate data SSMA
> > stopped at 2%. I tried the same against SQL2005 32-bit everything
> > worked fine.
> >
> > I then changed Oracle client from 32-bit to 64-bit so I was able to
> > create a linked server but moving data with insert into..Select * from
> > linked server did take some time, about 1 million rows/30-min this is
> > too slow!
> >
> > SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> > connected to the Oracle database.
> >
> > So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> > client should I use? Is SSIS a way out of the problem. Could I speed
> > up moving data via linked server?
> >
> > Thanks in advance
> >
> > Regards /Patrik
> >