#Daily Tasks to be followed by SQL Server DBA

[globalmediacampaign]

Orchestrating database refreshes for Amazon RDS and Amazon Aurora

The database refresh process consists of recreating of a target database using a consistent data copy of a source database, usually done for test and development purposes. Fully-managed database solutions such as Amazon Relational Database Service (Amazon RDS) or Amazon Aurora make it incredibly easy to do that. However, database administrators may need to run some post-refresh activities such as data masking or password changes, or they may need to orchestrate multiple refreshes because they manage several databases, each of them with more than one environment. In some cases, refreshes have to be performed frequently, even daily. In this post, we describe the features of a serverless solution that you can use to perform database refresh operations at scale, with a higher level of automation. This solution can be deployed and tested using the instructions available in the GitHub repo. In the next section, we go over what you’re going to build. Potential use cases The solution described in this post enables you to do the following: Refresh an existing database (or create a new one) using one of the four options available: Latestpoint – The data is aligned to the latest point in time. torestorepoint – The data is aligned to a specified point in time. fromsnapshot – The data is aligned at the snapshot creation time. fast-cloning (only for Aurora) – The data is aligned to the latest point in time, but it’s cloned using the fast-cloning feature provided by Aurora. Refresh an existing encrypted database (or create a new one). A cross-account use case has the following considerations: The only options available are fromsnapshot or fast-cloning (only for Aurora). The AWS Key Management Service (AWS KMS) primary key (managed by the source account) must be manually shared with the target AWS account before launching the refresh. Perform a cross-account refresh of an existing database (or create a new one). As a pre-requisite, the source account has to share with the target account the Amazon RDS or Aurora snapshot or the source Aurora cluster, before launching the refresh process. Run post-refresh SQL scripts against the new refreshed database (only available for Amazon RDS for MariaDB, Amazon RDS for MySQL and Aurora MySQL) to perform the following: Clearing, masking, or modifying sensitive data coming from the source production database. Deleting unnecessary data or removing unnecessary objects coming from the source production database. Customize the solution by adding or removing steps to orchestrate operations for those applications that have different requirements, using the same state machine. Keep the history of all the database refresh operations of your applications, in order to answer questions such as: When has my database last been refreshed? Does my application have all its non-production databases refreshed? Is the refresh that I launched yesterday complete? Prerequisites The solution implemented focuses on a time-consuming administrative task that DBAs have to deal with: the database refresh. The process consists of recreating an existing database. Typically, this is a copy used for test and development purposes whose data has to be “refreshed”. You can use a backup or the last available image of the related production environment to refresh a database. The solution can also be applied to scenarios when you create a new environment from scratch. The process can involve additional steps to apply different settings or configurations to the new refreshed database. The following diagram illustrates the process. The backup can be either logical (a partial or full export the source dataset) or physical (a binary copy of the database, which can be full, incremental, whole, or partial). The solution described in this post allows you to use physical backups (Amazon RDS or Aurora snapshots) during the restore process or the Aurora cloning feature in order to copy your databases. Solution overview The solution uses several AWS services to orchestrate the refresh process: Amazon Aurora – A MySQL-and PostgreSQL-compatible relational database built for the cloud. The solution uses Aurora snapshots or the fast cloning feature to restore Aurora database instances. Restores are performed using APIs provided by RDS and Aurora. Amazon DynamoDB – A fully-managed key-value and document database that delivers single-digit millisecond performance at any scale. We use it to keep track of all the refresh operations run by this solution. Amazon Elastic Compute Cloud – Amazon EC2 provides secure, resizable compute capacity in the cloud. The solution uses it in conjunction with AWS Systems Manager to run SQL scripts against your restored databases. AWS Lambda – Lambda lets you run code without provisioning or managing servers. You pay only for the compute time you consume. Lambda functions are used to implement all the steps of a database refresh. AWS Step Functions – A serverless function orchestrator that makes it easy to sequence AWS Lambda functions and multiple AWS services into business-critical applications. This is the core service of the solution, used to orchestrate database refreshes. Amazon RDS – A fully managed relational database solution that provides you with six familiar databases. The solution uses Amazon RDS snapshots to restore RDS databases instances. Restores are performed using APIs provided by RDS and Auroras. Amazon Simple Notification Service – Amazon SNS is a fully managed messaging service for both systems-to-system and app-to-person communication. We use it to notify users about the completion of refresh operations. Amazon Simple Storage Service – Amazon S3 is an object storage service that offers industry-leading scalability, data availability, security and performance. We use it to store the SQL scripts that the solution allows you to run against new restored databases. AWS Secrets Manager – Secrets Manager helps you to securely encrypt, store, and retrieve credentials for your database and other services. We use it to manage the access credentials of the databases involved in your refreshes. AWS Systems Manager – Systems Manager organizes, monitors and automates management tasks on your AWS resources. With Systems Manager Run Command, you can optionally run SQL scripts stored on Amazon S3 against your restored databases. This solution may incur costs; check the pricing pages related to the services you’re using. Architecture The architecture of the solution proposed is shown in the following diagram. The pre-restore workflow has the following steps: The end user prepares the refresh file (described later in this post) by configuring which steps have to be performed (including the optional creation of a Secrets Manager secret). If necessary, the end user can also prepare SQL scripts, stored on Amazon S3, to run as post-refresh scripts.  The restore workflow has the following steps: The end user initiates the refresh process by starting the Step Functions state machine (the refresh process could be initiated automatically, if needed). The state machine manages each step of the database restore by invoking Lambda functions that are part of this solution  The post-restore workflow includes the following steps: When the restore is complete, the state machine runs the post-restore SQL scripts. It provides two options: The state machine can run the scripts, stored on Amazon S3, through a Lambda function. If configured, you can use Secrets Manager to store and manage the database credentials. The state machine can run the scripts, stored on Amazon S3, using an EC2 instance, through Systems Manager Run Command. The state machine uses a DynamoDB table to store information about the process and its status. The state machine notifies the end user about the process final status using Amazon SNS. Steps of a database refresh Before describing in more detail what the solution looks like and how it works, it’s important to understand at a high level the main steps that are part of a database refresh: A backup of the source database is created. If the target database already exists, it’s stopped or, in most of the cases, deleted. The target database is re-created through a database restore operation, using the backup from Step 1. Post-restore scripts are run against the new restored target database. The Step Functions state machine implemented for this solution is composed by several states; most of them are related to specific steps of a database refresh operation. In particular, some states are required only for Amazon RDS, others only for Aurora, and others are required for both. The following table lists the main steps related to a refresh of an RDS DB instance performed by our solution. Step # Step Name Description 1 delete-replicas Deletes the existing read replicas of the target database 2 stop-old-database Stops the existing target database 3 perform-restore Performs the restore 4 delete-old-database Deletes the old target database 5 rename-database Renames the new target database 6 fix-tags Updates the tags of the new target database 7 create-read-replicas Re-creates the read replicas previously deleted 8 change-admin-pwd Changes the admin password of the new target database 9 rotate-admin-pwd Rotates the admin password within the secret for the new target database 10 runscripts Runs SQL scripts against the new target database 11 update-dynamodb Updates a DynamoDB table with some information about the refresh completed 12 send-msg Sends an SNS notification (e-mail) about the completion of the refresh The following table lists the main steps related to a refresh of an Aurora cluster performed by our solution. Step # Step Name Description 1 delete-replicas Deletes the existing read replicas of the target database 2 perform-restore Performs the restore (it only creates the cluster) 3 create-instance Creates a new instance within the cluster restored at Step 2 4 delete-old-database Deletes the old target DB instance 5 delete-old-cluster Deletes the old target cluster 6 rename-cluster Renames the new target cluster 7 rename-database Renames the new target database 8 fix-tags Updates the tags of the new target database 9 create-read-replicas Re-creates the read replicas previously deleted 10 change-admin-pwd Changes the admin password of the new target database 11 rotate-admin-pwd Rotates the admin password within the secret for the new target database 12 runscripts Runs SQL scripts against the new target database 13 update-dynamodb Updates a DynamoDB table with some information about the refresh completed 14 send-msg Sends an SNS notification (e-mail) about the completion of the refresh The graphic representation of the Step Function state machine that contains all the states mentioned above is available on the GitHub repo. You can use it on RDS DB instances, Aurora clusters, or both. The job poller strategy One of the main challenges of implementing an orchestrator with serverless services is to manage their stateless nature. When a certain operation is performed by a Lambda function against a database, how can we know when the operation is complete? The job poller strategy is a good solution. The following image is an extract from the solution showing this mechanism: For most of the steps that are part of a database refresh, we implement the same strategy: Step Functions invokes a Lambda function that performs a certain operation (such as restore a database). Step Functions waits a certain number of seconds (you configure) using the state of “Wait”. Step Functions invokes a Lambda function that checks if the operation has completed (if the database has been restored and its status is “available”). Step Functions verifies the results of the previous check using the state type “Choice”. Step Functions goes to the next state if the operation has completed; otherwise it waits again (returns to Step 2). Configuring your database refresh The steps of the database refresh are orchestrated by a Step Functions state machine based on an input file provided – the “refresh file”. It’s a JSON document containing all the input parameters for the state machine (in particular for the Lambda functions associated to the state machine states) which determines the characteristics of the refresh. A refresh file contains information about a specific refresh, so ideally for a single production database with two different non-production environments (one for development and one for test), a DBA has to prepare two refresh files. After these files are defined, they’re ready to be used and the related refresh can be scheduled or automated. The following code is the high-level structure of a refresh file: { "comment": "", "": { "": "", "": "", "": "", [..] "wait_time": , "check": { "": "", "": "", "checktodo": "", "torun": "true|false" }, "torun": "true|false" }, "": { "": "", "": "", "": "", [..] "wait_time": , "torun": "true|false" }, [..] } The file contains an element for every state machine’s state that needs an input. For more information about defining it, see the GitHub repo. Keep in mind the following about the refresh file: Not all the elements are required; some of them are related to steps that you may not want to run during a database refresh. Some elements are only related to Amazon RDS, others only to Aurora. Each element has a “torun” attribute that you can set to “false” in case you want to skip the related step. Each element has a “wait_time” attribute that determines for how long the state machine has to wait before checking whether the related operation or step is complete. Some elements have a “check” section that contains the input parameters for the Lambda function that verify whether a certain step completed successfully. This section has a “torun” parameter as well. Within an element, some parameters are required and others are optional. Within an element, some parameters are related to each other; if one has a value, the other one will become is also required. In this post, we show you three examples of elements related to three different steps of a database refresh. The following code shows a refresh of an RDS DB instance to the latest point: [..] "restore": { "dbservice": "rds", "source": "mysqlinstp", "target": "mysqlinstd", "restoretype": "latestpoint", "application": "app1", "environment": "development", "port": 3307, "subgrp": "db-sub-grp-virginia", "iamdbauth": "False", "cwalogs": "audit,error,general,slowquery", "copytagstosnap": "True", "dbparamgrp": "default.mysql5.7", "deletionprotection": "False", "secgrpids": "sg-03aa3aa1590daa4d8", "multiaz": "False", "dbname": "mysqlinstd", "dbclass": "db.t3.micro", "autominor": "False", "storagetype": "gp2", "wait_time": 60, "check": { "dbservice": "rds", "dbinstance": "mysqlinstdtemp", "checktodo": "checkrestore", "torun": "true" }, "torun": "true" } [..] The preceding section of the refresh file indicates that an RDS for MySQL DB instance “mysqlinstp” must be used as the source for the refresh to the latest point of the DB instance “mysqlinstd”. The section includes other information about the new database to be restored, including the security group ID, the storage type, the DB instance class. The state machine verifies every 60 seconds whether the restore operation is complete. In the “check” section, you can notice that a database is always restored with a name ending with the suffix “%temp”. This suffix is removed later with another step.  The following code illustrates how to rename an RDS for MySQL DB instance once restored: [..] "rename": { "dbservice": "rds", "dbinstance": "mysqlinstdtemp", "wait_time": 10, "check": { "dbservice": "rds", "dbinstance": "mysqlinstd", "checktodo": "checkrename", "torun": "true" }, "torun": "true" } [..] The preceding section of the refresh file indicates that the new restored RDS DB instance “mysqlinstdtemp” must be renamed to “mysqlinstd”. The state machine verifies every 10 seconds whether rename operation is complete. The following code runs post-refresh SQL scripts against a new restored RDS DB instance: [..] "runscripts": { "dbservice": "rds", "dbinstance": "mysqlinstd", "engine": "mysql", "access": "secret", "secretname": "/development/app1r/mysqlinstd", "method": "lambda", "bucketname": "awsolproj", "prefix": "rdsmysql/mysqlinstd", "keys": "00test.sql,01test.sql", "wait_time": 10, "check": { "dbservice": "rds", "bucketname": "awsolproj", "prefix": "rdsmysql/mysqlinstd", "checktodo": "runscripts", "torun": "true" }, "torun": "true" } [..] The preceding section of the refresh file indicates that the scripts “00test.sql” and “01test.sql” stored on Amazon S3 in the bucket “awsolproj” must be run through Lambda against the RDS for MySQL DB instance “mysqlinstd”. Database credentials are retrieved using Secrets Manager, and the status of the operation is verified every 10 seconds. Managing secrets At the end of the restore, the new database has the same passwords for all the users within the database, including the primary user. This situation could represent a problem from a security standpoint, and for this reason the Step Functions state machine includes the following two states: change-admin-pwd and rotate-admin-pwd. With change-admin-pwd, the password of the primary user is automatically changed with a new one specified in the refresh file. If a Secrets Manager secret is configured for that database, the secret can be synchronized as well. See the following code: [..] "changeadminpwd": { "dbservice": "rds", "dbinstance": "mysqlinstd", "temppwd": "temppwd123", "secret": "true", "secretname": "/development/app1/mysqlinstd", "wait_time": 15, "check": { "dbservice": "rds", "dbinstance": "mysqlinstd", "checktodo": "checkpwd", "torun": "true" }, "torun": "true" } [..] With rotate-admin-pwd, if a Secrets Manager secret is configured and it has the rotation settings enabled, the secret containing the primary user password is rotated: "rotateadminpwd": { "dbservice": "rds", "dbinstance": "mybetainstd", "secretname": "/development/gamma/mybetainstd", "wait_time": 15, "check": { "dbservice": "rds", "secretname": "/development/gamma/mybetainstd", "temppwd": "temppwd123", "checktodo": "rotatepwd", "torun": "true" }, "torun": "true" } The solution allows you to run post-refresh SQL scripts in two ways: Using Lambda Using Systems Manager Run Command and EC2 The first option is more suitable if you’re more familiar with Lambda and want to keep the solution’s infrastructure completely serverless. Otherwise, DBAs who are used to directly managing SQL scripts on a server can easily manage them through Systems Manager: scripts are downloaded from Amazon S3 to the EC2 instance that is part of the solution and run from there. In both cases, you have to store the scripts on Amazon S3. The following code is the section of the refresh file related to the “run-script-” state: "runscripts": { "dbservice": "aurora|rds", "cluster": "", "dbinstance": "", "engine": "aurora-mysql|mysql|mariadb|oracle|aurora-postgresql|postgresql", "access": "pwd|secret", "temppwd": "", "secretname": "", "method": "lambda|ec2", "bucketname": "", "prefix": "/", "keys": ",,", "wait_time": , "check": { "dbservice": "aurora|rds", "bucketname": "", "prefix": "/", "checktodo": "runscripts", "torun": "true" }, "torun": "true" } Within a SQL script, you can run SELECT, DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language) statements. As of this writing, this feature is available only for MySQL-related databases (Amazon RDS for MySQL, Amazon RDS for MariaDB, and Aurora MySQL) Tracking and troubleshooting your database refresh Keeping track of database refreshes is important especially when you have to manage hundreds of production databases plus the related non-production ones. This solution uses an encrypted DynamoDB table to record information about databases refreshes, giving you the ability to quickly answer questions like the following: Which date is the data of this database aligned to? When was the last time we refreshed this database? From which source did this database copy? Did the refresh of this database run successfully yesterday? Considering the production database, what’s the status of the refreshes of its non-production databases? The current structure of the DynamoDB table is the following: Table name – dbalignement-awssol Partition key – dbinstance Sort key – restoredate Additional attributes – appname,environment,recordingtime,restoretype,snapshot,source,status As of this writing, the solution doesn’t provide any local secondary index (LSI) or global secondary index (GSI) for the table, but you can easily add new GSIs to increase the number of access patterns that can be satisfied based on your needs. If a database refresh fails for any reason, you can use different services to understand the reasons. You can easily monitor the runs of your state machines through the Step Functions API or through its dashboard. The graph inspector can immediately tell you at which state there was a failure or at which state the state machine got stuck. If you choose a state, you can also take a look at the related input and output. You can also monitor the output of the Lambda functions associated with the states of the solution. Lambda logs information about its runs in Amazon CloudWatch Logs, from which you can get more details about what happened during a certain operation. Get notified or verify the database refresh completion The solution uses Amazon SNS to send emails about the success or failure of the database refreshes performed. In case of success, some details about the database just refreshed are included in the message sent. The following code is the section of the refresh file related to the “sendmsg” state: "sendmsg": { "dbservice": "aurora|rds", "application": "", "environment": "", "dbinstance": "", "source": "", "restoretype": "fromsnapshot|restorepoint|latestpoint|fastcloning", "topicarn": "", "torun": "true|false" } This feature is optional. What’s next The solution could be improved in some aspects, especially in the submission of the information about the database refresh. As of this writing, the input to provide must be manually prepared, but in the future we’re thinking about providing a user interface through which you can create the related JSON files and immediately perform some pre-checks that can validate the information provided. Notifications are sent to users via Amazon SNS but another option could be to use Amazon Simple E-mail Service (Amazon SES) to get more detailed information about the refreshes performed by sending formatted e-mails with additional information attached about the new database just restored. As of this writing, the solution doesn’t support Amazon RDS for SQL Server, and running post-refresh SQL scripts is available only for MySQL-related engines. We’re working to include those features in the remaining engines. Conclusion In this post, we showed how you can automate database refresh operations using serverless technology. The solution described can help you increase the level of automation in your infrastructure; in particular it can help reduce the time spent for an important and critical maintenance activity such as database refreshes, allowing DBAs to focus more on what matters when they manage their Amazon RDS and Aurora databases on AWS. We’d love to hear what you think! If you have questions or suggestions, please leave a comment. About the Authors Paola Lorusso is a Specialist Database Solutions Architect based in Milan, Italy. She works with companies of all sizes to support their innovation initiatives in the database area. In her role she helps customers to discover database services and design solutions on AWS, based on data access patterns and business requirements. She brings her technical experience close to the customer supporting migration strategies and developing new solutions with Relational and NoSQL databases.   Marco Tamassia is a technical instructor based in Milan, Italy. He delivers a wide range of technical trainings to AWS customers across EMEA. He also collaborates in the creation of new courses such as “Planning & Designing Databases on AWS” and “AWS Certified Database – Specialty”. Marco has a deep background as a Database Administrator (DBA) for companies of all sizes (included AWS). This allows him to bring his database knowledge into classroom brining real world examples to his students.   https://aws.amazon.com/blogs/database/orchestrating-database-refreshes-for-amazon-rds-and-amazon-aurora/

