Configuring the number of SQL Server Error Logs

Posted on by
Reply

SQL Server Error Logs; Introduction

By default, SQL Server maintains a minimum of 6 Error Log Files and each time the SQL Server is restarted, the Current Active Log File is recycled and new one maintained.

The Error Log Files are stored in the “Microsoft SQL Server\MSSQL.1\MSSQL\LOG\” Directory.

Monitoring the Error Log Files is very important since it displays vital information regarding the Server. Some of them are security related login information, Logins info such as Failure Logins or Failure and Success Logins etc are written based on the option chosen for the Instance. Other information reported includes Changes in Database Settings, Database backup related information; both successful backups and failures are reported.

Apart from those mentioned above, SQL Error Logs contains the useful info when a SQL Server does not start and what’s causing the failure to start.  There is a lot more information displayed in a SQL Server Error Log, a sample screen of what are the messages displayed upon SQL restart is displayed in the picture below.

Configuring Number of Error Logs

The default value of 6 Error Logs can be increased but cannot be decreases. To configure a higher number of Error Logs, follow the Steps as described below.

In the Object Explorer of SSMS, navigate to the Management Node and right click on SQL Server Logs and choose Configure as shown below.

A dialog box is displayed and the check box beside for Limiting the Number of error log files would be unchecked by default.

Check that and select the desired number of log files in the range between 6 and 99 and click the OK button (Not displayed in the Picture) to save the changes. A restart is not necessary for the changes to effective.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

 — Bru Medishetty

Deploying SSIS Packages

Posted on by
15

In this article we shall take a look at the steps in building an SSIS Package and deploy it.

You may have designed the package in your Development System and tested the Package from BIDS. Now it’s time to move it to the SQL Server. Up to this point, you have developed a Package in BIDS but have not deployed it onto the Integration Services. Let’s look into the steps involved in moving it to SQL Server Integration Services.

Deploying SSIS Packages would be helpful when you have multiple packages in a single solution, so that you can deploy all of them at once.

Building the Solution / Packages

Once the packages are all developed, right click on the Solution in Solution Explorer and choose properties in the Menu. Upon selecting properties, a Dialog box appears as shown in the picture below.

The output path is by default bin, it is the directory where the files that are built are going to be placed by SQL Server.  For deploying the SSIS Packages we need to have deployment files which will not be created by default when you build a project, to enable this under the configuration properties in the left Pane, select Deployment utility as shown in the picture below and change the property value to True corresponding to CreateDeploymentUtility, additionally you can also set the Directory path where the deployment utility has to be placed upon building the solution. Choose your settings and click Apply to ensure the changes in the properties are saved.

The Next step is to Right Click on the Solution in the Solution Explorer and choose Build. (as shown in the picture below)

The status of the build process is showed at the bottom left corner of the BIDS as shown below.

When the build /rebuild is successful, navigate to the directory is referred in the DeploymentOutputPath couple of screens back. You should be seeing the packages in the solution and along with them an additional file usually of 1 KB which is the Manifest File what helps in deploying the packages in the solution.(Picture below)

Deploying the Packages

Double click the Manifest File to start the deployment. The Package Installation wizard begins and Deploy SSIS Packages step is the first screen that is presented.

This screen lets you select where shall the packages be deployed, as mentioned in the Dialog Box, deploying in SQL Server is more secure, since SQL Server stores the packages internally compared to File System where additional security measures needs to taken to secure the physical files. My personal choice would be to deploying inside SQL Server.

Additionally, choose the option to validate the packages after Installation.

If the deployment is on the same Server you can mention “.” in the Server Name or choose the Server Name from the drop down list if is a remote Server where you would like to deploy the packages. Choose the Authentication mode that you would like to connect to that Target Server you want to deploy. Click Next to continue.  

The dialog box above displays, the location where the Package dependencies are going to be stored and can be modified if needed. Click Next after deciding the location for dependencies.

Click Next in the above screen to deploy the packages. The Installation is done and the tasks in the packages are validated.  

Click on Next in Dialog Box and you should be able to see a View Installation details Dialog. Click Finish to complete the Deployment of the SSIS Package.

One of the next steps would be to schedule the Package using SQL Server Agent.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Comparing the High Availability Features in SQL Server 2005

Posted on by
13

In this Article, we shall examine the High-Availability solutions in SQL Server 2005 and which solution can be well suited for a certain scenario. Though they are all part of High-Availability not all are feasible in a particular scenario. This article tries to evaluate the 4 HA features, their scope, Advantages and Disadvantages and it is does not explain the mechanism how they function or any other technical implementation details.

Failover Clustering

The reason I start with Failover Clustering is, it provides Availability at an Instance level and most of the Production Instances are implemented using SQL Server Failover Clustering.

Scope

Instance Level (All Databases in a single Instance)

Core Functionality

The main functionality of Failover Clustering is to minimize the downtime of the SQL Server on an instance level.

Requirements

