We are working on database from quit long but we know very few things about the database operations managements. We will try to cover below topics in this exercise:
- Automating SQL server management
- Working with SQL server Agent
- Managing SQL server with Powershell
- Monitor SQL server
- SQL server Maintenance.
We can check the logs of windows server using powershell command
Command : get-eventlog -logname System -EntryType Error
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 .
- Windows Powershell ISE > New Remote Poweshell Tab
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.
- Command : dir -recurse | out-file C:\MT940.xls
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 :
- Morning Health Checks : SOX Checks
- Forecast uploads on database server
- Resolving user issue in case they are not able to access the application with network basic network test.
- Checking the webserver [ IIS issues ] for the users who are using the web version of the application.
- Database migration from PROD to QA or DEV system.
- Resolving performance issues for users or a user .
- Release management of the new version of the software in the current environment.
- Integrating the PROD system with other application through FTP and SFTP as per business requirement.
- Deploying SQL / Application patches provided by the vendor with testing on DEV and then PROD system with change management process.
- Creating and Updating the SOPs which are required for the project.
- Automating the manual tasks as per the CSI in the project.
- Creating Users in application and providing adding them to the group as required as per the role and responsibility.
- Resolving trade issue done by the front office user for completing the first phase.
- Resolving the confirmation messages to be sent to MiSYS for trade confirmation.
- Sending Payment MT101 messages to Swift with bottom line.
- Checking the MT940 errors for the files importing to production system and find the solution to resolve it.
- MonthEnd activities need to be completed at the end of every month.
- Report creation as required by the treasury users .
- Capacity checks on the servers as data grows every month on the server.
- Performing UAT of other application for integration testing.
We will keep adding the activities as per our job role.
- 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:
- QL Server Database Engine
- Analysis Services -SSAS
- Integration Services -SSIS
- Reporting Services -SSRS
- Monitor and identify performance-related problems with front-end application, queries,T-SQL, transactions, and much more
- Monitor and identify SQL Server errors and warning
- Audit user activity
- Analyse trace results by grouping or aggregating them
- Create your own custom traces, and save them, for use whenever you wish
We will also cover profiler terminology like:
• Data columns
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
- how Profiler works
- Be familiar with core Profiler terminology, such as events, data columns, filters and traces
- Understand the permissions required to use Profiler and how to start up Profiler
- Be able to create and control basic Profiler traces and then save a completed trace to a file.
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 :
- 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.
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.
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.
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.
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.