SQL Server Mailbag: Migrating down to Standard Edition

In this edition of the SQL Server mailbag, expert Denny Cherry tackles reader questions dealing with downgrading SQL Server editions, troubleshooting data loss and more.

In this edition of the SQL Server Mailbag, database expert Denny Cherry tackles reader questions dealing with concerns over downgrading from SQL Server Enterprise to Standard edition, installing SSIS on a second server and more.

Have a question of your own? Send it along to editor@SearchSQLServer.com.


If I have been running SQL Server Enterprise Edition with my databases, can I downgrade to the Standard version and use the same databases with no alterations? I don't believe that SQL Server changes the format of the database so I should be able to migrate my database to a new server, but use the standard version instead to save money. -- Matt

Yes, you can mode the databases between editions without issue. Upgrading from Enterprise Edition to Standard edition can be a bit tricky as this isn't a supported upgrade path, so you'll need to either move to another server (or another instance on the same server), or uninstall the current Enterprise Edition, then install Standard Edition.

How can I copy an Oracle blob field to a SQL Server table using a remote server login?-- John

You should be able to simply do a SELECT against the Oracle table and insert the value into the SQL Server table using the three-part name of the Oracle table.

INSERT INTO SQLTable
(Column1, Column2, …)
SELECT Col1, Col2, …
FROM OraHost..Schema.Object
WHERE …
I am new to SQL Server administration. We have SQL Server Integration Services (SSIS) packages running on Server1. We want to install the same package on Server2. What files should be copied to the target server and what steps I should follow? -- Nag

If you don't have any configuration files that need to be moved, the only thing that you'll need to do is deploy the SSIS package to the new server. If you are storing your packages on the file system, simply copy the dtsx file to the new server. If you are storing the packages in the SQL Server or the Integration Services Data Store then you'll need to grab the BIDS Project and deploy the package to the new server.

If you can't find the package you can export the package from SSIS on the old server, by connecting to the Integration Services instance on the old server, navigating to where the package is located within the tree, and exporting the package by right clicking on it. Then simply import the package to the SSIS instance on the new server.

We are facing a strange problem where all the data from all of our tables in SQL Server Database is getting deleted every alternate day. The data is around 30 GB. What could be the cause of this? -- Pawan

It could be a couple of things, none of which are all that good.

  1. Someone could have setup a job to delete the data.
  2. You've got database corruption on a massive scale, and are running a database maintenance task with the automatically fix option selected.
  3. Someone has broken into your systems and is deleting your data.

I'm thinking of changing the recovery model on the database from full to bulk-logged to run DBCC CHECKDB and rebuild the indexes. Is this the right thing to do? -- Rick

No, you should pretty much never change the recovery model of the database once you have it set to the model that you want to use. Changing it to run DBCC CHECKDB and to rebuild the indexes shouldn't really do anything. DBCC CHECKDB doesn't write anything to the log unless you are running it in a repair mode. Rebuilding indexes are fully logged operations, so having it in bulk-logged mode shouldn't affect anything.

You need the log that records the movement of each block of data in the database while the index rebuild is going on so that if you restore the database and logs the index rebuild is replayed correctly.

Have a burning SQL Server question of your own? Let us know at editor@SearchSQLServer.com, or pose it to our IT Knowledge Exchange forum for fast responses from your peers.

ABOUT THE AUTHOR:   
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.
Check out his blog: SQL Server with Mr. Denny
This was first published in August 2009

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close