SQL Server- Move TempDB to new location

Why and How to move TempDB to new location

Why:

There are mainly two reasons why you would like to move TempDB to a new location:

1. TempDB has grown big and existing drive doesn’t have enough disk space.

2. Moving TempDB to a new disk which is faster to improve the performance.

 

How:  Follow the steps to move TempDB to new location

First of all, check out the current location of TempDB files by executing the below query.

use tempdb

go

exec sp_helpfile

 

1

 

This will provide you the current location of tempdb.

now to move the tempdb to new location, execute the below command

 
USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\Tempdb\tempdb.mdf‘)
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = ‘C:\Tempdb\templog.ldf‘)
GO

 

2

 

I am moving tempdb from (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\) to (C:\Tempdb\) as i have only one drive in my machine. You can change it accordingly.

To make changes come in effect, restart SQL Service.

 

3

After restarting the sql services, check the location of tempdb now by running the below query.

use tempdb

go

exec sp_helpfile

4

 

 

Tempdb has been moved to a new location.

 

Hope this was helpful !!!!!

Advertisements

SQL Server – Error while enabling Windows feature: NetFx3

Problem:

I was installing SQL Server 2012 on Windows Server 2012, and the installation failed with the following error.

 

Error while enabling Windows feature: NetFx3, Error Code: -2146498298, Please try enabling
Windows feature: NetFx3 from Windows management tools and then run setup again. For more
information on how to enable Windows features, see http://go.microsoft.com/fwlink/?linkid=227143

1

 

Reason:

Since I didn’t enable .NET Framework 3.5 before installing SQL Server 2012 it failed.

 

Solution:

Enable .NET Framework 3.5 and then install SQL Server 2012.

Below is the procedure to enable .NET Framework 3.5 in Windows Server 2012

 

Using GUI

1. Go to Server Manager -> Manage -> Add Roles and Features

2

2. Click Next

 

3

3. Click Next button, the wizard will shows the Installation Type section where we have to select the Role-based or feature-based installation option that it has used to configure this server by adding roles, role services and features.

 

 

 

4

4. Click Next button, it will show the Server Selection section. Click Select a server from the server pool and over the grid, select the server

 

 

5

5. Click Next

 

6

6. Select the .NET Framework 3.5 Feature Checkbox and click Next.

 

7

7. Warning message informs us that for one or more installation selections are missing source files on the destination server, so we have to specify an alternative source path. Click the Specify an alternative source patch

 

 

8

8. Provide the required source file path for the .Net Framework 3.5 which is in Windows Server 2012 ISO file. Extract or Mount this. and click OK

 

9

9. TheResults section will show the installation status. Installtion will complete.

 

10

 

Once .net framework 3.5 is enabled. We can install SQL Server 2012 on Windows Server 2012.

 

 

Using Command Line

dism /online /enable-feature /featurename:NetFx3 /source:d:\sources\sxs

11

 

 

 

Hope this was helpful !!!

 

SQL Server – How to configure MSDTC in Windows Server 2012 Cluster

Below are the steps to configure MSDTC in Windows Server 2012 Cluster:

1. Open Failover Cluster Manager and Right-click on Roles and pick the “Configure Role” option.

1

2. Choose the option “Distributed Transaction Coordinator (DTC)” from the list. Click  “Next”.

2

 

3. Provide Name and IP for new role selected (Distributed Transaction Coordinator)

3

 

4. Select the Disk to be used for MSDTC

4

 

5. Review the configuration and confirm by clicking next.

5

 

6. The installation will run and in the last step you will show summary report. Click “Finish”.

6

 

7. Now you will be able to see a DTC role in the Failover Cluster Manager.

7

 

 

Hope this was helpful !!!

 

ERROR!!! “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”

Issue:

Sometimes you may get into issues while connecting to SQL Server with below error message

1

“A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

 

Solution:

There could be several reasons for you getting this error.  Below are few things you need to check to troubleshoot and resolve this issue.

1. Make sure the server you are trying to connect is up and running.  Ping the Server IP (where SQL is installed) from the client machine.

ping

 

 

2. If the Server is up, then make sure the SQL Server is up and running. To check that, go to SQL Server configuration manager (Start > All Programs > SQL Server 2005/2008/R2> Configuration Tools > SQL Server Configuration Manager)

