Move Publisher database to another drive without affecting replication - SQL Server by serverku

A month ago, I wrote some articles related to replication configuration which are following,

Skip distributor agent error in sql server transactional replication - How to
Configuration Replication failure and retry alert-SQL Server

Hope you read and liked it. Now moving one another article and it is about to move the publisher database to another drive in the same server without breaking whole replication. So let me share some steps and continue with same as below,

1. Go to Replication Monitor from Replication tab under default instance the option with right click.

2. Make sure all undistributed command processed and should be 0 for all subscriber.

3. Go to agent tab (to disable)
         a. Go to snapshot agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.
         b. Go to Log reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.  
         c. Go to Queue reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.

4. Making database restricted and offline it.

 ALTER DATABASE PublisherDB SET restricted_user with rollback immediate;
 ALTER DATABASE PublisherDB SET OFFLINE;

5. Mapping database with new file location.

ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_Data , FILENAME = 'D:\data\pdb_data.mdf' );
ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_log , FILENAME = 'D:\data\pdb_log.ldf' );

6. Stop SQL Database service and copy database files to new mapped location.

7. Making database online and make available with multi user.

ALTER DATABASE PublisherDB SET ONLINE;
ALTER DATABASE PublisherDB SET multi_user;

8. Repeat step 3 with “Go to agent tab (to enable)” and finish this task.

Please note these scripts run against publisher server. These are just steps and hope you found it useful. Thanks for reading.

Adding/Modifying files and filegroup in Database - SQL Server by serverku

You all know about the database file structure as whenever we created databases without specifying a file location and file groups, they will be created in default path and default filegroup. Here I have tried to create a new database without defining any options, look the file structure on the default location and the default group.

CREATE DATABASE FILEGROUPDB
GO

SP_HELPDB FileGroupDB
GO

(Click on image to enlarge)

We can change the default filegroup with following script.

ALTER DATABASE AdventureWorks2008R2 
MODIFY FILEGROUP [PRIMARY] DEFAULT; 
GO

As a better perspective for the database Architecture, some frequently updated or used tables should be on different filegroup and the database secondary and log files also be one different filegroup. TempDB data and log files should have alone disk and filegroup as well.

Let us we go ahead with some of activities as how can we can create a new filegroup, how can we add the new secondary files to that file group. We will also look for the modifying the database file and changing the location. First capture the above image of the database file structure before doing any activities with database files. Please go to details explained by the comments in the script below and then we will compare the database file structure with original one.

USE MASTER 
GO

-- Adding a new filegroup to database 
ALTER DATABASE FileGroupDB 
ADD FILEGROUP NewFileGroup; 
GO

--Adding a new seconday and log files to a database to above created filegroup 
ALTER DATABASE FileGroupDB 
ADD FILE 
( 
    -- New secondary files added here 
    NAME = FileGroupDB_Data_1, 
    FILENAME = 'C:\FileGroupDB_Data_1.ndf', 
    SIZE = 15 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
), 
( 
    -- New log file added here 
    NAME = FileGroupDB_Log_1, 
    FILENAME = 'C:\FileGroupDB_Log_1.ldf', 
    SIZE = 5 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
) 
TO FILEGROUP NewFileGroup; -- Defining filegroup name here 
GO

-- Modifying size of the file 
ALTER DATABASE FileGroupDB 
MODIFY FILE  
( 
-- Changing log file size here 
NAME = FileGroupDB_Log, 
SIZE = 20 MB 
)

-- Moving log file to another location 
ALTER DATABASE FileGroupDB 
MODIFY FILE 
( 
NAME = FILEGROUPDB_log, -- Moving first log file to new location here 
FILENAME = 'C:\FileGroupDB_Log.ldf' 
) 
GO

(Click on image to enlarge)

After running the last script you will notified by following message,
"The file "FILEGROUPDB_log" has been modified in the system catalogue. The new path will be used the next time the database is started.
It will use the new file location when SQL Service will be restarted. We can have another option is taking database offline and use the same scripts written above and again database needs to back online which does not need to restart the database service.

Moving table or indexes on another filegroup - SQL Server by serverku

Because of data growth and performance issue we need to move tables or indexes to other file groups. The purpose of moving tables or indexes to improve the database performance as it reduces I/O from single drive or single filegroup. We can create another filegroup and move the tables and indexes to new created filegroups, then it will spit the I/O to two different filegroups. This way it will improve performance.

When we need to move the tables or indexes to another filegroup? We should move those table needs to move which are having more rows or heavily in size, Also need to move if those table's data are frequently updated. Here I would like to show the demo for the same. Let us see how can we move tables/indexes to another filegroup.

-- First we are creating database  
-- Main primary file on Primary filegroup 
-- Secondary files on secondary filegroup. 
-- Log files on different file group. 
CREATE DATABASE filegroupdb 
ON PRIMARY 
 ( name = filegroupdb_data, 
 filename = 'C:\FileGroupDB_Data.mdf', 
 size = 4 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ), 
filegroup secfilegroup 
 ( name = filegroupdb_data_1, 
 filename = 'C:\FileGroupDB_Data_1.ndf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 
log ON 
 ( name = filegroupdb_log, 
 filename = 'C:\FileGroupDB_Log.ldf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 

GO 

After creating database we will create a new table on Primary filegroup. If you not mentioned filegroup name, then it will be created on default filegroup. Let us create it.

USE filegroupdb 
GO 

-- Creating table on Primary filegroup 
IF( Object_id('tblFileGroup', 'u') > 0 ) 
  DROP TABLE tblfilegroup 
GO 

CREATE TABLE tblfilegroup 
  ( 
     objectid   INT NOT NULL PRIMARY KEY, 
     objectname VARCHAR(100), 
     createdate DATETIME 
  ) 
ON [PRIMARY] 

GO

The table is created and now we look the property of the table where it is created,

SP_HELP tblFileGroup

Now we are coming to the point which is tables/indexes actually will be moved. There are two ways through we can move the tables and indexes.

#1. By moving clustered index
Using this way we just need to move clustered index and tables and all indexes will be moved to different filegroup.

How can with #1 way?

-- Defining dropping constraint with move
ALTER TABLE tblFileGroup
DROP CONSTRAINT PK__tblFileG__9A619291029E5EB6 WITH (MOVE TO SecFileGroup)
GO

-- Adding constraint
ALTER TABLE tblFileGroup 
ADD CONSTRAINT PK__tblFileG__9A619291029E5EB6 PRIMARY KEY(ObjectId)
GO

Let us check again the properties of tables as successfully moved or not.

SP_HELP tblFileGroup

#2. Recreate a clustered index with drop existing
We just need to recreate a table's clustered index with drop existing clustered index on that.

How can with #2 way?

CREATE UNIQUE CLUSTERED INDEX PK__tblFileG__9A619291029E5EB6
ON tblFileGroup(ObjectId)
   WITH DROP_EXISTING
ON SecFileGroup

GO

You also worked with moving tables/indexes to another location, Share your ideas here!