migration from standard edition to enterprise edition

Question

Post reply

migration from standard edition to enterprise edition

Avi Semah

Mr or Mrs. 500

Points: 550
More actions
July 19, 2004 at 12:28 pm

#85891

Hi,
I have written a Point of Sale vb application that does all the processing on the client side and that fetches, deletes, inserts, and edits records in acentral sql data base through the vb ado object and stored procedures that are triggered from the vb applicatio on the client side. The sql server that I use is enterprise edition. Noe comes the real moment and, trying to save money on licences, I need to migrate to standard edition, let alone I don't need the capacity allowed by enterprise edition. My question is whether I need to do alot of manipulation in the vb code as well as in the transferring the database from enterprise to standard edition. I am somewhta concerened since I have been through this migration issue in the past when I migrated from MS Access to MS SQL Server 2000 Enterprise Edition -- anyone who has been through this knows thal all the sql statements and the tables and the queries needed to be migrated to the sql server instance and that was a demnading process, not tro mention lengthy. So I ask the experts and the seasoned ones, would it be easy to migrate from Enterprise to Standard edition? My intuition tells me that it should not be hard, that it could be done very fast, and that nothing would need to be rewritten in on the VB application provided that all worked fine with the Enterprise Edition. But there is that chance that I'm wrong and that is why I would be greatly appreciative if anyone could shed some light on the issue in question.

thank you all in advance

Avi

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

As long as your application does not use the features of SQL Server Enterprise, you should be fine.

See BOL Features Supported by the Editions of SQL Server 2000.

Database Engine Features Supported by the Editions of SQL Server 2000

This table shows the database engine features and the editions of SQL Server 2000 that support them.

Database Engine Feature	Enterprise Edition	Standard Edition	Personal Edition	Developer Edition	Desktop Engine (MSDE 2000)	SQL Server CE	Enterprise Evaluation Edition
Multiple Instance Support	Supported	Supported	Supported	Supported	Supported	N/A	Supported
Failover Clustering (up to four nodes)	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Failover Support in SQL Server Enterprise Manager	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Log Shipping	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Parallel DBCC	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Parallel CREATE INDEX	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Enhanced Read-ahead and Scan	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Indexed Views	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Federated Database Server	Supported	N/A	N/A	Supported	N/A	N/A	Supported
System Area Network (SAN) Support	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Graphical DBA and Developer Utilities, Wizards	Supported	Supported	Supported	Supported	N/A	N/A	Supported
Graphical Utilities Support for Language Settings	Supported	N/A	N/A	N/A	N/A	N/A	N/A
Full-Text Search	Supported	Supported	Supported (except on Windows 98)	Supported	N/A	N/A	Supported
SQL Mail	Supported	Supported	Supported	Supported	N/A	N/A	Supported

Replication Features Supported by the Editions of SQL Server 2000

This table shows the replication features and the editions of SQL Server 2000 that support them.

Replication Publisher Feature	Enterprise Edition	Standard Edition	Personal Edition	Developer Edition	Desktop Engine (MSDE 2000)	SQL Server CE	Enterprise Evaluation Edition
Snapshot Replication	Supported	Supported	Supported	Supported	Supported	N/A	Supported
Transactional Replication	Supported	Supported	Subscriber only	Supported	Subscriber only	N/A	Supported
Merge Replication	Supported	Supported	Supported	Supported	Supported	Anonymous Subscriber only	Supported
Immediate Updating Subscriptions	Supported	Supported	Supported	Supported	Supported	N/A	Supported
Queued Updating Subscribers	Supported	Supported	Supported	Supported	Supported	N/A	Supported

Analysis Services Features Supported by the Editions of SQL Server 2000

This table shows the Analysis Services features and the editions of SQL Server 2000 that support them.

Analysis Services Feature	Enterprise Edition	Standard Edition	Personal Edition	Developer Edition	Desktop Engine (MSDE 2000)	SQL Server CE	Enterprise Evaluation Edition
Analysis Services	Supported	Supported	Supported	Supported	N/A	N/A	Supported
User-defined OLAP Partitions	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Partition Wizard	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Linked OLAP Cubes	Supported	N/A	N/A	Supported	N/A	N/A	Supported
ROLAP Dimension Support	Supported	N/A	N/A	Supported	N/A	N/A	Supported
HTTP Internet Support	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Custom Rollups	Supported	Supported	Supported	Supported	N/A	N/A	Supported
Calculated Cells	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Writeback to Dimensions	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Very Large Dimension Support	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Actions	Supported	Supported	Supported	Supported	N/A	N/A	Supported
Real-time OLAP	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Distributed Partitioned Cubes	Supported	N/A	N/A	Supported	N/A	N/A	Supported
Data Mining	Supported	Supported	Supported	Supported	N/A	N/A	Supported

Data Transformation and Decision Support Query Features Supported by the Editions of SQL Server 2000

This table shows the data transformation and decision support query features and the editions of SQL Server 2000 that support them.

Analysis Services Feature

Enterprise Edition

Standard Edition

Personal Edition

Developer Edition

Desktop Engine (MSDE 2000)

SQL Server CE

Enterprise Evaluation Edition

Data Transformation Services

Supported

Deployment only

N/A

Supported

Integrated Data Mining

Supported

N/A

Supported

English Query

Supported

N/A

Supported

Avi Semah Mr or Mrs. 500 Points: 550 More actions · Answer 2