Click on SQL Server Serivices on the left hand side and ensure the SQL Server (MSSQLSERVER) Service is running.

 

2

 

 

3. Check that the SQL Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Browser service enabled and running.

 

3

 

4.  Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes (if it is applicable to your environment) and TCP/IP protocol.

 

4

 

5.  For the TCP/IP protocol, right click and select properties to check the TCP/IP communication port as well. The default port is 1433, which can be changed for security purposes if needed.

 

5

 

6.  Try to telnet the SQL Server from Client machine with SQL port. Ensure that SQL port is open in Windows Firewall.

 

7

 

7. Make sure Remote connection is allowed in SQL Server. Open SSMS, connect to server, right-click server and select Properties. Click the Connections node and then Under Remote server connections, ensure Allow remote connections to this server is checked

 

6

 

 

Hope this was helpful !!!

How to Configure Database Mail

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users.

Configuring Database Mail using SSMS is the easiest way. Please see below the steps

 

Navigate to the Management folder of the appropriate server, right-click on Database Mail, and select Configure Database Mail

1

 

Click Next to pass the welcome screen

2

 

select the option Set up Database Mail by performing the following tasks and click Next

3

If database mail has not yet been enabled for this SQL Server instance, then you will see the screen shown. If it has been enabled, you won’t see the screen. Click Yes and proceed

 

FeatureNotAVailable

 

A profile is a collection of one or more SMTP accounts that can be used by SQL Server to send messages. In other words, when SQL Server wants to send a message, the message is sent to the profile, and then the profile is responsible for seeing that the e-mail is actually delivered. For fault tolerance, a profile can include more than one SMTP account.

To create a new profile, you must enter a profile name, an optional description, and then add and configure one or more SMTP accounts.

Enter Profile Name and Optional Description and click Add to create and configure SMTP Account.

4

 

Database Mail Account page.

5

To create/configure database mail account you need to fill in the details accordingly

Account Name

Optional Description

Outgoing Mail Server (SMTP). This specifies attributes of the SMTP Server that will be sending the e-mail, including these six options:

E-mail address – the e-mail account that has been set up for use with SQL Server’s database mail (for example, dbteam@company.com).

Display name – the display name of the above e-mail address that is shown as part of an e-mail.

Reply e-mail – the e-mail address used if someone should reply to an e-mail sent from the e-mail address entered above. Database Mail can’t respond to e-mails it receives, so you can either leave this option empty, or add your own e-mail address, just in case someone should respond to an e-mail received from SQL Server.

Server name – the name of the SMTP mail server.

Port number – the port number used by your organization’s SMTP server

This server requires a secure connection (SSL) – some SMTP servers require that SSL be turned on for additional security

6

There are three authentication modes available. In this tutorial, we have used Anonymous authentication (This option is rarely used because it allows anyone to access the SMTP server).  So, select accordingly.

Click OK to return to New Profile page

 

 

7

click on Next to reach the Manage Profile Security screen. I already had two profiles configured, that is why the below screen shows two more profiles.

Now you have to assign the profile as either public or private. A private profile is only usable by specific users or roles, while a public profile allows any user or role (with access to msdb) to send mail.

8

Click Next to move on to the Configure System Parameters screen.

This option in the Database Mail Configuration Wizard allows you to set the values of specific Database Mail parameters for the profile. Leave them to default and click Next

9

 

Review of the summary screen, click on Finish

10

 

If the Configuring… screen reports success, then Database Mail has been successfully set up

11

 

Now, In order to check whether the configured Database mail is working, right-click on the Database Mail folder in SSMS and select Send Test E-Mail,

12

 

In Database Mail Profile, select the profile just created and in To, type in your email address and click on Send Test E-Mail.

13

 

 

14

Database Mail has been configure.

 

 

Configure a SQL Server Agent Operator

configure a user to receive notifications about Microsoft SQL Server Agent jobs

When we configure a Maintenance Plan to send an e-mail, created with either the Maintenance Plan Wizard or the Maintenance Plan Designer, we can’t enter an e-mail address directly into the Maintenance Plan. Instead, we configure e-mails to be sent to an operator.

An operator is an alias for a specific person (such as yourself), or a group (such as a DBA mail group). This alias is more than just a name; it is actually a set of attributes that include the operator’s name, the operator’s contact information, and the operator’s availability schedule.

 

To create a new operator, open SSMS, navigate to the SQL Server instance you wish to configure, open up the SQL Server Agent folder, navigate to the Operators folder, right-click on it and select New Operator

15

 

New Operator screen has lots of options, we will focus only on the three that are most important.
Name – enter the name of the person or group who you want to receive notification e-mails.
Enabled – this option is selected by default, and you don’t want to change it, otherwise you won’t be able to receive any notices from SQL Server.
E-mail name – enter your e-mail address, or the group’s e-mail address here.

 

16

 

That’s it. All the other options are optional, and you can use them if you want to, we have left them to blank. click on OK

 

How To configure SQL Server Agent to use Database Mail

 

  • In Object Explorer, expand a SQL Server instance.
  • Right-click SQL Server Agent, and then click Properties.
  • Click Alert System.
  • Select Enable Mail Profile.
  • In the Mail system list, select Database Mail.
  • In the Mail profile list, select a mail profile for Database Mail.
  • Restart SQL Server Agent.

 

 I hope this was helpful.

 

 

SQL Server Best Practices – Part 1

 

  • Use a dedicated server for your database [it will prevent security breaches and leaks. Manageability and Availability will be enhanced]

 

  • Install only required components. [It is good practice to install only needed features and by doing so it will reduce the attack surface and better resource utilization]

 

  • Do not install sample databases and sample codes on SQL servers in the production environment. [If needed, install the samples only in the development and test environments]

 

  • Windows Authentication mode is more secure than SQL Authentication. [Windows Authentication mode is the default and recommended authentication mode]

 

  • If there is still a need to use SQL Authentication – enforce strong password policy.

 

  • Disable the SA account and rename it. Do not use this account for SQL server management. [Before Disabling/renaming the SA account, verify that another account with administrative privileges exists in order to have access to the SQL Server.]

 

  • Keep minimum administrators as it can be.

 

  • Allocate different administrative accounts if there is more than one administrator. [This will set accountability]

 

  • Remove BUILDIN\Administrators group from the SQL Server Logins. [Because it gives SQL Server System Administrator rights to any account in the Windows Local Administrators group].

 

  • Change default SQL Server ports associated with the SQL Server installation to keep hackers from port scanning the server. [Default port (i.e 1433) is well known and is common target for hackers. Therefore it is recommended to change default port associated with the SQL Server installation]

 

  • Enable logging SQL Server login attempts (failed & successful). [The default mode is:Failed Logins Only. Thus, it is recommended to set the auditing mode to be Both Failed and Successful Logins.]

 

  • Hide SQL Server Instance from Broadcasting Information [The SQL Server Browser service enumerates SQL Server information on the network. In such way attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances. Thus it is strongly recommended to hide SQL instances from being shown in the network]

 

 

  • Recent service packs and critical fixes should be installed for SQL Server and Windows. [Service Packs contain important fixes for a product and are tested even more rigorously than incremental releases like cumulative updates, so they’re pretty important to install. But like everything else, they may contain something that has unexpected side effects– so you need to test your application with the service pack before you change it in production.]

 

No global profile is configured. Specify a profile name in the @profile_name parameter

Yesterday, a developer from application team sent an email saying, he is not able to send mail alerts using database mail from a production server.

I asked which DB Profile name [@profile_name] you are using.   As we have different profiles in different servers.  [Which is not a best practice.  We should use same profile name within our environment to keep uniformity]

He was using the correct profile. Then I requested him to share the error details.

Error Details were as below:

“No global profile is configured. Specify a profile name in the @profile_name parameter”

I realized the profile was not configured as default profile.  So, I made the DB mail profile as default and it resolved the issue.

 

Below is the procedure to configure Global Profile in Database Mail.

  1. Connect to the target SQL Server with SSMS (SQL Server Management Studio)
  2. Expand the Managementsection
  3. Right-click on Database mailand select Configure Database Mail

1

4.  From the Wizard, click on Next >

2

5.  From the Task screen, select Manage Profile Security and click on Next>

3