The Hardware needs to be certified for Implementing Failover Clustering. Moreover all the participating Nodes in the Cluster need to be of Similar Hardware Configuration.

Advantages

1. Provides Automatic Failover of the SQL Instance to the Participating Node (Passive Node), hence no manual configuration is required when failover occurs.

2. Is not related to the Recovery model of the Databases.

3. Application that rely on more than one Database of an Instance, do not need changes as all the Database are available upon failover, where as in other 3 HA features, the dependent databases also have to be configured for availability.

Dis-advantages

1. Purely relying on Failover Clustering is not suggested since a Data Disk Failure would result in not having another copy of the Databases to Fallback immediately.

2. There is no redundant copy of the Database; hence if a Reporting Application requires the same copy of the Transactional Database, then it has to rely on the existing Database impacting the performance of the main application.

3. Cannot be implemented with distributed systems, the Nodes are required to be within 100 Miles ( I remember reading in BOL )

Database Mirroring

Scope

Individual Database Level.

Core Functionality

The main functionality of Database Mirroring is to provide a mirrored set of the Database for immediate failback.

Requirements

Does not require a signed or certified Hardware and can be implemented on standby Server which can be of a different configuration unlike Failover Clustering.

Advantages

1. Supports immediate and automatic failover of applications using ADO.Net and SQL Native Client, using Client-side Redirect.

2. Supports multiple modes of configuration namely, High Availability Mode, High Protection and High Performance Mode. Each mode has its advantages, providing an option to choose accordingly based on requirement in hand.

Dis-advantages

1. Database redundancy is not provided for Read-Only purpose, but a workaround way using Database Snapshots on the Mirrored Database is suggested in BOL. It has to be remembered that Database Snapshots are limited to Enterprise Edition of SQL Server 2005 (Not sure of Database Snapshots on the Mirrored Database in SQL Server 2008)

2. Can be implemented only with Databases having FULL Recovery model, hence databases with other recovery models cannot be used in Database Mirroring.

Log Shipping

Scope
Individual Database Level.

Core Functionality
Provides Database availability by shipping transaction logs onto single / multiple Secondary servers.

Requirements
Does not require Signed Hardware and does not require Hardware that is Similar to the Primary Server.

Advantages

1. Using immediate restore option, we can setup almost an exact copy of the production system at the Secondary Server.

2. Delayed Restore; using this option you setup a secondary Server with the transaction Logs shipped but are not restored immediately, this option would be highly helpful, if incase of Data Corruption / Loss because of a User accidentally deleting / dropping / modifying important Data. If acted immediately, you should be able to recover the Data from the Database on the secondary Server.

3. Multiple Secondary Databases can be setup for a single Primary Database, providing the option to use one copy for DR and another for a reporting kind of use (The Users need to be disconnected when the Logs are restored and that can be done Automatically by SQL Server), so restoring Job can be scheduled for every 6 Hours so as not to interrupt reporting users frequently.

Dis-advantages

1. Automatic Redirection / Failover of the application is not possible.

2. When compared to Database Mirroring or Transactional Replication there is a slight delay in synchronizing the Database at the secondary server.

3. Can be implemented only with Databases having FULL/Bulk-Logged Recovery model.

4. Sometimes Entire Database is not required for the application when coming back immediately from a Disaster. Let me explain, a Database has multiple years (let’s say 7) of transactional Data and you may not need the entire 7 years of Data when disaster strikes, the most recent few years (may be 2 or 3) is good enough to start immediately. OR a subset of the Database is required for a Marketing application; you cannot provide it using Log Shipping.

Replication

Scope
Database Object Level

Core Functionality
Provides a set of tools that enable copying of the Source Data to multiple Distributed Servers for High Availability and Scalability. There are 3 types of Publications available and can be used based on the data change frequency or direction of Data flow (updateable and Merge Replications).

Requirements
Designated Hardware Servers are not required.

Advantages

1. Provides redundancy of Database, giving a choice to use one system for reporting and another for DR or Hot standby etc.

2. Can be used to build Scale-Out Implementation by using Peer-to-Peer Transactional Replication.

3. Can be used to setup a Reporting / Data Warehousing System which needs only some part of the Source Database, this can be achieved using Horizontal and Vertical filtering of Data.

4. Oracle Database can be used as a Publisher.

5. Independent of the Recovery Model of the Database, hence any database can be published.

Dis-advantages

1. Automatic Failover of the application is not possible, the application has to be coded / changed to use the standby system.

2. Many Table related properties, such as Index Partition Schemes, Check Constraints and Non Clustered Indexes etc are not propagated to the subscribers by default; they need to be configured when publishing.

3. Data Conflict can occur when Merge Replication is configured and sometimes may turn out to be very complex to solve the conflicts.

Conclusion:

Each of the high availability features presents a way to have Database (or Data) availability, and at the same time cannot be used in all scenarios. It is through proper understanding and planning, one or more than one of these features are used in order to achieve the desired goal or setup a DR Environment.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty