#MS SQL Server DBA Daily Tasks

[adismith011]

Get 24*7 Online Oracle DB Support with Cognegic’s Remote DBA Services

What is Oracle Database?

First of all, Oracle is Software Company who develop one of the best software to easily handle and manage all types of data, and information and that type of software is Oracle Database. The Oracle Database or Oracle DB is a kind of relational database management system which was developed in the year 1997 and it is one of the most trusted and widely used relational database engines to create and manage databases.

The Incredible Advantages of Using Oracle Database

We all prefer numerous of databases to get our work done such as MS SQL Server, MySQL, Cassandra, and MongoDB and so on. But apart from these databases, only Oracle is one of the best and most trusted databases which are utilized by millions and users on a daily basis. Due to its huge popularity, the Oracle released its new version which comes along with the plethora of features and services for their users so that they can easily manage their day-to-day task.

1)    The first and most important advantage of using Oracle database is, it is perfectly utilized by all corporations level applications including large enterprise, small size business and so on

2)   Obviously, it has the capability to handle an enormous amount of data and information without any single point of failure

3)    the most important thing about Oracle is, it comes along with one best tool i.e. ACID which easily ensures the integrity of data

4)    It also provides support for online backup and recovery

Some Common Oracle Issues create Problem for Users

Well! It doesn’t matter which type of database you are using for your work because in every type of databases you will face some technical issues. In the same way, in Oracle DB you have to face some common issues which create the problem for your and prevent you to work with your Database. Some of the most common issues related to Oracle Database.

•    Invalid number issue

•    Internal code error issue

•    Improper PGA setup issue

•    Improper disk setup issue

•    Improper memory configuration issue and so on

Solve all type of technical Glitches through Cognegic’s Oracle Database System

Cognegic is USA based organization and giving outstanding amongst other administrations in Database. Our developers are giving complete administrations in Oracle database advancement. Our developer's group has enough skills and expertise in Oracle database application, usage, establishment, administration, tuning and considerably more.

We have the expertise and working background to give the entire life-cycle execution process for the Oracle database items. We have a group of the engineer who has a solid database administration foundation. We additionally have database heads, which perform remote set up and organization of our clients' database servers.

For More Info: https://cognegicsystems.com/

Contact Number: 1-800-450-8670

Email Address- info@cognegicsystems.com

Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

You Can Contact to us via this Keywords also

·         Remote DBA Services

·         Oracle Database Systems

·         Free Oracle Database Tool

·         Online Oracle Database Tool

·         Online Oracle DB Support

·         Oracle Database Solution

[gaurava16fc]

MS SQL Server - Checklist to be followed by DBA on daily basis

Hi Friends,

Hope, you are doing fine!

Although I do not have any exposure as a DBA profile in my professional career, but still I had learnt something by seeing the various professionals during my past organisations and would like to share the below consolidated few points as a “Check-List” that may make DBA’s day-to-day life easier & happy:

Check System Event Logs and SQL Server Error Logs for…

[marcosplavsczyk]

TempDB is a section of SQL Server instance that is used to store temporary data of tables, views, objects, functions, and indexes and is often overlooked in the grand scheme of performance optimization; hence it is left on default by DBAs. It is not an overly complicated area, but in everyday DBA tasks, this area gets a little less attention. Leaving the TempDB configuration on default will not result in a production environment crash, but it will take a toll on the overall SQL Server performance and, in worse case scenarios, jeopardize contingency plans in disaster recovery scenarios. With a little optimization management and customization in key areas, the aforementioned scenarios can be averted, and the performance of a SQL Server instance increased to mitigate the toll.

With ApexSQL Manage, a SQL Server instance management tool, potential problems can be detected by performing various health checks of SQL Server instances, among which is a TempDB configuration check. For the majority of failed health checks, there is a one-click solution where the application generates a FixSQL script to resolve the issue.

Here are the questions that should be considered when evaluating SQL Server performance concerning TempDB:

Are TempDB log and filegroup files located on the same HD drive as the instance?

Is the number of tempdb files the same as the number of processor cores present on SQL Server?

What is the TempDB Recovery Model type?

Is TempDB response time (writing and reading) below 20 ms?

Do TempDB log and data files have an unreasonable growth size?

Is TempDB size set to less than 10% size of the largest database on the server?

Do data files for every available TempDB have a dedicated drive?

TempDB log and filegroups files location

Best practice if not to keep TempDB log and filegroups on the same HDD as SQL Server binaries. This is the default location when installing and configuring a SQL Server instance, but in reality, this action is considered a potential risk for the environment. Moving the TempDB files on a different disk provides a failsafe measure for these scenarios and a viable recovery plan. Executing this rule will check if there the TempDB files are located on the same HD drive. There is no FixSQL script to rectify this issue since the procedure for moving the files must be done manually:

TempDb data files and processor cores number mismatch

This is still considered to be the biggest “it depends” in the SQL community. The main reason to use multiple data files in high trafficked SQL Server instances is to increase the I/O to TempDB. Official Microsoft recommendation is to keep a 1:1 ratio of TempDB data files and processor cores. But the general rule of thumb is to increase the TempDB files to follow the number to processor cores present on the instance. This is, of course, dependent on the actual environment configuration and use cases. The number of tempdb files should be equal to the number of processor cores for 8 or fewer cores. But, for environments with more than 8 processor cores, the number of TempDB files should be equal to 1/4 ~ 1/2 of total processor cores. When increasing the number of TempDB files, make sure to increase them gradually and only to the point of alleviating contention.

Running this rule will check if the TempDB data files match the number of processor cores:

TempDB Recovery Mode check

The recovery model of Tempdb should always be set to SIMPLE. By default, the TempDB recovery model cannot be changed.

Running this rule will check the recovery model of TempDB:

TempDB Response time (writing and reading) below 20 ms

The response time of TempDB is a clear indicator that there are performance issues on the SQL Server instance. Best practice response times for TempDB read and write operations should be less than 20 ms. To remedy the issue, it is recommended to keep the TempDB database files located on a fast dedicated drive, e.g., RAID or SSD drives. Running the rule will show if the TempDB response time of a chosen instance is not in compliance with best practices:

TempDB log and data files unreasonable growth size

The key focus here is to avoid auto-growth as much as possible by pre-grow the files to the desired amount. Performance issues on the SQL Server instance can occur if growth increments are set to a low value where the Server will often result in auto-growth operations, which will cause a performance strain on that instance since it is waiting for the file to be grown. Calculate these operations daily, and the result will be a great performance decrease. The best practice is to pre-grow the files and set the file growth increment to a reasonable size to avoid the TempDB database files from growing too often as a result of small growth values.

Note: If the TempDB file size is under 200 MB, set the File Growth to Megabytes value, otherwise set the File Growth to a Percent value.

Before embarking on this endeavor, the recommendation is to determine the best average value by observing the TempDB database files and instance performance in a few days. Once the average value is determined, simply run the rule, click the FixSQL button to generate a script, and customize it accordingly. When the parameters are configured, click the Execute button to apply the changes to the instance. By default, ApexSQL Manage is checking if the TempDB files are greater than 1024 MB and in this case, FILEGROWTH is set to 10% of the total file size:

TempDB size set to less than 10% size of the largest database on the server

The initial size of the TempDB, when installing a SQL Server instance, is set to 8MB with a growth increment of 64MB. This is a drastically low setting for production environments and, if not addressed, will result in a great performance toll of said instance. The best practice is to set the TempDB size to at least 10% of the largest database on the instance, and if the performance is still taking a hit, increase the value to a point where the performance strain is diminished. In this example, the largest database on the selected instance is a total of 3360MB (MDF, LDF, and NDF files), and the recommended size in the generated FixSQL script is to set the TempDB size to no less than 336MB. Given that this instance has 4 TempDB files, each file is going to be set to 85MB:

TempDB data files not located on a dedicated drive

For SQL Server 2012 and above, the option to keep TempDB data files on an SSD drive has been implemented. This opened a new door into the performance increase of SQL Server instances. Since production environments tend to grow to meet the ever-increasing demand for free disk space, it is recommended to keep the TempDB files on a separate SSD drive dedicated solely to the TempDB data files. Following this best practice, the TempDB size can be pre-grown to a previously determined amount, and this will, in a term, minimize the need for often auto-growth operations, which can affect SQL Server instance performance and eliminate the possibility of occupying disk space needed for other applications. If the execution of the rule fails, click the FixSQL button to generate the script and specify the parameters for the new location of TempDB data files:

Conclusion

With a few tweaks of TempDB settings, the overall performance of SQL Server instances can be increased to a significant amount. Using ApexSQL Manage to examine TempDB configurations and determine the health status on multiple instances at once provides a faster and easier method of detecting potential issues and resolving them before they have a major performance impact. Enforcing TempDB best practices will ensure that peak performance is being met across all managed instances.

[marcosplavsczyk]

Introduction

For a SQL Server DBA handling multiple databases on any given time, knowing how to set up regular backup schedules, backups with unique names on a daily basis, making backup mirrors for redundancy, cleaning up old backup files is important. Equally important is automatic confirmation that the backups have been successfully created for the databases with an email notification. There are a couple of different ways to set up email notifications which can be done from Microsoft’s SQL Server Management Studio, or from a third party application for managing MS SQL Server backups like ApexSQL Backup.

In this article, we are going to cover three different ways to set up email notifications for backup jobs in SQL Server:

Setting up email notifications with SQL Server Agent

Setting up email notifications using SQL Server maintenance plans

Setting up email notifications using ApexSQL Backup

For the SQL Server Agent service to be able to send email notifications for backup jobs, as well as for Maintenance plans, there are a couple of prerequisites that have to be performed first which we will go through.

Those prerequisites are:

Enable Database Mail

Configure SQL Server Agent to use Database Mail

Create an Operator

Enable Database Mail Database Mail can be enabled in two ways, via Database configuration wizard or using templates, specifically Simple Database Mail Configuration script.

Configuring Database Mail using Database configuration wizard First off, we need to open up SSMS and connect to our server. After that, expand the instance node for which we are configuring the Database Mail for. Expand the Management node, right click Database Mail and select Configure Database Mail

The Database configuration wizard opens to the Welcome page where we click on Next. Optionally, we can check the Skip this page in the future checkbox before clicking on Next

In the Select Configuration Task page, since we are configuring Database Mail, select the first option, Set up Database Mail by performing the following tasks and click on Next

Note: After pressing Next, if Database Mail has not been enabled previously, a message will pop up saying: The Database Mail feature is not available. Would you like to enable this feature? Clicking on Yes will enable this feature

In the New Profile page, we input the Profile name and optionally, Description, and after that click on Add..

In the New Database Mail Account page, we are going to create a new Database Mail account containing the information for sending email to an SMTP server.

Under Account name we input the name of the new account, under Description optionally enter the account description.

Under E-mail address we input the email address that email notifications are going to be sent from. Under Display name we input the name which will be shown on email notifications sent. Under Reply e-mail, optionally we can input the email address where replies to email messages sent from this account will be sent. It can be the email of the database administrator, or it can be left blank. Under Server name we input the SMTP address for our mail server, which is most commonly in the form of smtp..com. After that we input the port number for the server under Port number. In most cases, the port number is 25, however, it is advised to consult the mail administrator for further information. Afterwards, checking the option This server requires a secure connection (SSL) will encrypt communication using Secure Sockets Layer.

Under SMTP authentication, we are presented with three options for the authentication. The type of authentication varies, in this case we are going to choose Basic authentication which then requires to fill the User name, Password and Confirm password text boxes with information used to log on to the mail server. After filling all the fields, we click on OK to finish the creation of new Database Mail account.