[notsadrobotxyz]

DBA Daily/Weekly/Monthly or Quarterly Checklist

In response of some fresher DBA I am giving quick checklist for a production DBA. Here I am including reference of some of the script which I already posted as you know each DBA have its own scripts depending on database environment too. Please have a look on into daily, weekly and quarterly checklist. Note: I am not responsible of any of the script is harming your database so before using directly on Prod DB. Please check it on Test environment first and make sure then go for it.Please send your corrections, suggestions, and feedback to me. I may credit your contribution.  Thank you.------------------------------------------------------------------------------------------------------------------------Daily Checks:Verify all database, instances, Listener are up, every 30 Min. Verify the status of daily scheduled jobs/daily backups in the morning very first hour.Verify the success of archive log backups, based on the backup interval.Check the space usage of the archive log file system for both primary and standby DB. Check the space usage and verify all the tablespace usage is below critical level once in a day. Verify Rollback segments.Check the database performance, periodic basis usually in the morning very first hour after the night shift schedule backup has been completed.Check the sync between the primary database and standby database, every 20 min. Make a habit to check out the new alert.log entry hourly specially if getting any error.Check the system performance, periodic basis.Check for the invalid objectsCheck out the audit files for any suspicious activities. Identify bad growth projections.Clear the trace files in the udump and bdump directory as per the policy.Verify all the monitoring agent, including OEM agent and third party monitoring agents.Make a habit to read DBA Manual.Weekly Checks:Perform level 0 or cold backup as per the backup policy. Note the backup policy can be changed as per the requirement. Don’t forget to check out the space on disk or tape before performing level 0 or cold backup.Perform Export backups of important tables.Check the database statistics collection. On some databases this needs to be done every day depending upon the requirement.Approve or plan any scheduled changes for the week.Verify the schedule jobs and clear the output directory. You can also automate it.Look for the object that break rule. Look for security policy violation.      Archive the alert logs (if possible) to reference the similar kind of error in future. Visit the home page of key vendors.Monthly or Quarterly Checks:Verify the accuracy of backups by creating test databases.Checks for the critical patch updates from oracle make sure that your systems are in compliance with CPU patches.Checkout the harmful growth rate. Review Fragmentation. Look for I/O Contention. Perform Tuning and Database Maintenance.Verify the accuracy of the DR mechanism by performing a database switch over test. This can be done once in six months based on the business requirements.------------------------------------------------------------------------------------------------------------------------------------------------------- Below is the brief description about some of the important concept including important SQL scripts. You can find more scripts on my different post by using blog search option.Verify all instances are up: Make sure the database is available. Log into each instance and run daily reports or test scripts. You can also automate this procedure but it is better do it manually. Optional implementation: use Oracle Enterprise Manager's 'probe' event.Verify DBSNMP is running:Log on to each managed machine to check for the 'dbsnmp' process. For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP.Verify success of Daily Scheduled Job:Each morning one of your prime tasks is to check backup log, backup drive where your actual backup is stored to verify the night backup. Verify success of database archiving to tape or disk:In the next subsequent work check the location where daily archiving stored. Verify the archive backup on disk or tape.Verify enough resources for acceptable performance:For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of , the minimum free space for : . When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least days’ data growth. Go to each instance, run query to check free mb in tablespaces/datafiles. Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct it.Verify rollback segment:Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE. Optional: each database may have a list of rollback segment names and their expected statuses.For current status of each ONLINE or FULL rollback segment (by ID not by name), query on V$ROLLSTAT. For storage parameters and names of ALL rollback segment, query on DBA_ROLLBACK_SEGS. That view’s STATUS field is less accurate than V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses, showing these as OFFLINE and ONLINE respectively.Look for any new alert log entries:Connect to each managed system. Use 'telnet' or comparable program. For each managed instance, go to the background dump destination, usually $ORACLE_BASE//bdump. Make sure to look under each managed database's SID. At the prompt, use the Unix ‘tail’ command to see the alert_.log, or otherwise examine the most recent entries in the file. If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in .Identify bad growth projections.Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, upgrade the max_extents to unlimited. For that run query to gather daily sizing information, check current extents, current table sizing information, current index sizing information and find growth trendsIdentify space-bound objects:Space-bound objects’ next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE COALESCE. Or add another datafile. Run spacebound.sql. If all is well, zero rows will be returned.Processes to review contention for CPU, memory, network or disk resources:To check CPU utilization, go to =>system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.Make a habit to Read DBA Manual:Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readingsshould include DBA manuals, trade journals, and possibly newsgroups or mailing lists.Look for objects that break rules:For each object-creation policy (naming convention, storage parameters, etc.) have an automated check to verify that the policy is being followed. Every object in a given tablespace should have the exact same size for NEXT_EXTENT, which should match the tablespace default for NEXT_EXTENT. As of 10/03/2012, default NEXT_EXTENT for DATAHI is 1 gig (1048576 bytes), DATALO is 500 mb (524288 bytes), and INDEXES is 256 mb (262144 bytes). To check settings for NEXT_EXTENT, run nextext.sql. To check existing extents, run existext.sqlAll tables should have unique primary keys:To check missing PK, run no_pk.sql. To check disabled PK, run disPK.sql. All primary key indexes should be unique. Run nonuPK.sql to check. All indexes should use INDEXES tablespace. Run mkrebuild_idx.sql. Schemas should look identical between environments, especially test and production. To check data type consistency, run datatype.sql. To check other object consistency, run obj_coord.sql.Look for security policy violations:Look in SQL*Net logs for errors, issues, Client side logs, Server side logs and Archive all Alert Logs to historyVisit home pages of key vendors:For new update information made a habit to visit home pages of key vendors such as: Oracle Corporation: http://www.oracle.com, http://technet.oracle.com, http://www.oracle.com/support, http://www.oramag.com Quest Software: http://www.quests.comSun Microsystems: http://www.sun.com Look for Harmful Growth Rates:Review changes in segment growth when compared to previous reports to identify segments with a harmful growth rate. Review Tuning Opportunities and Perform Tuning Maintainance:Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management. Compare with past reports to identify harmful trends or determine impact of recent tuning adjustments. Make the adjustments necessary to avoid contention for system resources. This may include scheduled down time or request for additional resources.Look for I/O Contention:Review database file activity. Compare to past output to identify trends that could lead to possible contention.Review Fragmentation:Investigate fragmentation (e.g. row chaining, etc.), Project Performance into the FutureCompare reports on CPU, memory, network, and disk utilization from both Oracle and the operating system to identify trends that could lead to contention for any one of these resources in the near future. Compare performance trends to Service Level Agreement to see when the system will go out of bounds. -------------------------------------------------------------------------------------------- Useful Scripts: -------------------------------------------------------------------------------------------- Script: To check free, pct_free, and allocated space within a tablespace SELECT tablespace_name, largest_free_chunk, nr_free_chunks, sum_alloc_blocks, sum_free_blocks , to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%' AS pct_free FROM ( SELECT tablespace_name, sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name), ( SELECT tablespace_name AS fs_ts_name, max(blocks) AS largest_free_chunk , count(blocks) AS nr_free_chunks, sum(blocks) AS sum_free_blocks FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = fs_ts_name; Script: To analyze tables and indexes BEGIN dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ; END ; Script: To find out any object reaching SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents , to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB FROM dba_extents e , dba_segments s WHERE e.segment_name = s.segment_name GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents HAVING count(*) > &THRESHOLD OR ( ( s.max_extents - count(*) ) < &&THRESHOLD ) ORDER BY count(*) desc; The above query will find out any object reaching level extents, and then you have to manually upgrade it to allow unlimited max_extents (thus only objects we expect to be big are allowed to become big. Script: To identify space-bound objects. If all is well, no rows are returned. SELECT a.table_name, a.next_extent, a.tablespace_name FROM all_tables a,( SELECT tablespace_name, max(bytes) as big_chunk FROM dba_free_space GROUP BY tablespace_name ) f WHERE f.tablespace_name = a.tablespace_name AND a.next_extent > f.big_chunk; Run the above query to find the space bound object . If all is well no rows are returned  if found something then look at the value of next extent. Check to find out what happened  then use coalesce (alter tablespace coalesce;). and finally, add another datafile to the tablespace if needed. Script: To find tables that don't match the tablespace default for NEXT extent. SELECT segment_name, segment_type, ds.next_extent as Actual_Next , dt.tablespace_name, dt.next_extent as Default_Next FROM dba_tablespaces dt, dba_segments ds WHERE dt.tablespace_name = ds.tablespace_name AND dt.next_extent !=ds.next_extent AND ds.owner = UPPER ( '&OWNER' ) ORDER BY tablespace_name, segment_type, segment_name; Script: To check existing extents SELECT segment_name, segment_type, count(*) as nr_exts , sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts , dt.tablespace_name, dt.next_extent as dflt_ext_size FROM dba_tablespaces dt, dba_extents dx WHERE dt.tablespace_name = dx.tablespace_name AND dx.owner = '&OWNER' GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent; The above query will find how many of each object's extents differ in size from the tablespace's default size. If it shows a lot of different sized extents, your free space is likely to become fragmented. If so, need to reorganize this tablespace. Script: To find tables without PK constraint SELECT table_name FROM all_tables WHERE owner = '&OWNER' MINUS SELECT table_name FROM all_constraints WHERE owner = '&&OWNER' AND constraint_type = 'P'; Script: To find out which primary keys are disabled SELECT owner, constraint_name, table_name, status FROM all_constraints WHERE owner = '&OWNER' AND status = 'DISABLED' AND constraint_type = 'P'; Script: To find tables with nonunique PK indexes. SELECT index_name, table_name, uniqueness FROM all_indexes WHERE index_name like '&PKNAME%' AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE' SELECT c.constraint_name, i.tablespace_name, i.uniqueness FROM all_constraints c , all_indexes i WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE' AND c.constraint_type = 'P' AND i.index_name = c.constraint_name; Script: To check datatype consistency between two environments SELECT table_name, column_name, data_type, data_length,data_precision,data_scale,nullable FROM all_tab_columns -- first environment WHERE owner = '&OWNER' MINUS SELECT table_name,column_name,data_type,data_length,data_precision,data_scale,nullable FROM all_tab_columns@&my_db_link -- second environment WHERE owner = '&OWNER2' order by table_name, column_name; Script: To find out any difference in objects between two instances SELECT object_name, object_type FROM user_objects MINUS SELECT object_name, object_type FROM user_objects@&my_db_link; For more about script and Daily DBA Task or Monitoring use the search concept to check my other post. Follow the below link for important Monitoring Script: http://shahiddba.blogspot.com/2012/04/oracle-dba-daily-checklist.html

