Migration from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium

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 Ashok,

Running Under 64 bit OS =No means that Operating System is not 64 bit version, you are running 64 bit Version of SQL Server on Operating System which is 32 bit.

Regarding SQL Server Memory it is Dynamically Allocated and De Allocated unless you have specified using sp_configure or via SSMS, another thing you need to refer is have you or some one change the values for network packet size / Min memory / maximum memory value

have you set any option for max degree of parallelism Or max worker threads

If you do have more then 2 GB memory you need to use AWE OR PAE switch in order to use memory more then 2 GB , also refer Estimated and Actual Exucution Plan in both the server to get idea for taking too much time in new server.

Refer http://www.sql-server-performance.com/statistics_io_time.asp

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17732

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18192

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17107

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16988

HTH
Hemantgiri S. Goswami

Migration from PervasiveSQL 7 to SQL Server 2000(Recomendations)

*sorry for spelling mistakes - im forigne
If you have experience with this procedure please let me know of any difficulties/problems that occured because of the change over. What procedure did you go through to make them compatible and perform at a reasonable level? Do you know the appox. size increase of the database (GB/MB) from one version to the other?

Current platform:
Compaq Proliant
Netware 4.5
4.5 GB database (accounting/inventory)

New platform:
IBM eserver X235
Win 2000 SQL Server
avalible HDD space approx. 100gb

If you could make any suggestions it would be appreciated.bump

Migration from Oracle 7.34 to SQL 2000

I'm attempting to move a database which currently exists on an old Oracle
7.34 instance and tried to use Data Import via DTS to SQL 2000.
The Oracle database consists of views, indexes,packages,triggers as well as
tables structures. I tried DTS using both the ODBC and OLE for Oracle and
only tables and data came over correctly.
Views it appears were converted into tables. Indexes not at all.
I'm using the Oracle 9.2 client on my workstation.
Any ideas on how I might be able to convert this database over from Oracle?
thks
You'll have to move the code, views, etc manually
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:tea_c.4190$lv3.2047604@.news4.srv.hcvlny.cv.ne t...
> I'm attempting to move a database which currently exists on an old Oracle
> 7.34 instance and tried to use Data Import via DTS to SQL 2000.
> The Oracle database consists of views, indexes,packages,triggers as well
as
> tables structures. I tried DTS using both the ODBC and OLE for Oracle and
> only tables and data came over correctly.
> Views it appears were converted into tables. Indexes not at all.
> I'm using the Oracle 9.2 client on my workstation.
> Any ideas on how I might be able to convert this database over from
Oracle?
>
> thks
>

Migration from Oracle 7.34 to SQL 2000

I'm attempting to move a database which currently exists on an old Oracle
7.34 instance and tried to use Data Import via DTS to SQL 2000.
The Oracle database consists of views, indexes,packages,triggers as well as
tables structures. I tried DTS using both the ODBC and OLE for Oracle and
only tables and data came over correctly.
Views it appears were converted into tables. Indexes not at all.
I'm using the Oracle 9.2 client on my workstation.
Any ideas on how I might be able to convert this database over from Oracle?
thksYou'll have to move the code, views, etc manually
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:tea_c.4190$lv3.2047604@.news4.srv.hcvlny.cv.net...
> I'm attempting to move a database which currently exists on an old Oracle
> 7.34 instance and tried to use Data Import via DTS to SQL 2000.
> The Oracle database consists of views, indexes,packages,triggers as well
as
> tables structures. I tried DTS using both the ODBC and OLE for Oracle and
> only tables and data came over correctly.
> Views it appears were converted into tables. Indexes not at all.
> I'm using the Oracle 9.2 client on my workstation.
> Any ideas on how I might be able to convert this database over from
Oracle?
>
> thks
>

Migration from MySQL to SQL Server

How 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)
);

hi,

you can use "standard" signed integers in SQL Server syntax to host integer values in the range -2,147,483,648 to 2,147,483,647 as SQL Server does not provide unsigned integers... you can "scale up" to bigint if you required larger range in the integer domain..
http://msdn2.microsoft.com/en-us/library/ms187745.aspx

SQL Server does not provide an "Enum" feature, but you can define a CHECK CONSTRAINT for that...
usually you should consider a referential integrity based referenced table in the design if the "range" can grow as a direct CHECK requires modelling refinement and rewriting if you, say, want to add sometime later another "role" valid entry...

