Archive for the ‘Tricks & Tips’ Category
Here are some of the SQL Server database deployment tips that have been very useful to me.
1. Store all your database object scripts as individual files under source control. This provides an excellent way to keep the database code organized and will also be very useful when auditing code changes. Another advantage would be that these scripts can be used to creating an instance of an application database with minimal amount of data.
2. Maintain a senior resource as a single point of contact to handle all SQL changes in non-DEV environments. This resource could also be responsible for code reviews and helping with code optimization. This provides an excellent way for enforce organizational best practices and it will also make it simple to keep track of changes in a given database environment.
3. Compare the database which is getting deployed with the last database that got updated in the software factory line. Comparing the schema and/or data will give you a contemporary view of the database state, thus helping you with a diligent way of preparing your deployment scripts. You can use tools like Redgate SQL-Compare, dbcomparer, etc for this purpose.
4. Make sure that the SQL scripts always successfully pass through the development, QA and stage environments before reaching the production/live environment. On a side note data in the production should be replicated (after scrubbing is necessary) to all the non-production environments as frequently as possible to achieve best level of code compatibility.
5. Always take a backup of the database, turn off replication agents and all SQL jobs, before deploying to the production/staging environment. This might add some overhead, but making this a habit will help with a error free, faster and a easy to restore deployment.
6. This may seem trivial by it is good practice to end every SQL Script with “GO”. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad-hoc session or script if this is the first GO .
7. Maintain the deployment scripts with a naming convention. This will help in organizing the scripts for executing them in predetermined sequence and will give you a good idea about what a script is intended to do. I usually name my files as follows: naming the scripts with number in the following format give established the order of execution and also give you an opportunity to add new script(s) in between any two scripts.
8. It is good practice to sandwich your SQL script in between a Transaction and Rollback/Commit block. One exception to this rule (if you will) would be when you have a script that performs a huge number of data manipulation operations. This could some times cause the transaction log to grow really big and cause the server to run out of space.
9. Create data validation scripts for the deployment changes. This way you do not have to wait for the application testing to respond to any errors.
10. I have used script consolidation for some of the deployments I was involved with. File consolidation helps in saving time when you have to execute a ton of script files. Here is the code for the SQL Merge tool.
11. Prepare a deployment document for establishing and organizing the deployment process with the following information; this will be useful with maintaining compliance with the Sarbanes-Oxley Act .
- Deployment Schedule
- Activity ownership
- Pre-deployment activities
- Deployment activities
- Post-deployment activities
- Rollback plan
- Contact list
For sometime now, the ASP.NET development server (VS 2010 + Windows 7) on my development laptop has slowed down so much that it takes about 5 minutes for the initial page to load. The CPU Usage and RAM consumption did not look anything out of the ordinary. I tried many things to speed up the process and here are some of them:
1. Turned off Intellisense,
2. Minimized active background services.
3. Disk defragmentation
4. Run VS2010 as in administrator mode.
5. Deleted unused software.
6. Turned off anti-virus software (not a good idea, I know)
7. and a few more not so appealing ones
8. Changed localhost to 127.0.0.1
None of them worked and the only thing that saved me from the brink of going insane was, me getting an upgraded laptop with more of every thing (8GB Ram, I-5 CPU M 520 @ 2.4 Ghz, 2 Cores, 4 Logical Processors). Things started to look nice and bright after the upgrade. My builds are faster, debugging is fun and me thinks this will help me be more productive.
I wonder if the minimum system requirements as mentioned by MS for VS 2010 are not quite adequate. Go get your self a fast one if possible…
I was hit with slowness on VS2010 caused by Team Foundation Server (TFS) on another project on a laptop that is as fast the one described above. The difference in environment from the above was that we use TFS for source control.
Problem: TFS was extremely slow when I remote to work form home and it was fine when I was using the company network.
Solution: A colleague at work actually helped me identify this fix that is the actual fix to the slowness of VS2010 (win7 + TFS). Refer to Slow connection when connecting to Team Foundation Server from Visual Studio 2010 to get rid of the slowness.
I had to check to see if the mail server was live or dead and had to Telnet the server: telnet <server_name> 25. I have noticed the that Telnet client is not enabled in the default setup of Windows 7. Following are the steps I went though to enable telnet:
1. Click Start
2. Control Panel
3. Programs and Features.
4. Turn Windows features on and off
5. Check Telnet Client.
Web application slowness in different environments in a software factory-line is something that we all must have come-across at some point or the other. Here is my perspective on “where to begin” to address this issue in a general sense for a Microsoft centric web application. Let as assume that our application was developed using ASP.Net and a SQL Server database.
1. Go though the Event Viewer log for any errors, warnings and informational messages. Watch out for messages that were logged by your application and another other applications on your web server.
2. Check the IIS logs to see if there is any unusual response rate i.e. errors (http 500, 404, etc).
3. The application pool in IIS can be a source of the slowness of the application.
4. The web server could have ran out of disk space (lack of error log rolling and backup service).
5. IIS crash because of a memory leak, thread locking, etc. Check out Troubleshooting an IIS Crash/Hang
7. Make sure that the web server is up-to-date with all the latest “software patches” 😉 (oops !!! service packs)
8. Make sure that the database connection pool settings are correct.
9. Consider rendering the website content using a content delivery network (CDN) service provider like Akamai, Amazon CloudFront, Microsoft Azure, AT&T, etc.
10. If none of the above seems to cause an issue then read-on.
1. Analyzing the web request and the web response across multiple pages of the application using tools like Charles, Fiddler, Firebug, etc can provide you lot of information that you would not know otherwise.
2. Narrow down the scope of the slowness in the page execution.
3. Not disposing objects after use can eat up lot of resources on the web server causing the slowness.
4. Make sure that the response times of all the Ajax, web service calls are in line with the expectations.
5. Run extended load tests to determine if there might be a new cause of failure, that might not have been noticed during regular load tests.
6. Always employ best practices for implementing web site acceleration .
7. Consider fetching multiple result sets in one database call as opposed to one result set per database call. This will reduce the number of round trips to the database.
8. If none of the above seems to cause an issue then read-on.
1. Low disk space on the SQL Server/Cluster.
2. Not following SQL Server best practices.
3. Go though the execution plans of the various suspect SQL scripts/statements to isolate the issue. Table scan can be a very costly operation as opposed to a index scan.
4. Run a SQL Trace for a few hours in an environment with lot of traffic and feed the trace file to the SQL Server Performance Tuning wizard.
5. Apply the recommendations of the SQL Server Performance Tuning wizard to the database to see if that helps.
6. Verify and make sure that the background SQL, SSIS and SSRS tasks are scheduled to run during off-peak hours.
7. Considering breaking down a huge database into smaller ones. as an example an e-commerce website should be accessing data from a Catalog, Marketing, Sales and Audit databases instead of one big database.
8. Consider regular archiving and cleanup of historical data from all databases.
9. Index defragmenting  can improve the SQL execution times too.
10. Sorting of huge record sets might be best left to he done at the application level than at the database level. This could be controversial depending on who you speak to. With this kind of a solution chances are that a modern day beefed-up web server in a web farm(load balanced environment) should be able to handle expensive data operations. This would conserve SQL Server processing time to handle more requests.
11. I hope your issue might have been resolved by now.
I will update this blog, as and when I can think of other ways to help the cause.
Good luck and happy programing !
Also find this post on CodeProject
Here is a neat trick from Ram  to enable the security tab in the properties dialog of a folder in WinXP. The security tab is hidden by default in Win XP. You can enable the security tab by following these steps:
1. Open Windows Explorer, and choose Folder Options from the Tools menu.
2. On the View tab, scroll to the bottom of the Advanced Settings and clear (click) the check box next to “Use Simple File Sharing.”
3. Click OK to apply the change, and you should now have a Security tab when viewing the properties of a file on an NTFS volume.