[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]

SQL Server Management Studio (SSMS) is an integrated environment for managing SQL Server instances. By using SSMS, one could access, configure, manage, administer, and develop all components of SQL Server.

As official and the most recognizable “all in one” client user interface, its design is quite user friendly and comprehensive. Furthermore, the software combines and brings together a range of both graphical and visual design tools and rich script editors to ensure simplified interaction with SQL Server.

On top of all, it’s worth mentioning that with every major release, the application is better in terms of security and performance.

And to any database administrator (DBA) productivity and versatility may prove to be very important. Productivity will allow the job to be done easily and swiftly, and versatility ensures different ways of accomplishing one single task.

However, with all the mentioned perks, there are some downsides to its functionality, that are time-consuming and sometimes very tedious.

One of which, that SSMS clearly lacks the possibility of adding multiple SQL Server instances at once.

Understanding the problem

Don’t think that this is an overblown statement. Just imagine large corporations with a huge amount of SQL Servers, we’re talking hundreds here, both local and scattered around the network waiting to be discovered and used.

And now, here is a person that needs to do a simple task like pulling the build numbers for all SQL instances that are currently managed. First and foremost, the connection between every single one of those instances needs to be established in order to use and manage them. To connect them via the SSMS, every SQL Server instance needs to be manually added. Just imagine hundreds of instances that need to be added one-by-one. It is clearly an exhausting task, without any exaggeration.

