Hello ppl,
I’m curious what’s the best way to migrate all SQL databases (with all users) to another server.
Are there tools to do a migration like this or must I do it manual?
Thanks for your time and answer.
With kind regards,
Pedro
P.S. Its SQL 2000 on a Windows Server 2003 OS
We are going away from a 2003 Windows Server OS with SQL Server 2008 R2 to a 2012 Windows Server with 2012 SQL Server. Both the distributor and subscriber resides on 2003 Windows Server and the focus will be to migrate those databases to the 2012 SQL Server.
 We would also like to avoid sending down a new snapshot due to logic in the replication process (major headache to clean up data).  what's the best approach in moving the distributor and subscriber databases without having to run a snapshot? Â
In theory and needs testing:
1. Work with business users to get downtime
2. Stop sql apply so no new changes are going to the Oracle publisher database
3. Remove subscription, publication and distributor from current replication
4. Oracle DBA to clear out replication related objects - fresh clean slate
5. Use log shipping to apply last t-log and restore db with Keep_Replication
6. Set up new distribution, publication (keep existing object unchanged), and subscriber (without initialization) Will this work?
Hi all
I was just wondering on if I have to migrate the systems databases from 2000 ie. Master , Model & MSDB ??
Thanks
Scenario
Recently moved a SQL2000 database to SQL2005.
Collation on SQL 2000 database server was SQL_Latin1_General_CP1_CI_AS
Colaltion on SQL 2005 database server is Latin1_General_CI_AS
After restoring the SQL 2000 database on SQL 2005 we observed that the database collation was SQL_Latin1_General_CP1_CI_AS. We have altered the database collation to Latin1_General_CI_AS. We understand this will work for all new objects created on the database but existing objects (prior to the collation change) will continue to have the 'SQL_Latin1_General_CP1_CI_AS' collation.
What is the best method to change the collation of the older columns to 'Latin1_General_CI_AS' -
1) Run ALTER TABLE ALTER COLUMN <colname> <datatype> COLLATE Latin1_General_CI_AS <nullability>
on each varchar/char column
or
2) For each table
BCP out data
DROP TABLE
RECREATE TABLE (taking the new collation Latin1_General_CI_AS)
BCP in data
3) Other methods?
Thanks in advance
David.
hi, i need some help here,i made a simple data driven website in my development PC, at first, i used MS Access for my data base then i migrate it to sql server express 2005, and it works fine when i try to run it in my development PC with my IIS. but of course i will be deploying it to a real server, in our office were i will deploy my data driven site, we have a PC server Win Server 2003 with SQL Server 2000 Enterprise (im not pretty sure by the name but i know its not a free version, its something like a 2000 sql version with a word enterprise at the end).so my question is, i am developing an ASPdotNET site originally used SQL server 2005 express,how can i migrate my site to the Win Server 2003 and my database from SQL Server 2005 express to SQL server 2000 enterprisewill it work?? if not, what are the possible solution that i can do in cases like mine?i used Visual Web Developer 2008 Express Ed. in making my sites, and its DOES NOT allow databases in SQL Server 2000!!!!!it says "can't recognize the database"i hope you can help me with this.Thanks-SALAMAT PO....=)
View 1 Replies View RelatedHelp needed with Issue's related to side by side migration of SQL Server 7 to SQL Server 2005
View 4 Replies View RelatedWhen 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
Hello,
We want to migrate our SQL Server 2000 Databases to SQL Server 2005 including all objects, sprocs, views, jobs, securty, DTS Packages, etc. Pretty much everything.. If anyone has done this in the past for enterprise level databases, I would greatly appreciate it if they could send me some documentation, references, pointers.
Thanks
Russel
Hi
Our costumer is going to install new hardware.
Actually he is using 2 servers which both have windows 2000 server (+ AD) and sql server 2000 installed. On the new servers he will use windows 2003 and sql server 2005 The hard part will be migrating sql server 2000 to sql server 2005. I have read (not 100%, though) the SQL2005UpgradeTechRef document and downloaded SQLUASetup.exe to be ready (microsoft sql server upgrade analyzer software)
But actually i want to know what is best upgrade path and what are your experience. Is there already a got (or better or perfect) document availabe for the sql server 2000 to sql server 2005 sql server side by side upgrade. Please help! thanks
Hello Gurus:
My database is running right now at SQL Server 2000 Enterprise Edition. We would like to upgrade it to SQL server 2005 Standard Edition.
Since this database was upgraded from MS Access 2000 to SQL server 2000. This database is accessed by a proprietary application. I don't know some of the details about this application.
There is one more issue with this database, replication was implemented earlier and now it is disabled.
I am thinking that to take a full backup from SQL server 2000 and restore it in SQL server 2005. What do you think?
Please let me know if you have any other idea.
I have another question for you. Right now replication is disabled and I restore SQL server 2000 database to SQL server 2005. Would there be any problem when we try to implement replication again?
Thanks in advance.
Regards,
Arunesh
Hi,
We have our databases running in sql server 7 and sql server 2000. Our clients are planning to migrate to sql server 2005 very soon. The front end is VB for few applications and .NET for another 2 applications.
We maintain sql server authentication and have logins created in the security folder.
I was able to download the sql server express and management studio express and restore the sql server 7 db there.
I tried to create a sql server login in sql server 2005 also. But then, the server seems to allow only windows authentication.
Can someone help please?
Also, can someone advise on what change might be necessary in the application when we migrate to 2005.
Thanks in advance,
Narayani.
We are migration few database from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server)
Problem :
- The performance difference observer was not much (Just 15-18%)
- The CPU Utilization goes to 100% immediately, when the software login screen appears and it sustains in between 90% to 100%
- Multi-User Access (3 or more concurrent users), the sql performance is tremendously slow.
Steps followed for Migration:
- We have used the backup and restore method to migrate the data from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server).
- We ran the DBCC DBREINDEX for each affected tables
- We executed the sp_updatestats procedure for all the tables
- Upgraded the DB compatibility version to 90
- Installed the SQL 2005 Enterprise Server (64 bit-Itanium Server) Service pack 2.
One thing that we observed, was the memory usage, on SQL 2000 the sqlservr.exe uses around 1.5 GB where as on the SQL 2005 its just 120 MB.
A normal select query on 2000 Server took 11-12 sec, having 10 lac records, and the same query on 2005 took 1 min (3-5 sec CPU process taken by sqlservr and rest by the Sever Management Studio.
The only differance noted was that on 2000 the entire data was displayed at one go where as in 2005 the data was displayed at the 1st sec, and was filling the rest in the buffer, and to finally display entire data it took 1 min.
Also
when we run this query
select * from sys.dm_os_sys_info
one column os_priority_class shows a value = 32 ? what does this means.
In the MSSQLSERVER SERVICES, properties, there is a parameter displaying value
Advanced > Running Under 64 bit OS = No ? what does this means.
Could anyone pls suggest as to how to increase the sqlservr.exe memory area, and also how the enhance the performance.
HI all,
Could any one pls suggest me how to migrate from DB2 to SQL SERVER 7.0
Thank you all..
---Chow
hi,
We have machine where SQL 2005 server is installed.Atleast 3 web applications are running on it with data size 22GB in all. We need to migrate all data/tables/groups/users from this server to another server.On target server, SQL server 2005 will be installed.After that this migration activity needs to be carried out.what are options available to migrate to target server?
Microsoft migration tool? Any already existing stored procedure etc?which is preferred solution in such case?
Thanks for help
PD
Hi All,
Greetings,
We have
sql server 7
Sqlserver ServicePack SP3
Sort Order = 32
BuildNumber 7.00.961
on Windows NT,Now we are planning to upgrade operating system from win NT machine to windows 2000.
Please let me know what and all precausion do i need to take what and what all backup should be done apart from user database.
How do i take care of the users,logins,roles, DTS Job and Replication.
Please help me in this and also any links if u have on this topic
Waiting for reply
TIA
Adil
Hello All,
Soon I shall have to migrate 2 medium sized databases from SQL server 7 to 2005.
Any words of wisdom, “how to�, “do and don’t� would be greatly appreciated.
Here are the details:
SQL 7.0 SP4, on Win2000
SQL 2005SP1,on Win2003
Sql 2005 needs to be installed on a new server (Win 2003), which will also be used as a web server (IIS6). I was thinking to go with a clean install for sql 2005 first, and after that to migrate the databases.
Thank you in advance,
Emily
Hi all,
I have script written in SQL Server 2000.
When i tried to migrate to SQL Server 2005, it is working fine in the SQL Server 2005 installed in Windows 2003 Server. System tables(syscolumns, sysobjects etc)are created in "Database->Tables->SystemTable".
But when i tried to run in SQL Server 2005 installed in Windows XP, I'm facing the following problems.
1."The object 'CK_SavedSear_LastL_4B422AD5' is dependent on column'LastLoadedTime'
"ALTER TABLE DROP COLUMN LastLoadedTime failed because one or more objects access this column"
2.Moreover none of the tables had been created in "Database->Tables->SystemTable".
In both XP and 2003 Server, While installing, i have choosen same options.
Please anybody could help me to resolve this issue.
Thanks and Regards,
Nilavu
I'm migrating 15 user databases from one server to another. The current production server is Windows 2000 Server SP4 and SQL Server 2000 Standard Edition SP4. The new production server is Windows 2003 Server and SQL Server 2000 Enterprise Edition SP4. The new server does not have the same directory structure as the current server.
I plan to detach the database, move the .mdb and .ldb files to the new server and then attach the database again. I'm not sure what to do with the master and msdb databases. Do I need to bring over the master? I know I need the msdb for all the job information. Since these are system databases, do I need to follow a different process? I want to get all these databases over to the new server so I can begin some parallel testing prior to the final cutover.
For anyone that has gone through this process, is there anything that I need to pay special attention to? Any issues moving from Windows 2000 to Windows 2003 or SQL Server 2000 Standard to SQL Server Enterprise Edition?
Any assistance would be GREAT!!!
Hi all,
I have script written in SQL Server 2000.
When i tried to migrate to SQL Server 2005, it is working fine in the SQL Server 2005 installed in Windows 2003 Server. System tables(syscolumns, sysobjects etc)are created in "Database->Tables->SystemTable".
But when i tried to run in SQL Server 2005 installed in Windows XP, I'm facing the following problems.
1."The object 'CK_SavedSear_LastL_4B422AD5' is dependent on column'LastLoadedTime'
"ALTER TABLE DROP COLUMN LastLoadedTime failed because one or more objects access this column"
2.Moreover none of the tables had been created in "Database->Tables->SystemTable".
In both XP and 2003 Server, While installing, i have choosen same options.
Please anybody could help me to resolve this issue.
Thanks and Regards,
Nilavu
We are in the process of Migrating our SQL Server 7.0 databases to SQL Server 2000.
We have purchased two new servers for this process.
What is the best method for the Database Migration ?
- Copy Database Wizard ?
- DTS ?
I would like to migrate an access data to SQL Server. However, the Access database has an OLE data type field that stores word documents. Is there a way to handle this in SQL Server?
View 2 Replies View RelatedHi! I have an SQL server installation with a 6.5GB database online at a particular location. I need to move the Database to another location which is a 1000 Miles away.
I have an additional physical Server at the other location which I can use to Sync the database from the original location. Can anyone please guide me to the best strategy to sync the database from the original location to the new location with minimum downtime??
Any help would be greatly appreciated.
Thanks and Regards
Anish
I want to migrate from COBOL400/AS400 to MS SQL Server, can any body tell me the way I can perform Data migration/Transfer without any loss of data
View 2 Replies View RelatedHi all
I hope you can help me in some way.
Basically for the last 5 months i've developed a SQL 2k db for my company.
All of a sudden they quite fancy using Linux on the server, therefore i would be required to use PostgreSQL instead of SQL Server 2k.
I could do with some advice about how PostgreSQL and SQL Server compare? and how much work would be involved in migrating from one to the other.
Points i've found myself and may be an issue are:
I've used some of SQL Servers XML capabilites and as far as i can tell PostgreSQL doesn't support XML.
I've made extensive use of functions that return table variables etc... is there a similar idea i can use in PostgreSQL?
Extensive use of Stored Procedures and T-SQL etc...
DTS packages. Any equivalent or similar idea?
(ps i've also posted this in the PostgreSQL but since no one replyed i was hoping the sql server boffs could help).
Hi All,
Would like to know if there is any impact on application if migrated to sql server 2005?
As of now,
We have a database setup for SQL server 2000.
Thanks
Hi All,
We are planning to move from sql server 2000 and sql server 2005.
Just want to know how well is the backward compatibility and
things that we should take care of before migration.
Thanks in advance..!!!
Hi all
Does SQL Server has any equivalent of Oracle (PL/SQL) PRAGMA AUTONOMOUS_TRANSACTION ????
I am in a process of migrating Oracle database to SQL Server. I am facing trouble in the above mentioned thing, as I cannot compromise by not putting it.
Thanks in advance
Christian
Migration from MsAccess to Sql-Server.
Problem:
Ing_ID = nz(me.Ing_AutoNr,0)
me.openform "MyForm",,,"ID = " & Ing_ID
This work in MsAccess with new records but this doesn't work in Sql-Server. How can I solve this?
Our clint need to migrate from already runner database on SQL SERVER 2005 to Oracle 9i, WHAT will be basic and important issue we will face in this term and HOW we will handle with our functions and procedures created in SQL SERVER 2005 database... ???
regards
Hello,We maintain a VB6 front-end application using an Access 2000 database. Allcode and forms are in VB6. The program also uses several queries/reportsdefined in Access. For corporate reasons we must move to SQL Server rightaway. Based on our configuration, we're hoping someone can give us a feelfor what's involved here. Since we're only using Access for the data we'renot sure what an "upsizing" would entail. Can someone point us in the rightdirection, tell us the main gotchas, etc. Thanks a lot.Teri Welch
View 7 Replies View RelatedHi,We need help on following things,1. Inputs on creating comments on the columns & Tables of a SQLDatabase & generating the sql script of that.2. Is it possible to call a .exe file in SQL server like followingcode in ORACLEcreate procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )language SQL;externalname "CERT_VERIFY"location 'HOST_IMG:TEST_CALCS.EXE'with ALL logical_name translationlanguage CGENERAL parameter style3. We are using Rules for restricting data(now), We need inputswhether to use Check constraints or Rules.Thanks & Regards,Chandra Mohan
View 1 Replies View RelatedDear Friends,I am about to migrate a Visual FoX Pro 7.0 & 5.0 databases to MS SQLServer 2000. I would like toshare the experience with you, if you have undergone such a task. Isthere any utility for achievingthis.Any white paper or link for guidance on the same.I wannted to migrate the whole database to MS SQL Server 2000. Iwanted to migrate the tablestructures and procedures to MS SQL Server 2000.Please shed some light please.RegardsKumar
View 1 Replies View RelatedHow can I change the MySQL code to SQL Server's like below?
I could not find out to change appropriate UNSIGNED keyword and ENUM
keyword in SQL Server.
CREATE TABLE myTable (
id INT UNSIGNED NOT NULL,
name CHAR(20),
role ENUM('ADMIN','USER','GUEST','UNKNOWN') DEFAULT 'ADMIN'
PRIMARY KEY(id)
);