SQL Server DBA Support

March 10, 2017 Leave a comment

SQL Server DBA Support

 

  • Proactive Problem management
  • Predictive capacity analysis
  • Intuitive event and incident correlation

Apart from this our service catalogue comprise of regular DBA activities like Monitoring, Backups, Space Management, Resource Utilization, License Rationalization, patch management Database Vulnerability Assessment, Object Migration and other activities. Other activities include:

  • Manage hierarchical security (access control, permissions, encryption keys and certificates) for databases and ensure data integrity using templates and schemas
  • Testing of new releases and features for application, O/S and Database upgrade & Troubleshoot application performance & Optimize query& index performance.
  • Designed & Tested a Scale-Out Architecture using Replication, DTC / 2PC, Distributed Database  with Data & Middle Tier, Web & Client Tier  for SQL Server Databases to facilitate a High-Availability environment by creating Read-Only copies for Reporting Services & Read-Write Hot copies of critical Databases, for Hot Instant Disaster Recovery.
  • Enhanced performance, scalability and usability across multiple cores & support that efficiently scale computer, networking and storage in both physical and virtual environments.
  • Establishes and maintains policies, standard operating procedures, and associated documentation for user interaction with the database environment.
  • Support on-premises to Microsoft Azure IaaS , PaaS & amazon RDS, Hybrid Scenarios with SSIS, Stretch Database, PowerBI with on-premises data.

Revenue and Risk Reward Program:

 

  • SLAs based on Risk Reward –Example. Availability of 99.95%
  • 10% cost reduction year over year during contractual period
  • Technology Consumption Management to ensure customer uses licenses/servers to the minimal and reduce OPEX costs.
Advertisements

SmartERP SQL server Portfolio

SmartERP SQL Health Shop Clinic


By utilizing SmartERP’s foundational ITIL process and delivery methodology, our SQL DBA experts can apply their breadth of expertise to provide both proactive and reactive database support. Our proactive database support will ensure that your database environment receives the preventative care it requires for the future while also promising to have the availability to respond to your most critical needs with integrity.

SmartERP’s robust support service model encompasses a wide range of activities. Within our company’s best practices, our expert DBAs will provide routine tasks, such as patching, backups, configuration and installation. Other support tasks are tailored to each unique database environment. This includes customized deployment procedures, refresh methodologies, failover and replication routines that require focused attention beyond rudimentary knowledge application.

Irrespective of the SQL version that you are using, by choosing SmartERP SQL DBA Support, you can be rest assured that your database is in safe hands. We are running SQL health clinic where many options do exist in the market; the question still remains to which IT cardiologist you would want to trust for the maintenance of the heart of your enterprise. We would like to introduce  complete SQL packages solution with multi specialist clinic where each SQL disease will be treat as a proper diagnosis along with permanent solution. We have a very strong DBA and Development team also where people are very good in DBA, BI and reporting side which ensure that whenever client reach to us with SQL related task they will be great fair treatment.

The delivery design of SmartERP’s database support will never restrict your company’s ability to optimize your environment or ignore opportunities to improve current practices.

DTS Package migration steps from SQL 2000 to SQL2008

March 31, 2015 Leave a comment

  1. Run time support installation for DTS packages.
  2. Design time support installation for DTS packages.
  3. Configure SSMS for DTS packages.
  4. Migrate the DTS packages by using SSMS.These are all high level steps which includes below step by step activities.
  • Check the backward compatibilities and integration services are installed on target server or not.Check the integration service is there or not.
  • Run services.msc
  • If the above services are not installed, go to binaries location and run the Setup on SQL2008 instance on target server and go to the feature selection page.
  • Select both features in the installation wizard and proceed with installation.
  • Download the design time components from the below URL and install the same.
  • http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.
  • Download and install SQL upgrade advisor to analyze the existing packages for migration issues.
  • Start the migration package wizard from SSMS to do the DTS package migration.                 Upgrade Advisor: http://www.microsoft.com/en-in/download/details.aspx?id=11455
  • Design time Components: http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Required Software Download location:
Categories: SQL SSIS

DTS Package migration steps from SQL 2000 to SQL2008