After that we have created a new account which can be then seen under SMTP accounts. At this point we can create more accounts which can be sorted by priority as a fail safe in case that some of the accounts fails to send the email notification. After that, we click on Next onto the next page

Under the Manage Profile Security page, we have two tabs, Public Profiles and Private Profiles. In the Public Profiles tab, we configure the account that will be available to any user or role with access to mail host database (msdb) to send email notifications using that profile. In the Private Profiles tab, we select the users and which profiles they can use, and after that we click on Next to continue

In the Configure System Parameters page, we can adjust some of the parameters for the emails. After that, we click on Next

In the Complete the Wizard page, we can overview the operations which will be completed. If everything is set up properly, we can click on Finish to complete the wizard, if not, we can go back to the problematic part and correct it, afterwards selecting Finish if everything is in order

After pressing Finish, the wizard will perform all the actions and show them in a window stating if they were successful or not. After that, we click Close to close the wizard

Configure Database Mail using Simple Database Mail Configuration template Open SSMS, go to View and select Template Explorer

After the Template Explorer opens, find and expand the Database Mail node, then double click on Simple Database Mail Configuration, which opens up a query window.

Go to Query and select Specify Values for Template Parameters… or press Ctrl+Shift+M on your keyboard to open up Replace Template Parameters window

In the Replace Template Parameters window insert appropriate information for the parameters and press OK

Execute the script by pressing F5 on your keyboard after which you should get the message: “Command(s) completed successfully.”

Note: Although this script enables Database Mail and creates a new account and profile, it does not grant any database users access to the profile and by default, only the members of the sysadmin fixed security role can use this profile. If you would want to grant access to profiles, please read this document

Configure SQL Server Agent to use Database Mail In the Object Explorer, expand a SQL Server instance, right click on SQL Server Agent and click on Properties

In the SQL Server Agent Properties window, select Alert System from the navigation tab to the left. To the right, check the box near Enable mail profile, for the Mail system set Database Mail, for Mail profile set the profile we created and after that click on OK. After that, restart the SQL Server Agent service

Creating an operator As with enabling Database Mail, creating an operator is a process which can only be accomplished by a member of sysadmin fixed server role. To create an operator, expand the SQL Server agent node, right click the Operators folder and select New Operator…

In the New Operator window, under General tab, enter the Name for the operator and operator’s email address under E-mail name under Notification options. Also make sure that the Enabled checkbox is ticked. Click afterwards on OK to confirm the creation of the new operator

An alternative way to create an operator is through a T-SQL script provided below

USE msdb; GO EXEC dbo.sp_add_operator @name = N'ApexSQL', @enabled = 1, @email_address = N'john.doe@xmail.com'; GO

For more information about adding operators through Transact-SQL, and the stored procedure sp_add_operator, see here

Setting up email notifications with SQL Server Agent

Email notifications for backup jobs using the SQL Server Agent are usually setup when creating the backup job. For this you have to have SQL Server Agent service running. One way to check that is to open up Services by going to Start -> All Programs -> Windows Administrative Tools -> Services. Note that this may differ slightly from operating system to operating system. Another way is to search for Services using your operating system’s search feature. When opened, scroll to find SQL Server Agent (Server_name), and check if it’s running. If not, right click the service and click on Start or press Start in the top left corner of the window

Alternative way of starting SQL Server Agent is through SSMS’s Object Explorer. For the server upon which email notifications for backup jobs should be setup, right click on SQL Server Agent and click on Start to start the service. In the window that appears, asking whether we are sure to start the agent, click on Yes and the service will proceed to start.

After that, proceed to make a new job by expanding the SQL Server Agent, right clicking on the folder Jobs and clicking on New Job…

For the sake of conciseness of this article, the procedure of creating regular scheduled backup jobs is explained thoroughly in this article. In this article only the email notification for backup jobs wil be addressed