so... you can "translate" as

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE myTable ( Id int NOT NULL PRIMARY KEY, Name char(20) NULL, -- but I bet varchar(20) should be better -- as not all names will be 20 chars long Role varchar(7) NOT NULL DEFAULT 'ADMIN' CONSTRAINT chk_Valid$Entries$for$Roles -- use this check constraint if you require CASE SENSITIVE validation of the role -- CHECK ( Role IN ('ADMIN' COLLATE Latin1_General_CS_AS , 'USER' COLLATE Latin1_General_CS_AS , 'GUEST' COLLATE Latin1_General_CS_AS , 'UNKNOWN' COLLATE Latin1_General_CS_AS ) ) -- use this check constraint if you do not require CASE SENSITIVE validation of the role CHECK ( Role IN ('ADMIN', 'USER', 'GUEST', 'UNKNOWN') ) ); GO INSERT INTO myTable VALUES ( 1 , 'Andrea', DEFAULT ); -- default role GO INSERT INTO myTable VALUES ( 2 , 'Andrea2', 'USER' ); -- valid role GO INSERT INTO myTable VALUES ( 3 , 'Andrea3', 'other' ); -- NOT valid role GO INSERT INTO myTable VALUES ( 4 , 'Andrea3', 'User' ); -- NOT valid role IF case sensite -- collation check has been set GO SELECT * FROM myTable GO DROP TABLE myTable; --<-- Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "chk_Valid$Entries$for$Roles". The conflict occurred in database "tempdb", table "dbo.myTable", column 'Role'. The statement has been terminated. Id Name Role -- -- - 1 Andrea ADMIN 2 Andrea2 USER 4 Andrea3 User 

I provided 2 different check constraints... the active one is NOT case sensitive, that's to say users can enter both 'ADMIN' and 'admin' (or whatever conbination of cases), where the commented one performs a case sensitive check and the 4th insert will fail accordingly...

thanks to Lorenzo Benaglia, friend and SQL Server MVP fellow for helping in the "migration"...

regards

Migration from MS access TO MS SQL

We recently needed to switch from Access over to MS SQL because of the
limitations with access and its size maximums. We currently use Access as our reporting trigger for the entire companies reporting. I'm currently trying to discover functionality that I had in Access in MS SQL. I'm slowly but surely learning. Our database structure in Access was set up with Master Files and revenue files in seperate Access databases and then linked into functional reporting access databases. So we had many links from DB to DB. Additionally we had links directly into the AS400(JDE). My question is two fold:

How do you link from DB to DB in MS SQL, my assumption was using a VIEW.
Is this the best method or should you store all the tables in the same DB(probably not, I would say).

How do you link(create view) a file where the source is the as/400? And again is this a good method for getting at that data. I think my problem here is the PROVIDER_NAME

One more. Our Infrastructure group named the server Local is that going to inhibit anything in the future.

And also one more. I get an error creating views that look at other SQL DB's when I include criteria. When I don't they work..

Thanks
jmThat's a lot of questions. I can try to answer some of them:

FACT: You can reference tables and other objects in other databases by using the full three-part name syntax:

database.owner.object

You can use this syntax is views, stored procedures, etc...wherever you would noramally reference the table or view. If you omit the database and owner, SQL server assumes you are refering to an object in the current database owned by either the current login or the dbo. Fully referencing objects is not normally required, and that's probably why you are not familiar with it.

OPINION: Put all your tables in one database. Was there a reason you had them separated in Access? That probably affected system performance.

QUESTION: What error are you getting when you include criteria in your linked views? (There should be some rules for the forum forbidding people from mentioning they are receiving an error without stating what the error is! :rolleyes: )

I don't think naming your server Local will do any harm except make code extremely confusing to read, especially code on other servers that reference it! It does show a complete lack of imagination and creativity, and you can tell them I said so.

blindman|||As fare as I know, SQL Server does not support links to "alien" systems like AS/400. So, you have in my opinion two options:

* Maintain MS Access as integration platform, even if your data is moved to SQL Server
* Let your reporting tool connect to the different systems and join the results

For a multi-database application, and a Access-SQL Server converison tool look at my references beneath.|||It is possible basically you may need MS Host Integration Server so you can talk to AS/400 servers. Using SQL 2000 Link Server technology you use Ole DB/ODBC to talk to any other RDBMS as long as it supports Ole DB/ODBC (including AS/400). I have not tried this myself so I could be way of the mark, I have tried it with Sybase running on Unix.|||Yes, you should be able to connect to your Oracle database. Checks SQL Server Books Online for "Configuring Linked Servers".

Access makes a fine reporting tool and a flexible user-interface, but if your data is on SQL server you would get the best performance by pushing as much processing back onto the server as possible. Access is notoriously slow when processing queries on remote data, because it often must pull entire tables across a network in order to make its joins. It is sometimes able to formulate a plan that involves the server filtering the data first, but this cannot be relied upon. Your best bet is to let SQL server make all the links between tables and other servers and pass that data back to Access for display. Add to that the fact that MS Access' security schema is primitive and clunky compared to SQL server.

Check out the option of converting your Access database to an Access Data Project (.adp file extension). ADP applications use a more direct connection to SQL server, and all processing is performed on the server since the ADP file cannot store tables and views. An Access Data Project is my preference for rapid application development.

blindman|||Blindman is corected. Why need a "middleware" Access when your data is in SQL Server? not ony that SQL Server is more robust & secure than Access , the performance is much better when directly access data. thru SQL Server.

Migration from MS Access 2003

Hello,

I'm very new to MS SQL Server; I just downloaded Express 2005 yesterday. I have it up and running; it seems to be working fine.

Now onto business - I need to migrate data from existing MS Access 2003. How, exactly, do I do this? I've tried searching MSDN, TechNet, and here, and while I get results that seem to tell part of the story, I don't seem to get a true "How to" for this task.

Can someone please assist me with this?

Thank you.

Here you go:

http://support.microsoft.com/default.aspx/kb/237980

Buck Woody