March 31, 2015 Leave a comment

  1. Run time support installation for DTS packages.
  2. Design time support installation for DTS packages.
  3. Configure SSMS for DTS packages.
  4. Migrate the DTS packages by using SSMS.These are all high level steps which includes below step by step activities.
  • Check the backward compatibilities and integration services are installed on target server or not.Check the integration service is there or not.
  • Run services.msc
  • If the above services are not installed, go to binaries location and run the Setup on SQL2008 instance on target server and go to the feature selection page.
  • Select both features in the installation wizard and proceed with installation.
  • Download the design time components from the below URL and install the same.
  • http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.
  • Download and install SQL upgrade advisor to analyze the existing packages for migration issues.
  • Start the migration package wizard from SSMS to do the DTS package migration.                 Upgrade Advisor: http://www.microsoft.com/en-in/download/details.aspx?id=11455
  • Design time Components: http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Required Software Download location:
Categories: SQL SSIS

Resolution step for the issue” No full-text supported languages found. Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed.”

March 31, 2015 1 comment

We are getting the below issue after install the full text search and here I mention the workaround.

Issue: Informational: No full-text supported languages found. Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed.

Workaround:

1st Step:SELECT @@language; then run the below command

 

sp_fulltext_service ‘update_languages’

 

If the issue is not resolved then needs to follow the below step.

After the installation completed,I went into SQL Server Configuration Manager and under the SQL Server 2005 Services detail Right Click SQL Server Full Text Search -> Properties.

  • Stop the service here.
  • Change the Built In Account to Network Service
  • Start the service.
  • Went back into SQL Server Management Studio
  • Selected properties from my database – > files -> uncheck the full-text >Then saved.
  • Then went back into the same setting and re-enabled it.
  • Then when I went to the table level – > create full-text index. I can now select a language (before I saw an empty drop-down list)
Categories: Full Text Catalog

Some Important information for Tempdb database which should be known by Each SQL DBA

March 31, 2015 Leave a comment

Tempdb Database:  It is a workspace for holding temporary objects or intermediate result sets.

 –       Temporary user objects that are explicitly created, such as: global o local temporary tables, temporary stored procedures, table variables, or cursors.

 –       Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

 –       Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

 –       Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

 Operations within Tempdb are minimally logged. This enables transactions to be rolled back. Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in Tempdb to be saved from one session of SQL Server to another.

 

Every SQL Server has a shared database named Tempdb that is for use by temporary objects. Because there is only one Tempdb database per instance, it often proves to be a bottleneck for those systems that make heavy usage of Tempdb. Typically, this happens because of PAGELATCH, in-memory latch contention on the allocation bitmap pages inside of the data files. The allocation bitmap pages are the page free space (PFS), global allocation map (GAM), and shared global allocation map (SGAM) pages in the database. The first PFS page occupies PageID 1 of the database, the first GAM page occupies PageID 2, and the first SGAM page occupies PageID 3 in the database. After the first page, the PFS pages repeat every 8088 pages inside of the data file, the GAM pages repeat every 511,232 pages (every 3994MB known as a GAM interval), and the SGAM pages repeat every 511,232 + 1 pages in the database.

When PAGELATCH contention exists on one of the allocation bitmap pages in the database, it is possible to reduce the contention on the in-memory pages by adding additional data files, with the same initial size and auto-growth configuration. This works because SQL Server uses a round-robin, proportional fill algorithm to stripe the writes across the data files. When multiple data files exist for a database, all of the writes to the files are striped to those files, with the writes to any particular file based on the proportion of free space that the file has to the total free space across all of the files: This means that writes are proportionally distributed to the files according to their free space, to ensure that they fill at the same time, irrespective of their size. Each of the data files has its own set of PFS, GAM, and SGAM pages, so as the writes move from file to file the page allocations to occur from different allocation bitmap pages spreading the work out across the files and reducing the contention on any individual page.

 

The size and physical placement of the Tempdb database can affect the performance of a system. As per the Microsoft best practice always recommended separate SSD drive for Tempdb .

To optimizing Tempdb Performance so you can learn how to avoid performance issue caused by lack of free disk space.