thanks for the prompt reply and the BOL tables you have attached -- it really puts me in a better position. From looking at those tables, I think that the only thing I will need to do is manipulate the indexed views that I have. My question is whether the migration will necessitate rewriting the indexed views, or would the Standard Edition ignore the indexed views.

Another mind boggling issue is the transfer itself -- suppose I have a database called mydatbase that contains tables, views, stored procedures, etc. Will I be able to transfer the entire databes in one shot or will I need to deal with the objects seperately. That is, is is possible just to copy the database from the enterprise edition to the stored procedure?

Once again, I appreciate the help.

Avi

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

I perfer to copy entire database over by using sp_detach_db/sp_attach_db or backup/restore commands.

Avi Semah Mr or Mrs. 500 Points: 550 More actions · Answer 4

thank you again for the prompt reply. From your last reply I understand that it is possible to use the restore functionality of the sql server with respect to a database created in enterprise edition such that the restore will be prformed in standard edition. Thoug I onlt have the enterprise edition right now, I intennd to purchse the licenses per Standard edition, and once I do so, I'll use the restore option.

thanks for the good help

Avi

vadba SSChampion Points: 11133 More actions · Answer 5

You mentioned that you use indexed views. To get that performance in standard edition, you'll have to modify your queries a bit.

To use indexed views in SQL Server 2000 Standard Edition, you must specify the NOEXPAND view hint in the FROM clause of the SELECT statement.

A simple example looks like this:

SELECT * FROM myView WITH (NOEXPAND)

According to BOL, the following SET options must be set as follows:

SET NUMERIC_ROUNDABORT OFF

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_PADDING ON

SET ARITHABORT ON

SET QUOTED_IDENTIFIERS ON

Also, the full syntax from BOL is:

< view_hint > ::= { NOEXPAND [ , INDEX ( index_val [ ,...n ] ) ] }

Arguments

NOEXPAND

Specifies that the indexed view is not expanded when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index.

INDEX ( index_val [ ,...n ] )

Specifies the name or ID of the indexes to be used by SQL Server when it processes the statement. Only one index hint per view can be specified.

INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek.

If multiple indexes are used in the single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the indexed view. The ordering of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing and SQL Server applies as many conditions as possible on each index accessed. If the collection of hinted indexes does not contain all columns referenced in the query, a fetch is performed after retrieving all the indexed columns.

Avi Semah Mr or Mrs. 500 Points: 550 More actions · Answer 6

hello mkeast,

Thank you for your input -- it's always good to know as many details as possible before embarking on a new road of migrating from one system to another.

From your words I understand that it is still possible to make the Standard Edition take account of the

Avi Semah Mr or Mrs. 500 Points: 550 More actions · Answer 7

hi again,

Something happened with respect to my last reply--- not all the contents of that reply went through. So to continue from where I was cut off....

Thank you for your input -- it's always good to know as many details as possible before embarking on a new road of migrating from one system to another.

From your words I understand that it is still possible to employ the indexed view with standard Edition provided that I ass the NOEXPAND hint, along with abiding by some rules regarding the settings of some variables. Please correct me if I'm wrong.

If that is the case, I wanted to know what would happen if I just transfer the indexed views from Enterprise to Edition without attnding to the modification I need to embed with respect to Indexed Views. It might seem a bit out of the mission, but I have to admit that there are not too many indexed views in my database, and I'm more concentrated on the migration task. So assuming it's possible to restore the database created with Enterprise as a new database in Standard Edition, would this process go through without manipulating the indexed views with the NOEXPAND hint?

once again, thanks for your help

Avi

vadba SSChampion Points: 11133 More actions · Answer 8

Indexed views should not be a problem. The difference between Standard edition and Enterprise edition is that the query optimizer considers indexed views automatically in the Enterprise edition but not in the Standard edition.

Avi Semah Mr or Mrs. 500 Points: 550 More actions · Answer 9

Once again, mkeast, many thanks for the important input that I will certainly take account of once going about migrating from the expensive Enterprise Edition to the less expensive Standfard Edition

Avi

Alamir SSCertifiable Points: 6085 More actions · Answer 10

I want to ask you a question

do you intend to migrate from Enterprise to Standard in the same Cmputer ?

by the way make a backup of your working Database and restore it in another SQL Server Standard (Temp Server).

or make a Detach , Copy, and Attach.

I hope this help u.

Alamir Mohamed
Alamir_mohamed@yahoo.com

Avi Semah Mr or Mrs. 500 Points: 550 More actions · Answer 11

hi Alamir,

For financial reasons, I would like to use the Standard rather than the Enterprise. Currently I have an instance of SQL enterprise edition, and all the databases I need reside in that instance. I believe the migration will take place in the same computer.

Your tip is very useful and technical, as I knew that the migration is possible but I did not exactly how to do it, so I now I know that your way is the way to do it.

Thank you very much for your support

Avi

Alamir SSCertifiable Points: 6085 More actions · Answer 12

by the way, the best way to remove SQL Server is to :

1- Put your Setup CD of your SQL Server Enterprise Ed.

2- make a steps as you install a new DB server .. but in last Screen , it will ask you to install a new instance or to Remove the SQLServer ... Select to remove it

Now SQL Server is uninstalled ...

3- Restart your server and Install SQL Server Standard Edition ..

4- attach your DB (I think you will find it in the same place you keep it .. but it is recommended to move it to any place rather than [program files] Folder)

I hope this help u much

Alamir Mohamed
Alamir_mohamed@yahoo.com