Moving System Databases files

Moving System Databases files from old location to new location

Some times after instalation of the sql server, we need to change the system databases file locations. the below process guide us to change the system databases files locations from old to new.

Moving the  Tempdb,Msdb and Model database files from old to new location:

with help of the below command we can move the system databases files from old to new locations logically (not physically).

USE master;

GO

alter database tempdb MODIFY FILE (NAME = tempdev,FILENAME='NEW PATH');

GO

alter database tempdb MODIFY FILE (NAME = templog,FILENAME='NEW PATH');

GO

USE master;

GO

alter database msdb MODIFY FILE (NAME = MSDBData,FILENAME='NEW PATH');

go

alter database msdb MODIFY FILE (NAME = MSDBLog,FILENAME='NEW PATH');

go

USE master;

GO

alter database model MODIFY FILE (NAME = modeldev,FILENAME='NEW PATH');

go

alter database model MODIFY FILE (NAME = modellog,FILENAME='NEW PATH');

go

1. Now stop the sql server services to move the system databases files from old to new locations physically. 
2. after moving the system databases files physically, start the sql server services and with help of below command we can confirm the databases new location.


USE msdb;
SELECT physical_name from sys.database_files
USE model;
SELECT physical_name from sys.database_files

 Note: no need to move the tempdb files physically, because it's recrate always when sql services are start.

 Moving the Master and Resources database files:

 Edit the startup parameters to reflect the new path for –d(datafile), –l(logfile) and -e(sql log file) parameters.

go to start ----> all programs ----> Micro soft sql server 2005 ----> configuration tool ----> sql server configuration manager 

 After this we need to follow the below steps for sql server 2005, why because the resource database is depended on master database. so, we need to put the resource database files at master database files location.

Note: above sql server 2005 version no need to follow the below steps. just restart the sql services. because the resources database is independent.

1. Stop the services.
2. Move the master and resource database files to the new location
3. Start the services using NET START MSSQLSERVER /f /T3608 (*MSSQLSERVER is for default instance, if you have installed named instance then you need to use NET START MSSQL$Instancename /f /T3608)

 open the new query window and run the below command.(don't open the sql instance in management console). other wise run the below command in sqlcmd.

USE master;
GO
alter database mssqlsystemresource MODIFY FILE (NAME = data,FILENAME='NEW PATH\mssqlsystemresource.mdf');
go
alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME='NEW PATH\mssqlsystemresource.ldf');
go
alter database mssqlsystemresource set READ_ONLY;
go

 stop and start the sql server services and confirm the new locations.

Note: Please post your comments...

Happy Learning

How to stop and start the sql server services.

we have 4 ways to stop and start the sql server services.
1) services.msc
2) Management studio
3) Command prompt
4) Configuration Manager

Services.msc:
  with help of following steps we can stop and stat the sql server services.
1) Go to run

2) Type services.msc and press enter.
3) select the sql database engine services and then right click on service.

4) click on the stop button.

Note : The sql agent is dependent on the sql database engine services. so, when we stop the sql database engine services the agent services stop automatically. But when you start the sql database engine service the agent service doesn't start automatically. we need to start the agent services manually.
Start the sql server services:
1) 1) Go to run

2) Type services.msc and press enter.
3) select the sql database engine services and then right click on service.
4) press on the start button.

5) select the sql agent service and right click on the service.
6) press on the start button.

Through Management studio:
 with help of the following steps we can stop and start the sql services through Management studio.
1) Open the Management studio.
2) Connect to the sql instance, which we want to stop.
3) right click on the instance name and select the stop button.

Note: Once we closed the Management studio after stop the sql server services, it is not possible to start the sql server services through Management studio. we need to follow the other process to strat the sql server services.
Command Prompt :
1) Open the run and type cmd and press ok button.
2) type below command on cmd and press enter.
net stop mssqlserver --- ( for default instance)
net stop mssql$instancename ---- ( for named instance )

3) type the below command for start the sql server service.
net start mssqlserver ---- ( for default instance )
net start mssql$instancename ---- ( for named instance )

Configuration Manager :
1) Go to start and select all programs and then select Microsoft sql server under this one select the configuration tools and then select the sql server configuration manager.

2) select sql server service on left side panel like on above screen shot. and then select the sql server instance name which you want to stop the services. and right click and select stop button to stop the sql database engine services.

3) same process to start the sql server services.

Note: Please post your comments...

Happy Learning

Configure the Master target server or multi server environment

Configure the Master target server or multi server environment:

Multi server administration requires that you set up a master server and one or more target servers. Jobs that will be processed on all the target servers are first defined on the master server and then downloaded to the target servers.

Automating administration across multiple instances of SQL Server is called multi server administration. Use multi server administration to do the following:

·         Manage two or more servers.

·         Schedule information flows between enterprise servers for data warehousing.

To take advantage of multi server administration, you must have at least one master server and at least one target server. A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job. The following illustration shows the relationship between master and target servers:

If you administer departmental servers across a large corporation, you can define the following:

·         One backup job with job steps.

·         Operators to notify in case of backup failure.

·         An execution schedule for the backup job.

Write this backup job one time on the master server and then enlist each departmental server as a target server. From the time of their enlistment, all the departmental servers run the same backup job, yet you defined the job only once.

NOTE:  Multiserver administration features are intended for members of the sysadmin role. However, a member of the sysadmin role on the target server cannot edit the operations that are performed on the target server by the master server. This security measure prevents job steps from being accidentally deleted and operations on the target server from being interrupted.

Configure the Master target server

Here’s a basic look at how to setup Multi Server Administration. First thing I’m going to do is register both servers in SQL Server Management Studio (SSMS). If you do not see the Registered Servers tab in SSMS navigate to 'View' | 'Registered Servers' or press 'Ctrl + Alt + G'.

Once both servers are registered I can start configuring Multi Server Administration. To do this right click on 'SQL Server Agent' on your master server and choose 'Multi Server Administration' | 'Make this a Master…' to begin the process.

Click Next on the Welcome for the Master Server Wizard.

The next screen is where we can configure an operator. Just like when we create an operator for local jobs, we can create one for our Multi Server jobs. You can also leave this section blank if you prefer not to have an operator although I would not recommended this practice.  Once completed, press the 'Next' button to continue.

On the Target Servers screen is where we specify our target servers. You can have multiple targets, but for this example I’m only using one.  You can select target servers using your 'Registered Servers' from your SSMS session or you can add a connection by clicking the 'Add Connection...' button.  I’m going to select Mirror as my target and click the 'Next' button to continue the process.

Next, SQL Server will check that the versions of the master and target server are compatible as shown on the screen shot below.

On the Master Server Login Credentials screen security is checked between the master and target servers. If needed, the check box can be selected to create a login and assign it rights to the master server. Click the 'Next' button to continue the process.

Once you click the 'Finish' button on the Complete the Wizard screen it will start the configuring the servers.

Be aware that a common problem exists during configuration and the following error (The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server 'Server Name'.  Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.)  (Microsoft SQL Server Error: 22026)) may be thrown as shown on the screen shot below:

As the error indicates, we need to make a registry change on the target server. To accomplish this, logon to the target server and navigate to 'Start' | 'Run' | type 'Regedit'. As a best practice, be sure to backup your registry before making changes.  From the Registry Editor browse to 'HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<Your instance>\SQLServerAgent and change the MsxEncryptChannelOptions' value to '0'. Save the change and close the Registry editor. 

Once this is complete, run the wizard from the master server again and it should finish successfully as shown below.

To verify everything was configured correctly, connect to both servers and beside SQL Server Agent it should have either '(MSX)' or 'TSX:<master server>' as shown below.

To set up a Multi Server Job under SQL Server Agent you must connect to the master server in SSMS and navigate to 'SQL Server Agent' | 'Jobs' | 'Multi-Server Jobs". Configure the job as needed and on the 'Targets tab' select the target servers you want this job to execute on.

Once finished, click the 'OK' button to save job to both servers.

The same general process applies to Maintenance Plans, connect to the master server and navigate to 'Management' | 'Maintenance Plans' | right click and choose 'New Maintenance Plan…'.  This process starts by naming the job as shown below.

Configure the Maintenance Plan as needed and on the Servers button select the servers you want this Maintenance Plan to execute on.

Once finished, click 'Save and Close' to save both Maintenance Plans to both servers as shown below.

To make a target server

1.       In Object Explorer, connect to an instance of the Microsoft SQL Server Database Engine, and then expand that instance.

2.       Right-click SQL Server Agent, point to Multi Server Administration, and then click Make this a Target. The Target Server Wizard guides you through the process of making a target server.

To enlist a target server from a master server

1.       In Object Explorer, expand a server that is configured as a master server.

2.       Right-click SQL Server Agent, point to Multi Server Administration, and then click Add Target Servers.

3.       Complete the Target Server Wizard, which guides you through the process.

To defect a target server from a master server

1.       In Object Explorer, expand a server that is configured as a target server.

2.       Right-click SQL Server Agent, point to Multi Server Administration, and then click Defect.

3.       Click Yes to confirm that you want to defect this target server from a master server.

To defect multiple target servers from a master server

1.       In Object Explorer, expand a server that is configured as a master server.

2.       Right-click SQL Server Agent, point to Multi Server Administration, and then click Manage Target Servers.

3.       Click Post Instructions, and then in the Instruction type list, select Defect.

4.       Under Recipients, do one of the following:

·         Click All target servers to defect all target servers of this master server. Use this option if you want to completely uninstall the current multiserver administration configuration.

·         Click These target servers, and then click the corresponding Select box, to defect some, but not all, target servers of this master server.

Security

Distributed jobs that have steps which are associated with a proxy run under the context of the proxy account on the target server. Make sure that the following conditions are met or job steps that are associated with a proxy will not be downloaded from the master server to the target:

·         The master server registry sub key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQL Server Agent\AllowDownloadedJobsToMatchProxyName (REG_DWORD) is set to 1 (true). By default, this sub key is set to 0 (false).

·         A proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.

If job steps that use proxy accounts fail when downloading them from the master server to the target server, you can check the error message column in the sysdownloadlist table in the msdb database for the following error messages:

·         "The job step requires a proxy account; however proxy matching is disabled on the target server."
To resolve this error, set the AllowDownloadedJobsToMatchProxyName registry sub key to 1.

·         "Proxy not found."
To resolve this error, make sure a proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.

Secure Sockets Layer (SSL)

Full Secure Sockets Layer (SSL) encryption and certificate validation are enabled for connections between master servers and target servers by default.

The wizard takes you through the following steps:

·         Checking the security settings for the SQL Server Agent service and the SQL Server service on all servers intended to become target servers.
We recommend that both services should be running in Microsoft Windows domain accounts.

·         Creating a master server operator (MSXOperator) on the master server.
MSXOperator is the only operator that can receive notifications for multiserver jobs.

·         Starting the SQL Server Agent service on the master server.

·         Enlisting one or more servers as target servers.

If you have a large number of target servers, avoid defining your master server on a production server. Otherwise, target server traffic can slow performance on your production server. If you also forward events to a dedicated master server, you can centralize administration on one server.

Note: I collected the above information from different links. If you have any doubts on the above topic, please feel free to post the question, i will try to get the answer for your question.

HAPPY LEARNING