In this case, the performance and productiveness of a database administrator could take a massive hit, and to some, this may end up being a dealbreaker, to others the necessary hassle.

Another thing that needs to be mentioned here is that once the SSMS is closed or restarted, on the new application launch, the connection to previously managed instances is lost, and those needs to be added again, manually.

In this article, we will take a look into the process of adding SQL Server instance via SSMS and then provide an insight into the different ways of accomplishing the same by using the 3rd party alternative, ApexSQL Manage.

With all being said, let’s dive into the process of connecting a SQL instance in the SQL Server Management Studio.

Connect SQL instance in SSMS

To connect a SQL instance in SSMS, one should do the following:

Launch the SQL Server Management Studio

Once the software is initialized, by default, the Connect to Server window will pop-up:

Here, configure the following:

From the Server type drop-down list bellow can be selected:

Database Engine – the core component of SQL Server instance used for storing, processing and securing

Analysis Services – SSAS is a multidimensional analysis tool that features Online Analytical Processing (OLAP), powerful data mining abilities and reporting used in Business Intelligence to ease your managing data tasks

Reporting Services – SSRS is a reporting and visualization tool for SQL Server, and by using it, one can create, manage and publish reports and dashboards

Integration Services – SSIS is a fast and flexible data warehousing tool that can be used for data extraction and for executing a wide range of data migration tasks

Azure-SSIS Integration Runtime – the Integration Runtime (IR) is the compute infrastructure used by Azure Data Factory to provide the data integration capabilities across different network environments

This is where the Database Engine option should be chosen.

In the Server name drop-down list, choose the preferred SQL Server instance to make a connection with. Here, the previously used SQL instances are listed:

If the desired SQL instance is not listed, choose the Browse for more… option at the bottom of the drop-down list:

Under the Local Servers tab of the Browse for Servers window, displayed are all SQL instances installed on the local machine. In addition to this, under the same tab, all configured SQL Server Services are listed as well:

In the Network Servers tab, SQL instances discovered across the network are shown:

Once the SQL Server instance is selected, proceed to choose the Authentication mode. In its drop-down list the following options are displayed, and the majority of those with their own set of configurable settings:

Windows Authentication – this mode allows a user to connect through a Windows user account

SQL Server Authentication – anytime this mode is used, proper login and password credentials are required

Azure Active Directory – Universal with MFA – this mode delivers strong authentication with a range of different verification options the user can choose from: phone call, text message, smart cards with a pin or mobile app notifications

Azure Active Directory – Password – use this mode for connecting to SQL Server instance database and when logged in to Windows using Azure AD credentials from a domain not combined with Azure

Azure Active Directory – Integrated – this mode is used when connecting to SQL Database and logged in to Windows using Azure Active Directory credentials from a federated domain

After the proper authentication is chosen, click the Connect button to establish the connection between the SQL Server Management Studio and the selected SQL Server instance:

Now, the SQL instance is shown in the Object Explorer on the left with all its properties and objects:

To view the build number for this newly connected instance, right-click on its name and choose Properties:

Build number details are now listed in the General tab of the Server Properties window:

To add another SQL instance and view its build number, click the Connect drop-down menu and choose the Database Engine option:

In the Connect to Server window, configure the same settings based on the previously described steps:

Once a new instance is added, repeat the same steps to see its build number.

And this set of actions will have to be repeated numerous times in order to complete one simple task.

Using 3rd party software

ApexSQL Manage is a powerful application used for managing SQL instances. Some of its core features and capabilities ensure the user can easily discover instances across the network, execute high-level analysis in the search for SQL Server health issues, capture real-time state and condition of SQL Server instance by creating its snapshot, to make a comparison between instances and snapshots as well as to document them in various file formats.

In this section, the focus will be on the versatility mentioned in the introduction part of this article.

In that regard, the SQL manage instance tool offers a few more options when it comes to adding SQL Servers and making connections to the preferred SQL instance and the application itself. Also, it offers the “welcomed” capability of easily adding multiple SQL instances at the same time.

Following are the mentioned options:

Add SQL Instances manually – like in SSMS, SQL Server instances could be added manually and only one at a time

Add SQL instances discovered on a network – configure a search setting that will ensure scan will be executed on the specific area of network

Add SQL instances from the last scan – no new scan is required since the SQL manage instance tool remembers the results from the last scan being performed

Add SQL instances automatically – a scan can be automated and set to be executed in the desired time and frequency

Add SQL instances with quick action option – execute quick scan based on the previously configured settings

Note: For more information on this topic, please visit the Different ways of adding SQL Server instances in a SQL manage instance software article.

Completion of the example task will now look quite different, i.e., it can be done in just a couple of steps. The first step is to choose the preferred method for adding SQL instances, and with the connection between them and SQL manage instance software is established, they will be listed in the main grid of the Inventory tab. Their build versions will be displayed in the Build number column.

The second and final action would be to just read out build versions from the column. It is quite convenient that all build versions of connected instances can be seen at once:

Conclusion

When in need to manage multiple SQL instances at the same time, one doesn’t have to add them one-by-one to perform certain management tasks since there is a solution to achieve this feat instantly and quickly. This will save some valuable hours from daily work chores, helping up in reducing some pressure and, to an extent, increasing one’s productivity.

[marcosplavsczyk]

Daily obligations in a DBA life can be time-consuming, depending on the type of tasks needed to maintain a SQL Server. Problems that can occur range from SQL Server instance configuration to the actual databases it is facilitating. Allowing these issues to happen and persist can interfere with the overall performance of an environment and take away much needed time that can be spent differently.

ApexSQL Manage is a SQL Server instance management tool that can find and resolve potential problems in a SQL Server environment by performing regular health checks of SQL Server instances as well as discovering SQL Server instances, create instance snapshots and comparison between live SQL Server or snapshots and document SQL Server configuration.

Key areas, where potential issues with database configurations can interfere with SQL Server performance, and ApexSQL Manage can help are as following:

Full Recovery Mode issues

Database AUTO_CLOSE issues

Database AUTO_SHRINK issues

Database auto growth

Database capacity

Database compatibility value

Database collation

Databases without owners

Database files

Database Virtual log file number value

Full Recovery Mode issues

SQL Server’s Full Recovery Model enables you to recover to a point-in-time and provided complete protection against media failure or disaster incidents. To have a viable disaster recovery plan, it is recommended to set all production databases to full recovery mode. This will ensure a healthy chain of database and transaction log backups. Executing this rule will determine if there are databases on a SQL Server instance that are not set to Full recovery mode and offer a FixSQL script to immediately resolve this issue.

