Logs for review on windows server


We can check the logs of windows server using powershell command

Command : get-eventlog -logname System -EntryType Error

Aside | Posted on by | Leave a comment

Remote Administration from Powershell


We are managing 22 servers and we need to have lot of data analysis and every time we have to login on the server and apply some of the methods to understand the data and draw some results for business.

Today we need to connect to remote power shell from our machine .

  1. Windows Powershell ISE > New Remote Poweshell Tab 
Aside | Posted on by | Leave a comment

Exporting directory Listing to excel Sheet : Powershell


We need to move some of the files which are coming as bank statements from bottomline to accounting system and some times we need be aware about the duplicate of files. In this post we just want to use powershell to export the files list of a folder to a excel sheet.

  1.  Command : dir -recurse | out-file C:\MT940.xls
Aside | Posted on by | Leave a comment

Daily Technical Activities


We are just trying to go with full speed for learning in the month of June so that we can see the technical capacity with ourself . Today i thought to create the list of activities that we are doing on daily basis in the Application Support Profile :

  1. Morning Health Checks : SOX Checks
  2. Forecast uploads on database server
  3. Resolving user issue in case they are not able to access the application with network basic network test.
  4. Checking the webserver [ IIS issues ] for the users who are using the web version of the application.
  5. Database migration from PROD to QA or DEV system.
  6. Resolving performance issues for users or a user .
  7. Release management of the new version of the software in the current environment.
  8. Integrating the PROD system with other application through FTP and SFTP as per business requirement.
  9. Deploying SQL / Application patches provided by the vendor with testing on DEV and then PROD system with change management process.
  10. Creating and Updating the SOPs which are required for the project.
  11. Automating the manual tasks as per the CSI in the project.
  12. Creating Users in application and providing adding them to the group as required as per the role and responsibility.
  13. Resolving trade issue done by the front office user for completing the first phase.
  14. Resolving the confirmation messages to be sent to MiSYS for trade confirmation.
  15. Sending Payment MT101 messages to Swift with bottom line.
  16. Checking the MT940 errors for the files importing to production system and find the solution to resolve it.
  17. MonthEnd activities need to be completed at the end of every month.
  18. Report creation as required by the treasury users .
  19. Capacity checks on the servers as data grows every month on the server.
  20. Performing UAT of other application for integration testing.

We will keep adding the activities as per our job role.

Aside | Posted on by | Leave a comment

SQL Server Profiler 101


Description
  • SQL Server  Profiler is a powerful tool that allows you to capture and analyse events, such as the execution of a stored procedure, occurring within SQL Server.
  • This information can be used to identify and troubleshoot many SQL Server-related problems.
  • basics of how Profiler works, its core terminology, and how to create and save basic Profiler traces.

One of the things that separate mediocre DBAs from exceptional ones is that exceptional DBAs know how to use the tools available to them effectively.

The SQL Server Profiler can help DBA’S:

• Monitor the activity of the:

  1. QL Server Database Engine
  2. Analysis Services -SSAS
  3. Integration Services -SSIS
  4. Reporting Services  -SSRS
  5. Monitor and identify performance-related problems with front-end application, queries,T-SQL, transactions, and much more
  6. Monitor and identify SQL Server errors and warning
  7. Audit user activity
  8. Analyse trace results by grouping or aggregating them
  9. Create your own custom traces, and save them, for use whenever you wish

We will also cover profiler terminology like:
• Events
• Data columns
• Filters
•Trace

Also we will be working with traces and templates and learn:
• How to build custom traces from scratch
• How to Save Profiler traces to a SQL Server Table
• How to trace Analysis Services activity

After the end of this course , you will know why Profiler is such an essential tool in the DBA’s armoury, and will Understand

  1. how Profiler works
  2. Be familiar with core Profiler terminology, such as events, data columns, filters and traces
  3. Understand the permissions required to use Profiler and how to start up Profiler
  4. Be able to create and control basic Profiler traces and then save a completed trace to a file.
Aside | Posted on by | Leave a comment

Audit sensitive data to see who did what, when, where, and how ?


We need to have information about whats going on our SQL server and

  • What is being changed ?
  • When it was changed ?
  • Where its changed ?
  • How its changed ?

These all questions need to be explored to understand the auditing of SQL Server. As we know the financial data is critical for the regulatory requirements and it has to be audit with proper reports of a financial report of the company.

We were not aware about the standard used for audit purpose . But some of them are as follows :

  1. PCI DSS [ Payment Card Industry Data Security Standard ] 
  • Its designed to ensure All companies ensures security standard  that accepts, , process, store or transmit credit card information maintain a secure environment. 
  • https://www.pcisecuritystandards.org/ 

 

2. HIPAA  [  Health Insurance Portability and Accountability Act ] 

  • The Health Insurance Portability and Accountability Act of 1996 (HIPAA) required the Secretary of the U.S. Department of Health and Human Services (HHS) to develop regulations protecting the privacy and security of certain health information.1 To fulfill this requirement, HHS published what are commonly known as the HIPAA Privacy Rule and the HIPAA Security Rule. The Privacy Rule, or Standards for Privacy of Individually Identifiable Health Information, establishes national standards for the protection of certain health information. The Security Standards for the Protection of Electronic Protected Health Information (the Security Rule) establish a national set of security standards for protecting certain health information that is held or transferred in electronic form. The Security Rule operationalizes the protections contained in the Privacy Rule by addressing the technical and non-technical safeguards that organizations called “covered entities” must put in place to secure individuals’ “electronic protected health information” (e-PHI). Within HHS, the Office for Civil Rights (OCR) has responsibility for enforcing the Privacy and Security Rules with voluntary compliance activities and civil money penalties.
  • https://www.hhs.gov/hipaa/for-professionals/security/laws-regulations/ 

3. FERPA [Family Educational Rights and Privacy Act ] 

  • The Family Educational Rights and Privacy Act (FERPA) (20 U.S.C. § 1232g; 34 CFR Part 99) is a Federal law that protects the privacy of student education records. The law applies to all schools that receive funds under an applicable program of the U.S. Department of Education.
  • https://ed.gov/policy/gen/guid/fpco/ferpa/index.html

4. SOX  [ Sarbanes-Oxley (SOX) Compliance ] 

  • The Sarbanes-Oxley Act (SOX) requires that all publicly held companies must establish internal controls and procedures for financial reporting to reduce the possibility of corporate fraud. Your entire IT infrastructure–from server and network security to IT practices and operations–must be reinforced and configured to maintain and demonstrate compliance in the event of an audit.
  • http://www.soxlaw.com/introduction.htm

 

Now what needs to be audited for SOX requirement in SQL server and what not we need to learn more about it. We will keep posting on it in future post.

Aside | Posted on by | Leave a comment

Using the SQL Server Profiler


These SQL Server Profiler event classes can be used to help you find out if SQL Server is running as efficiently as it can. They include:

  • Execution Warnings: For very busy servers, it is possible that a query may have to wait a short time before there are available resources for it to run. This event class can be watched to find out if and how long queries have to wait for needed resources. This event class has two possible values. “Query Wait” is used to indicate how often a query has to wait. “Query Time-Out” is used to indicate how often a query has timed out waiting for the necessary resources. Obviously, you don’t want to see either of these events. If you do, and they occur regularly, then you need to reduce the load on your server, beef up the server hardware, rewrite the queries, select more appropriate indexes to resolve the problem, or some combination of all these actions.
  • Hash Warning: This event is used to measure hash recursions or hash bails that have occurred on your server. A hash recursion (event 0) happens when the input of the query does not fit entirely into memory, forcing SQL Server to split the input into multiple parts, which are then processed individually. A hash bail(event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion depth, which forces the query to run under an alternative query plan, one that is much less than optimal. Think of a hash bail as a hash recursion’s worst nightmare. As you can imagine, these two events can really slow down a query. Some options to correct these problem events include: insure that the Index Statistics are up-to-date, rewrite the query, experiment with optimizer hints, or add more RAM to the server.While the purpose of this warning is to let you know when hash operations go awry, it also serves another purpose. If you want optimum performance, you want to avoid hash operations of any type, even ones that work properly. So if you identify any hash warnings, you might want to try to find out why a hash operation is being performed in the first place, and try to fix this problem. This of course, will also fix your hash warning problem.
  • Missing Column Statistics: Tells you which columns in your query are missing statistics, such as the Index Statistics used by the Query Optimizer to help it evaluate the most efficient query plan. If one or more columns of statistics are missing, the Query Optimizer may not select the best query plan, hurting performance. To help prevent this problem, consider the following: Be sure you have “auto create statistics” turned on for your database, use the CREATE STATISTICS statement to manually create the statistics on the missing columns, or use the Index Tuning Wizard or Database Engine Tuning Advisor to identify and automatically create the needed statistics.
  • Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. To fix this, add a join predicate.
  • Sort Warnings: Tells you if sort operations can be done completely in memory or if the sorting operation has to be divided into two or more steps in order to complete. Obviously, a one step operation will be much faster. Potential solutions to this problem include reducing the number of rows to be returned and sorted, reducing the number of columns being sorted, getting rid of the sort, or adding more RAM to the server.

If you haven’t done so already, trace these events on one of your busier databases and see what’s going on.

Aside | Posted on by | Leave a comment