In the New Job window, click on Notifications tab in the navigation pane. There, check the box near E-mail, from the first dropdown menu choose the operator, from the second, choose when to notify: When the job succeeds notifies the operator via email notification if the backup job has completed successfully, When the job fails notifies the operator via email notification for backup jobs if the job has failed and When the job completes notifies the operator via email notification for backup job when the job is completed, successfully or not. After selecting that, press OK to update the already created job, or continue creating the job by selecting any of the tabs in the navigation pane

Setting up email notifications for backup jobs using Maintenance plans

Maintenance plans are a another way of setting up backup jobs with varying degrees of customization. In this article, we are going to go over creating a backup job using Maintenance plans in short. More detailed procedure on creating backup jobs using Maintenance plans can be found in this article

First open SSMS. Expand the node for the server you are using, after that expand the Management folder, right click on Maintenance plans and select Maintenance Plan Wizard

In the Welcome page we press Next and land on Select Plan Properties page where we input the plan name, optionally a description, under Run as we choose to run the plan as SQL Server Agent service account. Next we choose will the each task have separate schedule or will they all run at once. On Change… we can setup the schedule for the plan, after that click on Next

In the Select Maintenance Tasks page we select the tasks we want to perform. In this case we will select Back Up Database (Full) and click on Next

In the Select Maintenance Task Order page, since we have only one task, there is no order of tasks to make, so we click on Next to proceed to the following page

In the Define Back Up Database (Full) Task page, under General, Destination and Options tabs we can configure the task more in depth. After that, click on Next

In the Select Report Options, we choose whether we would like to have a text file report of the maintenance plan actions written, as well as setting up the email notification for backup job we are creating. In the To: dropdown menu, we select which profile to use for email reports. Since we have only one profile created, we choose that one for the reports. Afterwards, click on Next

In the Complete the Wizard page, we review the plan, if anything is not as intended, we can go back to change that. In case everything is as intended, press Finish to create the plan

After pressing Finish, the plan will be created and we are going to be presented with an overview of completed tasks. Click on Close to finish the Maintenance Plan Wizard

Setting up email notifications using ApexSQL Backup

ApexSQL Backup is a 3rd party tool for creating, managing and monitoring backup, restore and various maintenance jobs. ApexSQL Backup’s wizards help in creation of jobs in a few intuitive steps, as well as providing valuable monitoring for all the created jobs

First of all, to be able to create email notifications for backup jobs in ApexSQL Backup, we must configure the email client. We can do that by starting ApexSQL Backup, going to Configuration tab and clicking on Email button. In the window that opens, we input the Email address, Password, SMTP server address, check the box near Use port and insert port number if there is need for the port, and depending on the server, check the box near Use SSL. After that we can optionally test it by clicking on Test configuration, typing in an email address to send test email to. If everything is setup properly you should get the following message

Note: Some email providers require from the users to manually enable for 3rd party applications the access to the email account. That needs to be done either from their own apps or at their site

After confirming that the email settings are correct, press OK

After configuring the email through which we are going to send email notifications for backup jobs in ApexSQL Backup, we need to create a backup job. This is done by going to Home tab and then clicking on Backup button

In the Backup wizard we create the schedules for our backup jobs. For a more in depth how to for creating backups in ApexSQL Backup we can read this article. For this article, we are going to select the Notification tab in the navigation pane to the left, and there check the boxes near Success and Failure, whether we would like email notifications for backup jobs that have succeeded, failed or for both cases. To add a new recipient email, press the Add button and in the Email window insert the email address and click OK

With the already added addresses, we can edit them by pressing the … button near the appropriate address. We could also delete them by pressing X. After adding the email addresses we wanted, and having completed the rest of the backup wizard configuration, we can click on OK to complete it. The window confirming the creation of the schedule will appear. After confirming that the creation was successful, click on Finish to close the window

After the job runs by schedule, we will get an email to the email address provided in the backup wizard upon completion of the job

The post How to set up email notifications for backup jobs in SQL Server appeared first on Solution center.