To resolve this issue, check the desired rule from the main grid and click on the FixSQL button of the main ribbon. The tool will instantly analyze which databases are in violation of the rule and generate a TSQL script to correct the issue. Click on the Execute button in the FixSQL window to run the query:

Database AUTO_CLOSE issues

When AUTO_CLOSE is set to ON, the database is closed when the last user disconnects from the database. This can hinder performance on high traffic production database since AUTO_CLOSE flushes procedure cache after each connection and increases overhead on the SQL Server instance when the database is repeatedly opened and closed. Best practice for production environments where a database is accessed regularly by different users is to set AUTO_CLOSE to OFF.

To determine if a database has AUTO_CLOSE set to ON, simply run a database health check on that SQL Server and check if the result of the rule is Failed. If the rule is not in compliance with best practices, check the rule and click on the FixSQL button to create a FixSQL script that will resolve the issue. The created script will be shown in the FixSQL window. To execute the script, click on the Execute button:

Database AUTO_SHRINK issues

Unless necessary, do not set the AUTO_SHRINK option to ON. While this is a tempting option that can save up valuable disk space, it has a drastic set of drawbacks, which in the majority of cases outweigh the positives. Setting AUTO_SHRINK to ON can lead to massive database fragmentation since SQL Server is constantly moving the last page in the database to the first available space. Repeating this process every time will put the pages out of order. Additionally, the read and write operations can cause performance issues on the SQL Server instance.

To determine if a database has AUTO_SHRINK set to ON, simply repeat the steps mentioned above, run a database health check, check if the result of the rule is Failed, and click on the FixSQL button from the main ribbon to fix the issue. Once the FixSQL window is opened it will show which databases are affected, click on the Execute button to run the query:

Database auto growth

Log file(s) and data file(s) must increase size with constant value . Set the FILEGROWTH of your database’s transaction log and data files to use memory units instead of a percentage. Setting the log and data file(s) to grow by a percentage can, through time, result in larger growth increments. This can cause the growth operation to slow down, which in turn can lead to slow database performance. Additionally, Auto Growth is a delicate topic, and best practices depend on the user environment. For example, if a database grows 1GB per day, and Auto Growth is set to 100MB, SQL Server will trigger 10 Auto Growth events, which will drastically impact performance on that SQL Server instance. It is important to monitor the database growth and quantity of inserts to determine the best value for Auto Growth.

To set the proper auto-growth run a database health check, check if the result of the rule is Failed, and to fix the issue, click on the FixSQL button from the main ribbon. Once the FixSQL window is opened, it will show the SQL code, which can be further modified to fit the needs of the user environment. Click on the Execute button to run the query:

Database capacity

This rule demonstrates how to check if the data and log file grow too close to full capacity and how to generate a FixSQL query for this issue. The best practice is to grow a database during maintenance periods, preferably when the database is offline. This will provide adequate time to add enough space and analyze how much space will be needed for the next maintenance period.

To check if database capacity is reaching near full, run a database health check. If the result of the database capacity rule is Fail, check the rule and click on the FixSQL button from the main ribbon. Presented FixSQL script will show the TSQL code, which can be used to grow the database. By default, the growth increment will be around 50%. This can further be modified to fit the needs of the user environment. Click on the Execute button to run the query:

Database compatibility value

Sometimes a database has been restored with a different compatibility value and hasn’t been changed since. To avoid potential performance issues where queries can take longer to execute, it is best practice to check the database compatibility with the compatibility specified version of the SQL Server and change it accordingly. Running this rule will determine which databases on the selected SQL Server instance have a mismatch of compatibility levels and provide a FixSQL to resolve the issue:

Database collation

Differences in database collation and SQL Server collation can cause issues when joining between different databases. To avoid such issues, best practice is to keep the same collation across databases and SQL Servers. Executing this rule will compare the database collations to SQL Server collation and create a FixSQL script with databases that have different collations from the SQL Server instance.

Note: In some cases, this difference is a deliberate configuration of the user environment, and executing this FixSQL script will not provide any benefit.

Databases without owners

This issue can occur when the defined owner, a Windows login, has been removed from the Active Directory group or the local machine. Now, the SQL Server instance has an unknown value, and the database has no owner. To resolve this issue, best practice is to assign a SQL Server Login dedicated to that database. Running this rule will provide a FixSQL with databases which do not have an owner and recommend to set system administrator (sa) as the owner. If the sa login is not considered the best fit for the database owner, it is up to the user preference to assign the appropriate owner:

Database files

It is considered best practice to keep the database files and transaction logs on separate drives. In case of disaster on the primary drive, all database files and logs will be lost, and data recovery will not be a viable option. For this rule, there is no FixSQL script since this is not possible with SQL code alone. Instead, the Violation tab offers advice on how to proceed in this scenario:

When a new database is created, it is recommended to specify separate drives for the data and logs. To move the files after the database is created, the database must be taken offline. Following methods can be used to successfully relocate data and log files:

Restore the database from backup by using the RESTORE DATABASE statement with the WITH MOVE option

Detach and then attach the database specifying separate locations for the data and log devices

Specify a new location by running the ALTER DATABASE statement with the MODIFY FILE option, and then restart the SQL Server instance

Database Virtual log file number value

Virtual log files are used by SQL Servers to internally manage the log file. They are separated into smaller files that contain the log data records. Whenever there is a need for new free space, or the active virtual logs are full, a new one is created. Too many virtual log files can cause transaction log backups to slow down and can additionally slow down database recovery. In extreme cases, this can lead to issues on the SQL Server instance that can affect insert/update/delete performance:

Conclusion

To summarize, executing health check analysis for database category using ApexSQL Manage can provide an easier overview of potential issues in managed SQL environments as well as solutions on how to resolve them and maintain a healthy SQL Server. By using this SQL Server instance management tool for regular analysis and best practice enforcement, issues can be detected before they cause major performance impact.

[marcosplavsczyk]

There are many SQL Server auditing solutions available on the market since the audit requirements are building more and more. Over time, we can see the increased number of both internal and external compliance standards which impose numerous auditing requirements for different business areas and models. More often than before, companies have to ensure they comply with two, three or even more SQL Server auditing standards at once, making it paramount to equip themselves with powerful and versatile auditing solutions that will allow many different features and ensure compliance with present requirements.

In this article, we are going to discuss several SQL Server auditing features and requirements that are important to access when searching for the best-suited SQL Server auditing solution for your environment.

Auditing features

Auditing mechanisms and capabilities

Naturally, the most important features of any SQL Server auditing solution are strong and capable auditing mechanisms the tool relies on which offer the following:

Low/minimal performance impact

Reliability

Low/minimal intrusion to production servers

Variety

In our example, let’s look at ApexSQL Audit, a SQL Server auditing tool capable of auditing almost 200 SQL Server events across numerous SQL Server instances within the domain, using different mechanisms:

SQL Traces – available on all audited SQL Server instances starting with 2005 onwards

Extended Events – available for SQL 2012 and higher

SQL Audit – for SQL 2017 and higher

While SQL Traces are available on all supported SQL Server versions, they are most robust and have the highest performance impact between the 3. While this performance impact will not be shown for low or medium performance environments, it can become a thing to look more carefully in high or extreme performance environments where every bit of performance degradation can make a significant impact. Note that, in general, SQL Traces are practically minimal processing impact to the SQL Server itself, but in very large quantities, some performance impacts can be identified. Extended Events and SQL Audit technologies offer better performance since they are ‘cheaper’ to process on the SQL Server itself, but these technologies are not available on older SQL Server versions.

The second part of the “mechanisms and capabilities” section is reserved for auditing of before-after values on the table/column level. In many different compliance requirements, it is essential to audit actual values of the table field before and after the change, and to be able to identify who made the change, from where, how, and more – the essential requirement remains to see the “original” value and the “new” value on all DML operations (Insert, Update and Delete).

Again, in ApexSQL Audit, we can see how it leverages database triggers which fire on each insert, update or delete operations (based on the configuration set by the user) in order to capture values before and after the change as long as appropriate details on the executed operation:

One additional note when looking at configuration capabilities of the SQL Server auditing tool is granularity and precision. A solid tool must support both plug-and-play auditing configuration with only minimal user input requirement as well as advanced filtering capabilities which allow the highest precision and granularity filters which will allow configuring auditing in such way that some/all special cases can be included or excluded from auditing, like this:

What can (needs to) be audited

As mentioned above, it is important to be able to audit many different SQL Server events required by different compliance or internal standards. So, when choosing a SQL Server auditing solution, you should be first aware of which exact events must be audited in order to check if the proposed solution can indeed meet these requirements.

Looking at compliance standards like HIPAA, PCI, GDPR, SOX and more, here are the most required evet categories for auditing on both internal or external requirements:

DDL operations (data changes: Insert, Update, Delete…)

DML operations (schema changes: Create, Alter, Drop…)

Queries (data access: Select, Select Into…)

Security events (permission changes, login events…)

Backup/Restore tasks (database backup and restore info)

Execute tasks (transaction executions)

Warnings (different SQL Server warnings)

Errors (different SQL Server errors)

In the Audited events in ApexSQL Audit article, we can see a solid list of almost 200 SQL events that can be audited, covering all important categories and major events required by all current compliance and frequent internal auditing standards and requirements.

Data protection and safety

