How to downgrade SQL Server 2008 R2 from Enterprise Edition to Developer Edition?

We had few Non-Production servers which were running on Enterprise editions of SQL Server 2008/SQL Server 2008 R2. For obvious reasons we had to downgrade these editions to developer editions of respective versions. Unlike adding new features to existing instance or upgrading existing instance from 2008 to R2 or upgrading from Standard to Enterprise edition there is no direct way(atleast I have not found) to perform a downgrade. Yes that’s true there is no direct way of doing it.

Though I have learnt 2 ways of doing edition downgrade: one is the mostly followed and lengthy processes and second one is less-time and less-known method.

First Method: You can try this for your first time:

This is a complete removal of existing instance and re-installing the instance of required edition. Most of the blogs\articles\forums suggest\prefer this.

How to do it?

First of all, as a first responsibility of every DBA what you do? Yes, take backup of all databases(User DB’s and System DB’s).

1.    Make copy of current mdf\ldf files of all system databases to other location.

2.    Make note of current version of SQL server instance like Service Packs, Hot Fixes, CU# installed.

3.    Script out all the server level permissions of logins.

4.    Script out all database level permissions of users.

5.    Make note of all SQL users credentials like username and passwords.

6.    Make note of Network configuration details like TCP\IP, VIA so on and port number of the SQL instance. You can get it from SQL Configuration manager.

7.    Account details on which SQL Services are running.

8.    In addition you can make a note of mdf\ldf file locations of all databases(system and user). This is required if your databases are spread across different drives.

9.    Also make note of enterprise features that are being used. (Anyway you can’t use them anymore J)

Now go to control panel and un-install the instance let’s say I want to downgrade my ‘Tester’ instance from Enterprise to Developer. So un-install Tester instance completely from the machine using Add\Remove programs of Control panel. Once un-installation completes reboot the server, once server is back verify and make sure instance got removed successfully.

Now place the CD of Developer edition and start installing. Now give the same instance name and if you have DB’s spread across different drives you can specify those file locations while installation, use the details you took in Point 8. Also configure the account for the services during installation itself with the details you have in Point 7. Also add yourself as admin while installation. Then proceed clicking Next and also mainly make sure you are installing only the features which were being used\installed earlier. Once installation completes, install all the required Service packs, CU# and hot fixes required to bring the edition to same version number as earlier(we took in Point 2) .

After bringing the newly installed Tester instance to same version number as point 2, stop the SQL services and replace the mdf\ldf files of master and msdb databases of the new instance with the mdf\ldf files of old instance(we took copy in Point 1). Once after replacing the master and msdb mdf\ldf files, start the sql services.

Now verify the instance and you should be able to see all the jobs and logins back. Now you have to restore all the user databases to the new instance in fact you can attach them back if you have taken mdf\ldf file copies of user databases already. Configure all the logins\users and their permissions(Step 3 and 4). Then configure configuration manager same as previous instance(Step 6 and 7).

Now re-start the SQL services and verify if everything is working as expected. Also confirm now you have same instance running on Developer edition with same version number as earlier.

Note:

                                                                                                                                                                                          

·         You need to replace mdf\ldf files of ‘model’ DB as well if your model DB is customized.

·         After re-installation VERSION number should be same as previous.

Second Method:

Why I mentioned this method as less-known because I have not found any blog mentioning they tried this method. Only place I found is in the comments of this link.

The method says making the registry changes will downgrade the edition without need to re-installation. I have to downgrade anyways in non-production environment so thought of trying this and IT WORKED!

Even though it worked for me, every time I try this I make sure I have the backups of all DB’s and above mentioned backups from Step 1 to 9.

Once you have backups, go to registry.

1.    Type regedit.msc

2.    Got to SQL installation section in registry by default it will be here:
HKLM(HKEY_LOCAL_MACHINE) -> SOFTWARE -> Microsoft -> Microsoft SQL Server ->

3.    Here you have to select the instance you want to downgrade if you have multiple instances.

Expand required instance folder.

Eg: In my case it is ‘Tester’ instance so

MSSQL10_50.Tester -> Setup

4.    Click on ‘Setup’

5.    Here You need to change one by one all below entries.

AGTGroup

DigitalProductID

Edition

EditionType

ProductCode

ProductID

SQLGroup

FDHostGroup

Checksum

Take these values from any already installed Developer edition instance and replace here. Make sure from where you are taking these values have the same features installed and same version and all.

6.    Now click on ‘SQL_Engine_Core_Inst’ and change the entries here as well.

·         ProductCode

7.    Now re-start the SQL Services and verify everything is in place and working as normal.

That’s it you are done.

Note:

·         Second method requires registry changes so please take backup of registry before making any changes.

·         Most important do not try this in your sensitive servers unless you tried it once in your less critical environments and it worked.

·         Make sure you have backup of everything.

     DO NOT MAKE ANY CHANGES TO THE REGISTRY WITHOUT BEING SURE WHAT YOU ARE DOING!!

Thanks!!

How to Un-install a clustered instance from SQL Server 2008 R2 cluster?

We have a 2 node cluster(Active/Passive) and has 2 SQL Server 2008 R2 instances running. We need to Un-install one of the running instance from this cluster.

Normal SQL Server Un-installation we can do directly from Add/Remove programs of Windows server but to perform the same in cluster environment is bit different.

For removing clustered instance first we need to copy the set up files of SQL Server to local drive of Passive node and run ‘Setup.exe’.

Select ‘Maintenance’ option in the below window and click on ‘Remove node from a SQL Server failover cluster’

 this will start ‘Setup Support Rules’ window:

This will verify all the required rules and will give detailed report:

If any failures noticed, need to fix them before proceeding further. 

Next window will show available cluster nodes and SQL instances currently running:

Make sure correct instance is selected in ‘SQL Server instance name’ section. We need to select the instance (in the highlighted section below) name which we want to remove from cluster 

after clicking on next it will shows us the currently installed features of selected instance:

To start removal of instance click on ‘Remove’ button. Removal process will start:

Once instance removal completes, re-start the passive node. Now login to the active node and follow the above same steps. After removing the instance from Active node we need to reboot the active node as well to complete the Un-installation.

Note: Before rebooting current Active node make sure to failover the other running instance of active node to the passive.

Thanks!!

How to take PARTIAL or Filegroup backup? How to perform restore of PARTIAL backup file?

In SQL Server we have an option of taking backup of only certain part of the database, something like if we have a database which has multiple file groups and we want to take backup of only a single FileGroup that can be achieved. It is termed as Filegroup backup or Partial backup.

This kind of backups are needed in situations like if we have a large table in separate file group of production database and that table is not required to be available in test environment, so to save space in test environment and to reduce backup/restore times we can only take the backup of required file group and restore it to test environment.

FILEGROUP BACKUP:

BACKUP DATABASE AdventureWorks2012

FILEGROUP = 'PRIMARY'

TO DISK = 'C:\SQL2K8\Backups\AdventureWorks2012.bak'

WITH INIT,COMPRESSION

The backup command is almost same as normal backup command it will have section named ‘FILEGROUP’, in this section we need to mention the file group name which we want to backup. With this command SQL engine will back up only the file group name we specify, in above case only PRIMARY file group will be backed up.

PARTIAL RESTORE:

RESTORE DATABASE AdventureWorks2012_Test

FROM DISK = 'C:\SQL2K8\Backups\AdventureWorks2012.bak'

WITH PARTIAL,

MOVE 'AdventureWorks2012_Data' TO 'C:\SQL2K8\Backups\AdventureWorks2012_Data.mdf',

MOVE 'AdventureWorks2012_Log' TO 'C:\SQL2K8\Backups\AdventureWorks2012_log.ldf',

RECOVERY,REPLACE,STATS=10

While restoring the PARTIAL backups the only difference from normal restore command is we need to specify the option ‘PARTIAL’ in the restore command and rest of the command is same.

Points To Remember:

·        Database should be in FULL recovery in order to perform PARTIAL backup.

·        In partial restore one thing to note is, if the database has 2 file groups like PRIMARY and SECONDARY and we restored a PARTIAL backup which has backup of only PRIMARY file group, after restore through GUI we can still see the SECONDARY file group which was non-existent in backup file we used. But if we try to query the objects of that FG it throws error. Explained in below captures. So, even though we can see the FG through GUI it doesn't exist physically.

Performed a PARTIAL restore as shown in above capture using backup file which has only PRIMARY file group backup.

After restore when I verified properties of database it still shows SECONDARY FG.

Now I try to query the ‘SalesOrderDetail’ table which resides in SECONDARY FG, as the FG is not part of the partial backup we used the query throws below error:

Thanks!!

How to move a table from one file group to another file group in SQL server?

This is one of the questions haunting me from long where-in I was not aware of how to do that and I haven’t spent time as well to find the solution, luckily i tried this time. First thing is(as far as I tried) we cannot move a table from one file to another file in a database but what we can achieve is we can move a table between file groups. Something like from PRIMARY file group to SECONDARY and so on.

How?

To move a table from Filegroup1 to Filegroup2 we have to move the CLUSTERED INDEX of the table. After that we need to move the other NON-CLUSTERED indexes as well to the new file group. This will move the entire table from one FG to another FG.

Here I will move a table named ‘SalesOrderDetail’ of ‘AdventureWorks2012’ sample database from PRIMARY file group to SECONDARY file group. The below commands I have tried on both 2008 and 2012, they are valid for both versions.

Step: 1

To get Index Details:

To get the details of indexes along with file group information I am using below query. It will give table name, type of index, index name and in which file group it is currently in.

SELECT      'Table Name' = OBJECT_NAME(INX.object_id),

            'Index Name' = INX.name,

            'Type Of Index' = INX.type_desc,

            'FileGroup Name' = FGS.name

FROM        sys.indexes INX

INNER JOIN  sys.filegroups FGS ON INX.data_space_id = FGS.data_space_id

WHERE       INX.object_id = OBJECT_ID('[Sales].[SalesOrderDetail]')

 

 

As we can see in above capture the table has 3 indexes and all are in PRIMARY FG.

Creation of secondary file group and secondary file:

To create SECONDARY file group we can right click on the database and select ‘Properties’, then click on ‘Filegroups’, then click on ‘Add’ button and under the ‘Rows’ enter the name of the new file group, here I am giving the name as ‘SECONDARY’ itself and click ‘OK’. This will create new file group.

 

Adding file to new FG:

To create secondary data file and add it to new FG we can use below command:

Use AdventureWorks2012

ALTER DATABASE AdventureWorks2012

ADD FILE

( NAME = [AdventureWorksDW2008R2_Data2],

FILENAME = 'C:\SQLServer2012\AdventureWorks2012_Data2.mdf')

TO FILEGROUP [SECONDARY]

While creation itself we can specify the settings of the new data file like how it should grow, what should be its initial size and all. If we don’t specify those values it will take all default values. Through the above command a new file will be created with the name ‘AdventureWorks2012_Data2’ and it will reside in the location specified.

Moving of CLUSTERED INDEX:

After creating secondary FG and adding file to it , to move table ‘SalesOrderDetail’ to new FG we first need to move CLUSTERED INDEX of that table with below command:

CREATE UNIQUE CLUSTERED INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

 ON [Sales].[SalesOrderDetail]

(

   [SalesOrderID] ASC,

      [SalesOrderDetailID] ASC

) WITH (DROP_EXISTING = ON,PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

 ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

ON [SECONDARY]

In the above command the 2 things important are DROP_EXISTING = ON this will create new one by dropping the existing one and ON [SECONDARY] this will create it in SECONDARY file group. With above command we are trying to move the ‘[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]’ to new FG. The index names can be obtained from same query of Step:1.

Note: If the table is very huge it will be faster if we truncate (Depends on wether you are allowed or not to TRUNCATE) the table before moving.

Once the command completes successfully if we run the query of step 1 it will give below results:

As we can see the CLUSTERED INDEX  has been moved to new FG. Now in the same way we need to move the other NON-CLUSTERED indexes as well to new FG.

CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail]