There are several different published suggestions for calculating the number of files used by Tempdb for the best performance. The SQL Server Customer Advisory Team (SQLCAT) team recommends that Tempdb should be created with one file per physical processor core, and this tends to be one of the most commonly quoted configuration methods for Tempdb. While this recommendation is founded in practical experience, it is important to keep in mind the types of environments that the SQLCAT team typically works, which are typical the highest volume, largest throughput environments in the world, and therefore are atypical of the average SQL Server environment. So while this recommendation might prevent allocation contention in Tempdb, it is probably overkill for most new server implementations today. Paul Randal has written about this in the past in his blog post A SQL Server DBA myth a day: (12/30) Tempdb should always have one data file per processor core where he suggests a figure of ¼ to ½ the number of cores in the server as a good starting point. This has typically been the configuration that I have followed for a number of years for setting up new servers, and I made a point of then monitoring the allocation bitmap contention of Tempdb on the actual workload to figure out if it was necessary to increase the number of files further.

At PASS Summit 2011, Bob Ward, a Senior Escalation Engineer in Product Support, presented a session on Tempdb and some of the changes that were coming in SQL Server 2012. As a part of this session Bob recommended that for servers with eight CPUs or less, start off with one file per CPU for Tempdb. For servers with more than eight CPUs Bob recommended to start off with eight Tempdb data files and then monitor the system to determine if PAGELATCH contention on the allocation bitmaps was causing problems or not. If allocation contention continues to exist with the eight files, Bob’s recommendation was to increase the number of files by four and then monitor the server again, repeating the process as necessary until the PAGELATCH contention is no longer a problem for the server. To date, these recommendations make the most sense from my own experience and they have been what we’ve recommended at SQLskills since Bob’s session at PASS.

Disk space issue at Tempdb:

Most of the issues occur because of the long running queries or reports you are executing in the SQL Server Instance and that have to use different temporary data stored in the Tempdb database.

Determining the Amount of Free Space in Tempdb

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in Tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store:

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in Tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

 

Determining the Longest Running Transaction:

 

If the version store is using a lot of space in Tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

 

SELECT transaction_id

FROM sys.dm_tran_active_snapshot_database_transactions

ORDER BY elapsed_time_seconds DESC;

 

Determining the Amount of Space Used by Internal Objects

 

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in Tempdb.

 

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount of Space Used by User Objects:

The following query returns the total number of pages used by user objects and the total space used by user objects in Tempdb.

 

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Total Amount of Space (Free and Used)

The following query returns the total amount of disk space used by all files in Tempdb.

 SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

 Monitoring Space Used by Queries

 One of the most common types of Tempdb space usage problems is associated with large queries that use a large amount of space. Generally, this space is used for internal objects, such as work tables or work files. Although monitoring the space used by internal objects tells you how much space is used, it does not directly identify the query that is using that space. The following methods help identify the queries that are using the most space in Tempdb. The first method examines batch-level data and is less data intensive than the second method. The second method can be used to identify the specific query, temp table, or table variable that is consuming the disk space, but more data must be collected to obtain the answer. if the query is not active, what you get back may not be the actual culprit).

 ;WITH task_space_usage AS (

    — SUM alloc/delloc pages

    SELECT session_id,

           request_id,

           SUM(internal_objects_alloc_page_count) AS alloc_pages,

           SUM(internal_objects_dealloc_page_count) AS dealloc_pages

    FROM sys.dm_db_task_space_usage WITH (NOLOCK)

    WHERE session_id <> @@SPID

    GROUP BY session_id, request_id

)

SELECT TSU.session_id,

       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],

       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],

       EST.text,

       — Extract statement from sql text

       ISNULL(

           NULLIF(

               SUBSTRING(

                 EST.text,

                 ERQ.statement_start_offset / 2,

                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset

                  THEN 0

                 ELSE( ERQ.statement_end_offset ERQ.statement_start_offset ) / 2 END

               ), ”

           ), EST.text

       ) AS [statement text],

       EQP.query_plan

FROM task_space_usage AS TSU

INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)

    ON  TSU.session_id = ERQ.session_id

    AND TSU.request_id = ERQ.request_id

OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST

OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP

WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL

ORDER BY 3 DESC;

 

Reference article:

https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/

https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx

http://blogs.technet.com/b/beatrice_popa/archive/2013/05/21/sql-server-tempdb-database-and-disk-space-issues.aspx

http://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log

 

Categories: TempDB