Now that the data is audited, it is paramount to ensure that data is safely stored and that any attempts to tamper with the data will be reported on and that the perpetrator can be identified to prevent any future malicious or inadvertent attempts to change the audited data or details.

Some of the prime features regarding data safety to look for are:

Tamper-evident environment – see how ApexSQL Audit tackles this requirement in the ApexSQL Audit Tamper-evident design features article

Date encryption on both repository database as well as during the data transfer from/to the central repository

Configurable user access to the GUI and data – How to allow or deny certain user access to the ApexSQL Audit GUI is an example of how ApexSQL Audit handles user access

Reporting

Reporting on the audited data must be on the same level as gathering/auditing the data itself. A solid SQL Server auditing solution must have a plethora of built-in reports, ready to run in a couple of clicks which cover all important auditing categories mentioned above. Additionally, users must be able to create custom reports to fulfill their simple or complex requirements to submit different reports:

Furthermore, it is strongly recommended to have a scheduler within the tool features that can create predefined reports on a configured time schedule and even have them delivered via mail to specific users or teams handling auditing and reporting.

Alerting mechanisms

Continuity of auditing is one of the job essentials. With huge penalties (and paperwork as well) imposed on auditing interruptions or gaps, it is important to have strong alerting policies in the SQL Server auditing tool which can alert specific users/groups on events that can endanger the auditing itself, such are connection issues, permission issues, technical issues with production servers (like disk space) and more.

More importantly, some auditing requirements make it mandatory to alert DBAs or engineering departments on important SQL Server events that can compromise schema or data and endanger the whole system. With this in mind, it is very important to have both built-in alerts which will notify/alert on events that can endanger auditing operation, as well as ability to create custom alerts which will raise an alarm immediately on specific/security events such are unauthorized login attempts, deletion of specific schema, structure or data, access to ‘confidential’ data by unexpected logins, connections from unauthorized hosts and more.

In the How to create a custom alert with an email notification article, we can see how ApexSQL Audit solves these concerns by offering several critical built-in alerts as well as allowing users to place an unlimited number of highly-precise alerts which will alert specific users/groups on any unexpected or critical events regarding auditing and security

General features

Ease of use

Often a neglected concept, ease of use is an overall important feature for all auditors because in most cases, there are no specific “auditing engineer” positions where a single person or a whole team will be dedicated and work on the position which is focused only on auditing – in numerous cases, DBAs or engineers in similar positions are those which must implement and maintain auditing solution of choice.

With this in mind and all other daily tasks their position in the company dictates they must take care of, it is very important for the SQL Server auditing solution to be simple, easy to learn and use, have excessive and complete documentation and guides and transparent roadmap for future development. While having many useful features and options is always good, having a rave-party of options, icons and commands can heavily diminish usability of the tool and require harsh time investments to complete even simplest of jobs – so, having a well-organized GUI with plethora of options sorted in a well-oriented manner can make your life much easier.

Repository maintenance

Auditing jobs, in most cases, result in vast quantities of audited data. This means that SQL Server auditing solution must come with good and easy to use maintenance mechanisms which will allow users to operate with the repository and data within to ensure that there is no hindering to the auditing job, that storing and working with the historical data is easy, and that data that is no longer needed can safely and quickly be removed and deleted

Check out these two handy features for repository maintenance in ApexSQL Audit:

Repository maintenance options

Schedule archiving in ApexSQL Audit

Bonus features

Support plan and assistance

More often than not, help or assistance of dedicated support specialists may be required or even paramount in critical situations. While this is not technically a feature for SQL Server auditing solution, it is very important to ensure that the solution of your choice has a solid team behind it with capable support engineers and developers behind which can jump in and assist on short notice with strong expertise and knowledge, especially in production-critical events.

Proof of concept

With the lack of auditing specialist position in many modern companies, personnel in charge of auditing has a tough job of getting to know the leading solution on the market in short and steep time frames. While researching online documentation can give a solid overall insight into the solution itself, achieving a strong proof of concept is an entirely different experience. With this in mind, it is recommended to trial out the solution in development or even production environment and see how the tool behaves as well as all the fruits (including rotten ones) of its continuous labor. The advantage here is given to the solutions which:

Are easy to install and configure

Have at least 14-day trial (with the possibility to extend the duration when needed)

Have no limitations on any of the tool features during the trial length

Final words

As we have seen above, when choosing the best possible SQL Server auditing solution, it is important to pay attention to the following:

Auditing mechanisms

Which events and what information on the events can the tool capture

Data protection and safety

Reporting (custom and built-in)

Alerting capabilities

Ease of use and learning curve

Repository and data maintenance

Tool support and future

Proof of concept essentials

While some of the above categories may not be mandatory for specific environments, the recommendation is to always plan for the future, since auditing requirements and compliance standards are getting more strict and greater in numbers each year, so plan for the future in addition to the current requirements. Be sure to pick the tool which is best suited for your exact environment which also has a solid plan for future features you need or may need in the future based on the auditing requirements projections, predictions, and trends.

[marcosplavsczyk]

Creating a foolproof disaster recovery solution is imperative for every business. After the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are defined, a database backup plan should be created to match these objectives. Most DBAs tend to automate the majority of tasks related to database backup plans. Regular database backup schedules are set in order to create continuous backup chains, that can later be used to recover a database in the case of a disastrous event. By setting the backup schedule, the continuity of the backup process is ensured, and most of the job is performed automatically on a regular basis.

There are many situations when backup files need to be created outside of the regular backup schedule. These backup files can be created manually, in a regular way, with usual BACKUP DATABASE command. But using this approach can prove not always helpful, in the case of a disaster, if we need to restore a backup file that is created after this manual out-of-the-schedule backup operation. Let’s look at the few examples where manual database backup performed out of the regular schedule affects the daily routine:

Database uses simple recovery model

The full database backup is taken daily (at midnight for example) and regular differential backups are taken every hour. This way, the RPO of one hour is ensured. The disaster occurred around 7:30 AM, and DBAs reasonably decided to restore last differential database backup that was created at 7:00 AM. Unfortunately, the restore operation failed with the following message: “This differential backup cannot be restored because the database has not been restored to the correct earlier state”.

Further investigation of backup history showed that another full backup was taken that day at 6 AM. This manual database backup ended the backup chain that starts with the full backup file at 12 AM, and started the new chain. All differentials created before 6 AM use the 12 AM backup as their base. All differentials created after the manual backup at 6 AM use this backup file as a base. Therefore, the 12 AM full backup cannot be used to restore 7 AM differential backup.

In this case, if 6 AM manual full database backup got lost, all further differentials would be useless, and recovery would not be possible for the time up until the next full backup.

Picture 1: Effects of unscheduled full database backup on the differential log backup chain

Database uses full recovery model

One of the greatest advantages of full recovery model databases is the ability to recover a database to specified point in time, provided that all recovery resources are available. This also brings us to the main drawback of the full recovery model: it is necessary to perform regular transaction log backups to prevent uncontrolled growth of database’s transaction log. Storing and maintaining log backups requires additional resources, but once the process gets automated, the benefits often outweigh the effort.

Unlike in the previous case, taking a full database backup outside of the backup schedule won’t affect the transaction log backup chain. This is logical, since a full database backup operation doesn’t truncate transaction log of a database, and all transaction log backups contain transactions committed since previous transaction log backup only. The only exception to this rule is the first full database backup taken after the database is created. This doesn’t mean that we cannot use last full database backup as a starting point when performing point in time recovery. We can use any other available full backup, provided that we have available all transaction log backups created after that full backup, up to recovery point.

In the example, shown in picture 2, an unscheduled full database backup is taken at 9 AM. If the disaster occurred at 12 PM, both 12 AM and 9 AM backup files can be used as the starting point of the restore sequence. Restore sequence 2 would be much faster, but if manual backup file got lost, a restore would still be possible with the 12 AM backup file and subsequent log backups.

Picture 2: Effects of unscheduled full database backup on the transaction log backup chain

For databases in full recovery model, the problem might occur if someone took transaction log backup outside of the regular backup schedule, and misplaced it. The missing log backup creates a gap in the backup chain, and all transactions that were stored in this file will not be possible to recover.

For example, for the low traffic databases, the following backup schedule can be used:

– Full database backups are created daily at 12 AM – Transaction log backups are created every 3 hours

If someone took out-of-the-schedule transaction log backup at 10:30 AM, it will contain all transactions that were committed between 9 AM and 10:30 AM. If this file gets misplaced, these transactions will be lost indefinitely, and point in time restore will not be possible for this period.

Picture 3: Effects of unscheduled transaction log backup on the transaction log backup chain

Solution

The best way to avoid described issues when taking backup files out of the scheduled time frame is to use copy-only backups. Copy-only backups are independent backup files that do not disrupt the regular Log Sequence Number (LSN) routine. This means that backup chains and restore sequences won’t be affected by the copy-only backup. There are two types of copy-only backups: full database backups, and transaction log database backups. Differential copy-only backups are not supported.

Copy-only full backups can be used with databases in any recovery model. When copy-only full backups are restored, the process and effects are the same as if the regular full backup is restored. The difference is that copy-only full backup cannot be used as the base for future differential backups as shown on picture 4.

Picture 4: Effects of copy-only full backup on differential log backup sequence

If used with the sequence of transaction log backups, copy-only full backup acts in the same way as regular backup. In that case, the situation is identical to the one described on picture 2.