6.  From the Manage Profile Security screen, select the target profile, then click in the Default Profile tab to activate the drop-down. Select Yes from the drop-down then click on Next >

4

 

7.  From the Complete the Wizard screen, click on Finish

5

 

 

Database Properties

SQL Server includes several database property configuration options that can impact performance and recoverability in a production environment. In this post we will discuss the recommended configuration value as per the best practices of Microsoft.

  • Auto Close
  • Auto Create Statistics
  • Auto Shrink
  • Recovery – Page Verify
  • Recovery Model
  • Auto Update Statistics
  • Auto Update Statistics Asynchronously
  • Compatibility Level

 

Auto Close

When enabled, the AUTO_CLOSE option causes the database to shut down cleanly and the resources to be freed after the last database user exits. New connections to the database reopen it.

The AUTO_CLOSE option is not intended to be used for frequently accessed, highly concurrent shared databases. Thus, it should be enabled only for single-user desktop applications. Additionally, some of the performance issues experienced with AUTO_CLOSE prior to SQL Server 2005 have been resolved. However, performance issues can still occur in SQL Server 2005 because a database closure results in objects being cleared from the plan cache. Recommended/Default value is FALSE.

Auto Create Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If AUTO_CREATE_STATISTICS is disabled, the statistics will not be created on columns that are not indexed when needed by the query optimization process. The performance of incoming queries may suffer if AUTO_CREATE_STATISTICS is disabled. Thus, you should always keep this option enabled for each user database. Additionally, statistics are essential to the query optimization process. If new statistics are needed but have not been created, query performance will be affected. Recommended/Default value is TRUE.

Auto Shrink

The AUTO_SHRINK option, when enabled, causes data and log files to be automatically shrunk if more than 25 percent of a file has unused space. This option can cause performance and concurrency issues during the shrink process. It can also cause index fragmentation after a shrink process has been performed. Ensure that AUTO_SHRINK is turned off by default. If free space must be generated, do that by using the DBCC SHRINKFILE or DBCC SHRINKDATABASE commands. In addition, always rebuild or reorganize the indexes after a shrink operation in order to fix any fragmentation that might have occurred in the data files.

Torn Page Detection (Page Verify)

When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte database page where the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the result will indicate that a torn page was detected and that the page was initially written incorrectly.

Recovery Model

The recovery model controls transaction log maintenance and can have serious effects on a system if it is set incorrectly or inappropriately. If the wrong recovery model is specified for the back-up strategy being used, data loss can be experienced during a restore, or log files can grow to the point where they consume all space on the drive.

When the recovery model is set to Simple, no log backups are taken and log space is automatically reclaimed to keep space requirements small. This essentially eliminates the need to manage the transaction log space. However, in Simple mode, you can only recover to the end of the last backup. This is because there are no transaction log backups to apply in order to bring the database to a consistent current state.

When the recovery model is set to Full or Bulk-Logged, log backups are taken and the system can be restored to a current state or to a previous state. When Bulk-Logged is being used, certain operations are not logged. This reduces log space usage by special logging during most bulk operations. In addition, when the Bulk-Logged model is used, point in time recovery is not supported.

Auto Update Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If the statistics become outdated and significant changes have occurred against the underlying data, this can result in less than optimal query performance. If AUTO_UPDATE_STATISTICS is disabled, out-of-date statistics will not be automatically updated.

Ensure that AUTO_UPDATE_STATISTICS is enabled for each user database on the SQL Server instance. For highly volatile data, consider more frequent statistics updates using the system stored procedure, sp_updatestats, or the UPDATE STATISTICS command.

Auto Update Stats Async

AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to On, queries do not wait for the statistics to be updated before compiling. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay as a result of waiting for updated statistics, query response times are predictable. However, the out-of-date statistics may cause the query optimizer to choose a less efficient query plan.

You should consider setting the AUTO_UPDATE_STATISTICS_ASYNC option to On when the following characteristics apply to your application:

  • Client request timeouts caused by one or more queries waiting for updated statistics have been experienced.
  • Predictable query response times are required, even at the expense of occasionally running queries with less efficient query plans because of outdated statistics.

Compatibility Level

 Setting the databases to a lower compatibility level should be done only as an interim aid during migration. Keeping the databases at the same level indefinitely will disable some of the new features that later versions provide.