Introduction
The Application Architecture turns out through the experience over the year that the application level changes and has an effective optioned for making the application level changes. And while performing on third party application it doesn't have much control over the internal applications. It may be that of the legacy application that have written long back ago for improving the performance and scalability.
And the application refactoring can have a huge impact on the performance for making the application level changes by reducing the overall workload on SQL Server. And working at the application level is the first place that should be a key role. A skillful DBA can be very often pinpoint about the application the application behavior that causing the major burden in the SQL Server and it's processor perspective, a memory perspective and its i/o perspectives.
Description
In the application architecture and design issues describes the overuse of objects relational mapping code generators. And using ad hoc Transact-SQL from the application layer instead of using Transact-SQL stored procedures that lives inside the database. And overuse of XML data, which is not as common as for all the servers. Later by using SQL Server scalar user defined functions that contain many performance issues. The database should be utilizing as a data store not as an application server. The overuse of DML triggers for an application logic. And get into the CLR assemblies and those being overused when they are not needed.
A major and most common problem is just requesting too much data from the database and putting to much load on the infrastructure by allowing the user controlled and running newly created transaction from the applications, and the problem that causes with locking, blocking, and deadlocks. The over optimistic use of new features that show up in new version of SQL Server that some times causes a major problem. Basically related to that is not having a reporting strategy using the OLTP data store for reporting and that causes not by using the middle tier caching to reduce the load on the database server.
Conceptual
figure
- Browser - Is a services that run on a machine.
- Firewall - Is a packet filtering method.
- Web application - Is an application program that is stored on a remote server.
- Application - Is a program.
- Database - Is a collection of logical related data.
Description
Application refactoring is one of the most important concept in the acquiring the main jobs as a good DBA and is defending SQL Server from bad applications. Huge improvements in this area are possible when all the available resources have been met. And by using DMV queries, extended events and SQL profiles to help in finding the most costly queries and stored procedures. And can check the most frequently executed queries and stored procedures and contains in the middle tier caching and can look for a quite application that cause extra query activity.
In the application the stored procedures is being called two or three times when it only need to be called once, just because of mistakes in the applications and developers developed in the code. But as often there is no control over the applications and the coded they have developed. As of this the third party applications and many ISV applications haves serious database usage issues. The Microsoft applications that use SQL Server as a data store are the software notorious for this sort of issues. And all the is famous for having performance issues like Share points, Lync, System center, Team foundation servers.
Description
Some of the middle tier caching tools includes are:
- Memcached
- Windows Server AppFabric 1.1
- Memcached is a distributed memory object caching system.
- Memcached uses an in memory, key value store for data.
- Open source and can run on many different operating systems.
- The AppFabric caching services is a window service that runs on one or more host servers.
- A cache clusters is a collection of one or more instance of the caching services working together in the form of a ring to store and distributed data.
- Cluster configuration information can be stored in XML files or in a SQL server database.
- Windows server AppFabric 1.1 only runs on windows operating system.
Key Points
- Application Architecture - Most effectively optioned for making the application level alterations.
- Application Architecture And Design Issues - Describes the overuse of relational mapping code generators and other types.
- Application Refactoring - Protect the server from undefined conditions.