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 SQLTableI 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
(Column1, Column2, …)
SELECT Col1, Col2, …
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.
- Someone could have setup a job to delete the data.
- You've got database corruption on a massive scale, and are running a database maintenance task with the automatically fix option selected.
- 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.
|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