(

      [rowguid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,

 IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

 ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY]

GO

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]

(

      [ProductID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,

IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY]

GO

After moving NON-CLUSTERED indexes as well we can verify with same query:

 

In capture we can see now all the indexes have been moved to new FG.

The same has to be done for all the indexes of the table. For moving of NON-CLUSTERED indexes just script out the existing indexes and set the 2 options as DROP_EXISTING = ON and ‘ON [SECONDARY]’.

 

Thanks!!

How to mount new partition/volume as a folder in Windows?

If you open ‘Server manager’ after logging into Windows 2008 server and under ‘Storage’ section once we click on ‘Disk Management’ on right side we can see all the drives of the machine and below to that we can see all the disks available. Once a new partition is added or any unallocated partition already exists in machine we can find it if we scroll down. If unallocated partition exists it shows like below capture:

In my case I have 2 disks Disk 15 and 16 which have 100GB and 400GB of space which are currently un-allotted to any of the folder or drive. My requirement is to allot this 400GB of space to the AdventureWorks folder which is located at C:\SQL2K8\AdventureWorks).

To achieve this right click on the 400GB new volume in ‘server manager’ and select ‘New Simple Volume’ as shown in below capture:

Once you click on this it will open simple volume wizard, in the window check on the button ‘Mount in the follow empty NTFS folder’ and click on ‘Browse’ button, this open the drive and folders available in machine and in it browse to the folder for which you want to allot space, in my case I need to open C drive and SQL2K8 folder and select ‘AdventureWorks’ folder after that click on ‘OK’.

In ‘Format Partition’ window check the ‘Format this volume with …’ option and select ‘File System’ as ‘NTFS’ and ‘Allocation Unit Size’ as ‘64k’ and ‘Volume Label’ can be left unattended and check the ‘Perform a quick format’ option.

Note: The options to be selected in this windows may change based on your server settings and standards.

Then proceed clicking next and it will be done. To verify whether it has been allocated properly or not go to folder right click click ‘Properties’ and click on ‘Properties’ and then you can see the folder has free space of 400GB (as per my case here).

Thanks!!

#mountingdrive

Moving of data/log files to another drive of SQL Server 2000 server.

This is quite old topic but we never know which task you have to face as a DBA as I did recently J. In case of latest versions we can use ‘ALTER DATABASE’ command to move the database as discussed in another article. But this bit different and easy in SQL Server 2000 here we need to follow below steps to move a database files either data file or log file from one drive to another drive.

1.    Detach the database

Command: sp_detach_db ‘database name’

2.    Copy the required file(Data or Log file) to new location manually.

3.    Attached the database with below command

Sp_attach_db ‘database name’, ‘new location of data file’, ‘new location of log file’

Eg: Let’s say we want to move the ndf file and log file of Test database from a different location to the new location ‘C:\SQL2K\’, so the command will be like below:

 sp_attach_db ‘TestDB’, ‘C:\SQL2K\Test.mdf’,’C:\SQL2K\Test.ndf’,’C:\SQL2K\Test.ldf’

Note: After attaching the database delete the file from old locations.

Incase if the database of SQL Server 2000 version is involved in replication as in my case we first have to disable the replication obviously. To disable replication SQL 2K version please follow below setps:

sp_removedbreplication 'database name'

Go

exec sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

Go

Update sysobjects set replinfo = 0 where replinfo != 0

UPDATE syscolumns SET colstat = colstat & ~4096 WHERE colstat & 4096 <>0

Go

exec sp_configure 'allow updates',0

RECONFIGURE WITH OVERRIDE

Incase if the sp_configure command throwing error please execute it in new query window. After executing above queries DB will be out of replication but to remove completely you have to disable it from the server properties and select ‘Disable All’ options, based on the size of the database this disabling will take time and as it is old version sometimes the session will hang but once entire replication set up is disabled session will be back to normal.

Thanks!!