move SQL Server from Standalone to Cluster environment

By - 



For each file to be moved, run the following statement.






















ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );












Stop the instance of SQL Server or shut down the system to perform maintenance.



Move the file or files to the new location.



Restart the instance of SQL Server or the server.




Verify the file change by running the following query.






















SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');












If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. This procedure applies to all system databases except the master and Resource databases.


If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.


Stop the instance of SQL Server if it is started.



Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.


    

  • 
For the default (MSSQLSERVER) instance, run the following command:
    

    

    

    

    

    

    

    

    

    

    

    NET START MSSQLSERVER /f /T3608

    

    

    

    

    

    • 

  • 
For a named instance, run the following command:
    

    

    

    

    

    

    

    

    

    

    

    NET START MSSQL$instancename /f /T3608
    

    

    

    

    

    • 






For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.






















ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )












Stop the instance of SQL Server. For example, run NET STOP MSSQLSERVER.


Move the file or files to the new location.


Restart the instance of SQL Server. For example, run NET START MSSQLSERVER.



Verify the file change by running the following query.






















SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');













The purpose of this blog is not to tell what is already available :) I have a little trick to make things faster. Here we go…



First lets understand what we are doing and what’s the reason behind it. Whenever we start SQL Server, it goes to registry to find out location of master.mdf, maslog.ldf and ErrorLog files. Once all three are located, logging would start and master database would be opened to know location of other databases mdf and ldf files.




Take Standalone machine out of network. Make sure there is no entry for host name or IP address anywhere in the network.


Install SQL server in clustered environment and keep IP address and Virtual Server name same.


While installing make sure that folder structure is same as standalone instance.


Bring clustered SQL to same patch level as standalone SQL. This reason of this is that system databases can’t be reused across builds.


make sure that you can do failover.


Take SQL Server offline, keep a backup of DATA folder to safe location


Now, take the DATA folder from Standalone server (Copy all folders which have mdf, ndf and ldf files) and put this in DATA folder on clustered SQL Server. Verify that all the files are at same location which was there on Standalone server.


Bring SQL server online. If there are failures then start looking at Error Logs and troubleshoot further.

Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more