Copy-only transaction log backups work in a same way, but can only be used with databases in full and bulk-logged recovery model. They contain all transactions that were committed since previous regular transaction log backup, and they never truncate the transaction log. This means that the next regular transaction log backup will be able to capture all transactions since previous regular transaction log backup. This way, there is no risk of potential data loss.

Picture 5: Effects of copy-only transaction log backup on the transaction log backup chain

Copy-only backup files can be easily created by using T-SQL scripts, through user interface in SSMS, or with a third-party solution like ApexSQL Backup.

Create copy-only backups with T-SQL scripts

The query that is used to create copy-only full database backup is similar to the usual backup query. The only difference is WITH COPY_ONLY parameter. For the AdventureWorks2014 database, the following query can be used:

-- Query 1: Copy-only full database backup BACKUP DATABASE AdventureWorks2014 TO DISK = 'E:\Backup\CopyOnly_AdventureWorks2014.bak' WITH COPY_ONLY;

Running the query produces the backup file at the specified location:

To create copy-only transaction log backup, use the BACKUP LOG command:

-- Query 2: Copy-only transaction log backup BACKUP LOG AdventureWorks2014 TO DISK = 'E:\Backup\CopyOnly_AdventureWorks2014.trn' WITH COPY_ONLY;

The backup file is created in the same way as in the previous example, but this time the TRN file type is used.

Create copy-only backups in SQL Server Management Studio

To create copy-only backup files by using SSMS GUI, perform the following steps:

Expand the Databases node in tree view of Object Explorer. Right click on the database that needs to be backed up, and click Tasks/Back up… in the context menu.

In the General tab of Back Up Database, specify the database and backup type of the copy-only file. Make sure to check the box for Copy-only backup. Note that selecting Differential backup type disables the Copy-only backup control.

In the Destination section, the name of the last created backup file will be specified. To avoid overwriting this file, click on the Add button, and provide different file path for the backup. Make sure to include the proper name for the backup file in the file path. If needed, use the browse button to locate the backup destination manually.

Click OK button to complete the process with the default settings. The action completes with the success message:

The backup file is located at the specified file path.

Quick tip:

Copy-only backups are supported in all SQL Server versions, starting with SQL Server 2005. However, the copy-only backup files cannot be created through SSMS 2005 user interface, but only with T-SQL script. All later versions of SSMS have the copy-only option available.

Create copy-only backups with ApexSQL Backup

ApexSQL Backup is a 3rd party solution that also supports copy-only backups. The files are easily created and managed through GUI, even for the SQL 2005 databases. To create the copy-only backup file with ApexSQL Backup, perform the following steps:

Navigate to Home tab, and click Backup button in the application ribbon.

In the main tab of the Backup wizard, specify the server, database and backup type for the backup job.

Optionally, set the network resilience. If the box is checked, the application will automatically rerun the job, in the case that it fails for any reason. This option can be useful if there is a high traffic on the network, and timeout errors are encountered frequently.

Click on Add destination button to specify backup destination. Type the file path in the Folder text box, or browse for the backup destination. In filename box, type the custom name of the backup file, or use tags to generate the filename automatically. By using the tags, the information such as server, instance and database names, as well as backup type, date and time can be automatically included in the file name. Click OK to confirm the selection.

This brings us back to the Backup wizard. If needed, set the schedule for this backup operation at the bottom of the page.

In Advanced tab of the backup wizard, check the Copy-only option. Optionally, check additional options regarding media sets, verification, compression and encryption.

If needed, set email notifications for the created task. Click OK button to execute the job. If a schedule was specified in step 5, the backup job with all defined parameters is created. If not, all commands are executed immediately. In this case, the backup schedule is created, so it could be reused at any time when the need arises.

To run the created schedule, navigate to Schedules tab. Check the box in front of the created schedule, and click Run now button.

As soon as the schedule completes, Last run and Result columns are updated with corresponding data, and backup file is created on specified location.

One of the greatest advantages of ApexSQL Backup schedules is the reusability: the schedule can be disabled (to save hard disk space), and run manually only when there is a need for the copy-only backup. Parameters for the job can be reconfigured at any time with Edit button. Information on job status is available as soon as the job completes.

All actions performed through ApexSQL Backup, including the creation of copy-only backup is recorded in ApexSQL Backup central repository. Best way to check on these actions is through Activities tab, where all performed tasks are displayed in the grid.

Related pages:

Copy-Only Backups (SQL Server)

Point in Time Recovery with SQL Server

What is backup and restore in SQL Server disaster recovery?

The post How to create Copy-Only backups in SQL Server appeared first on Solution center.

[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.

[marcosplavsczyk]

Introduction

Apart from numerous factors, poor index maintenance can be a reason for decreased SQL Server performance. If a database contains tables with numerous entries, that get updated frequently, it is most likely that high index fragmentation will occur. For smaller indexes, high fragmentation does not necessarily degrade the performance of the queries that are run on a table. But for the larger tables, with indexes that consist of 1000 pages and more, fragmentation could cause noticeable performance issues. Luckily, performing index maintenance tasks on a regular basis can eliminate the risk of degrading performance significantly. The most effective ways for treating index fragmentation are reorganize and rebuild index operations.

For smaller databases, index maintenance tasks can be run manually, when the need arises. Detailed instructions for running reorganize and rebuild jobs manually can be found in the article: Why, when and how to rebuild and reorganize SQL Server indexes. But most larger and high traffic databases require index maintenance on a regular basis: weekly, or even daily. For these use cases, it is wise to configure a policy that would run automatically, on a schedule set by the DBA.

This article shows three solutions for creating fully functional SQL Server defragmentation policy:

Configuring the policy as SQL Server Agent job – this requires some skill with querying and some basic knowledge of T-SQL. Although a bit complex, this option is the most customizable. The SQL Server agent service needs to be installed and running.

Using a maintenance plan in SQL Server Management Studio – this option is a bit more user-friendly but does not allow the user to defragment a specific index. Defragmentation is allowed only on the table level. This option also requires SQL Server Agent to be installed on the server.

Using ApexSQL Defrag policies – ApexSQL Defrag is simple, yet powerful 3rd party solution for index fragmentation management in SQL Server. All operations are defined and deployed in graphical user interface and do not require the use of T-SQL or SQL Server Agent.

Detecting SQL Server index fragmentation

Before we apply any of the listed solutions, it is necessary to run index analysis first, in order to detect highly fragmented indexes. Running a reorganize or rebuild job on each database, schema or table index might sound like the easiest solution, but is not advised, since it can be a quite time consuming and resource intensive task. The best way to check index fragmentation in SQL Server is to use the built-in function sys.dm_db_index_physical_stats. This function returns size and fragmentation information for all indexes on a server, all indexes in a database, all indexes in a table, or just a single index, depending on provided parameters. Querying the raw function with basic parameters should yield all necessary information for the analysis, but the results will be quite unorganized. Therefore, we use the modified query:

--Script 1: Detecting index fragmentation SELECT dbschemas.[name] AS 'Schema', dbtables.[name] AS 'Table', dbindexes.[name] AS 'Index', indexstats.avg_fragmentation_in_percent AS 'Frag (%)', indexstats.page_count AS 'Page count' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() ORDER BY indexstats.avg_fragmentation_in_percent DESC

Running the query on AdventureWorks2014 database yields the following results:

Schema, table, name, fragmentation percentage and page count is displayed for each index in the database. Results are sorted by fragmentation percentage descending, so we could easily isolate indexes with highest fragmentation.

Depending on the fragmentation percentage value, fragmentation may be recognized as:

High fragmentation – fragmentation percentage values are above 30%. Highly fragmented indexes usually require rebuild operation.

Medium fragmentation – for the values between 10% and 30%. The best method to correct medium fragmentation is to use reorganize operation.

Low fragmentation – for the values below 10%. Indexes with low fragmentation do not require defragmentation, since they have minimal impact on the server performance.

As soon as analysis is completed, we can write the defragmentation script.

Writing a defragmentation script

To write a custom script, decide which indexes to include in it, depending on the results from the previous query. Generally, indexes with fragmentation levels between 10% and 30% need to be reorganized, while those with higher fragmentation need to be rebuilt. Another important factor when choosing which indexes to include in a script is index size. Small indexes can be included in the script, but defragmenting them does not affect server performance significantly. In general, indexes smaller than 1000 pages are rarely included in defragmentation jobs.

To reorganize all indexes in a table or just speciffic index, use the following statements:

--Script 2: Reorganize script --2.1 Reorganize single index ALTER INDEX Index_name ON Table_name REORGANIZE ; --2.2 Reorganize all indexes in the table ALTER INDEX ALL ON Table_name REORGANIZE ;

Rebuild script has the same syntax, but uses REBUILD statement instead:

--Script 3: Rebuild script --2.1 Rebuild single index ALTER INDEX Index_name ON Table_name REBUILD ; --2.2 Rebuild all indexes in the table ALTER INDEX ALL ON Table_name REBUILD ;

Writing reorganize and rebuild statements for each index, especially for the large databases can be a tiresome job. Therefore, we decided to use a dynamic script that automatically checks for the index fragmentation and index page size, and applies reorganize or rebuild operation depending on the results.

To use the script effectively, it is necessary to set a few threshold variables first:

@reorg_frag_thresh – Threshold for reorganizing an index in %. Suggested value for the variable is 10 (all indexes with fragmentation between 10% and 30% will be reorganized).

@rebuild_frag_thresh – Threshold for rebuilding an index in %. Suggested value for the variable is 30 (all indexes with fragmentation of 30% and above will be rebuilt).

@fill_factor – Speciffies the fill factor value for the rebuild operations. Default value is 0 (100% fill factor). It is strongly reccomended not to change this value, since it can result in growth of rebuilt indexes if value is set anywhere between 1 and 99.

@page_count_thresh – Page count threshold. All indexes equal or larger than set number of pages will be processed.

-- Script 4: Automatically analyze and defragment indexes -- Set variables -- ********************************************************************************************* SET NOCOUNT ON DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0 DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0 DECLARE @fill_factor tinyint SET @fill_factor = 0 DECLARE @report_only bit SET @report_only = 0 DECLARE @page_count_thresh smallint SET @page_count_thresh = 1 -- ********************************************************************************************* DECLARE @objectid int DECLARE @indexid int DECLARE @partitioncount bigint DECLARE @schemaname nvarchar(130) DECLARE @objectname nvarchar(130) DECLARE @indexname nvarchar(130) DECLARE @partitionnum bigint DECLARE @partitions bigint DECLARE @frag float DECLARE @page_count int DECLARE @command nvarchar(4000) DECLARE @intentions nvarchar(4000) DECLARE @table_var TABLE( objectid int, indexid int, partitionnum int, frag float, page_count int ) INSERT INTO @table_var SELECT [object_id] AS objectid, [index_id] AS indexid, [partition_number] AS partitionnum, [avg_fragmentation_in_percent] AS frag, [page_count] AS page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE [avg_fragmentation_in_percent] > @reorg_frag_thresh AND page_count > @page_count_thresh AND index_id > 0 DECLARE partitions CURSOR FOR SELECT * FROM @table_var OPEN partitions WHILE (1=1) BEGIN FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag, @page_count IF @@FETCH_STATUS < 0 BREAK SELECT @objectname = QUOTENAME(o.[name]), @schemaname = QUOTENAME(s.[name]) FROM sys.objects AS o WITH (NOLOCK) JOIN sys.schemas AS s WITH (NOLOCK) ON s.[schema_id] = o.[schema_id] WHERE o.[object_id] = @objectid SELECT @indexname = QUOTENAME([name]) FROM sys.indexes WITH (NOLOCK) WHERE [object_id] = @objectid AND [index_id] = @indexid SELECT @partitioncount = count (*) FROM sys.partitions WITH (NOLOCK) WHERE [object_id] = @objectid AND [index_id] = @indexid SET @intentions = @schemaname + N'.' + @objectname + N'.' + @indexname + N':' + CHAR(13) + CHAR(10) SET @intentions = REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) + @intentions SET @intentions = @intentions + N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) + N' PAGE COUNT: ' + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10) IF @frag < @rebuild_frag_thresh BEGIN SET @intentions = @intentions + N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10) SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE; ' + N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + N' ' + @indexname + ';' END IF @frag >= @rebuild_frag_thresh BEGIN SET @intentions = @intentions + N' OPERATION: REBUILD' + CHAR(13) + CHAR(10) SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD' END IF @partitioncount > 1 BEGIN SET @intentions = @intentions + N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10) SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)) END IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN SET @intentions = @intentions + N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10) SET @command = @command + N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')' END IF @report_only = 0 BEGIN SET @intentions = @intentions + N' EXECUTING: ' + @command PRINT @intentions EXEC (@command) END ELSE BEGIN PRINT @intentions END PRINT @command END CLOSE partitions DEALLOCATE partitions GO

Set defragmentation policies with SQL Server agent job

Before starting with the job configuration, make sure that SQL Server Agent is installed and running. To do this, open the SQL Server Management Studio, and find SQL Server Agent at the bottom of the Object Explorer. Right click on the agent, and click on the Start button in the context menu. In case that agent is running already, skip this step.

To create SQL Server agent that will defragment specified indexes automatically, perform the following steps:

Expand SQL Server Agent in Object explorer, right click on Jobs, and select New Job…

In General tab, specify the name and description for the job. Make sure to thick the Enabled checkbox. Proceed to the Steps tab.

Clicking on New… button in Steps tab opens the form for the first job step configuration. Provide any name for the step, and leave all other values as default. In the command window, paste the script created in previous chapter, and click OK.

In Schedules tab, in New job window click New button to create the schedule for the job. Set the schedule type, frequency and daily frequency. Since REBUILD statements lock the tables during the defragmentation process, it is best to set the schedule to time when servers experience the least amount of traffic. In this example, schedule is set to run each day at 2:00 AM. Click OK to save the schedule.

If needed, set Alerts and Notifications in respective tabs. In Targets tab, specify the targets for the job. To be able to target multiple servers, it is necessary to either create Central Management Server and Server groups or Multiserver environment. Click OK to finish job configuration.

To run the selected job immediately, expand SQL Server Agent and Jobs in Object Explorer, right click on created job, and select Start Job at Step. Since our job has only one step, it will start executing automaticaly.

Set defragmentation policy with maintenance plans

To create and run defragmentation policies with maintenance plans, it is necessary to perform the following steps in SQL Server management studio:

Expand the Management node in Object Explorer, right click on Maintenance Plans, and select New Maintenance Plan… from the context menu. Specify the name for the maintenance plan, and click OK.

Double click on Subplan_1.

In Subplan Properties, set the name and description for the subplan, and click on schedule icon.

Set the schedule for the subplan. When completed, click OK in both New Job Schedule and Subplan Properties windows.

Drag and drop Rebuild Index Task and Reorganize Index Task from the Toolbox to the clear area in Defragmentation plan design. If there is no Toolbox window visible on the screen, turn it on in the View tab of SSMS, or simply click CTR+Alt+X to activate it. Make sure to connect the green arrow from Reorganize Index Task with the Rebuild index task window.

Double click on Reorganize Index Task.

In opened window, select the database for the task from the Database(s) drop down menu

From the Object drop down menu, select Table option. If multiple databases got selected in previous step, this option will be disabled, and the job will reorganize all indexes in the selected databases. All tables for the specified database are listed in the Selection drop down menu. See the results of index fragmentation analysis, and select the tables that contained indexes with fragmentation levels ranging from 10% to 30%. Click OK to save changes for Reorganize Index Task.

Double click on Rebuild Index Task. As in steps 7 and 8, select the database and the tables to add to the task. There is no need to change any settings in Free space options and Advanced options.

Right click on the heading of Defragmentation plan design, and choose to Save Selected Items

The maintenance plan is now ready, and will run according to the set schedule. But should the need arise, it can be run immediately. To run the created plan right away, expand Management and Maintenance Plans nodes in Object Explorer. Right click on the created maintenance plan, and select Execute

As soon as the plan gets executed, the success message is displayed

Set defragmentation policies in SQL Server with ApexSQL Defrag

Setting defragmentation policies with ApexSQL Defrag is easy and straightforward. There is no need to run separate index fragmentation analysis, to use complex queries, or to memorize which indexes need to be defragmented. ApexSQL Defrag policies analize all selected objects first (databases, tables or single indexes). Depending on the results and set thresholds, it automatically runs reorganize or rebuild operations on selected objects. Furthermore, all created policies can be enabled or disabled at any time.

To create the policy in ApexSQL Defrag, perform the following steps:

To be able to manage a server with ApexSQL Defrag, add it to its server list first. Open ApexSQL Defrag, go to Home tab, and click on Add button in Servers group. In Connection window, select a server from the drop down menu. Specify authentication type, username and password for the selected server. The server will appear in the server list if provided credentials are valid.

To create a new policy, click on Policies button above the taskbar, and select Create

Set the name, description and schedule for the new policy

In Targets box, click browse (…) button. Select the objects that need to be included in the policy. There is no need to worry about the current fragmentation of selected object since all selected objects get analyzed automatically on each new policy run. Click OK to confirm the selection.

Click on Thresholds tab. Index fragmentation thresholds for rebuild and reorganize operations can be set manually on the two sliders. Default value for reorganize is set between 10% and 30%. All indexes that have fragmentation value above 30% will be rebuilt by default.

The final result of index fragmentation analysis depends on the selected scan mode. Choose between limited, sampled and detailed scan mode.

Limited mode – this is the fastest mode that scans the smallest number of pages. In the limited mode, only the pages above leaf level of an index get scanned.

Sample mode – only 1% of all pages in an index or a heap get scanned.

Detailed mode – all pages are scanned and included in statistics. It is the slowest mode and takes the most resources when compared with other modes, but it returns the most accurate statistics.

In Resources threshold, set the CPU load or memory usage thresholds. The policy will stop automatically if any of the thresholds values are reached.

If needed, set the email notification in Notification tab. Choose to receive notifications on job failure, job success, or both. To add email recipients to the list, click on the Add button, and type in the email address. When done, click OK to save the changes.

The policy is now available in the policy list. It will run automatically based on the schedule set in step 3. There are several operations on the created policies that are available in ApexSQL Defrag:

If the policy needs to be run right away, right click on the policy in the list, and select Run from the context menu. The policy will execute immediately. The result of the last run is available as soon as policy completes.

The selected policy can also be enabled or disabled from the context menu. An enabled policy runs depending on a set schedule. A disabled policy will not execute until it is enabled again.

If any parameters need to be changed for a policy, it is best to use Edit command from the context menu.

Finally, if the policy is no longer needed, it can be deleted either by using the command from the context menu, or by selecting it, and clicking on delete button in the ribbon.

Related links:

sys.dm_db_index_physical_stats (Transact-SQL) Reorganize and Rebuild Indexes What is causing database slowdowns?

  The post How to automate SQL Server defragmentation using policies appeared first on Solution center.