How To: Export CSV in SQL Server 2008

On one of my earlier posts I explained how to send attachment in SQL Server 2008. The attachment in my case would be a csv exported from the execution of a stored procedure. I used bcp utility command from within SSMS. Since there is "bulk insert" command in SQL server but no bulk export command, I used the bcp utility which has been around ever since the early days of SQL server. So in my case, the query would look like:

sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO

declare @sql varchar(8000)

select @sql = 'bcp "set fmtonly off exec MyDB..sp_getAdminAllUserStats ''2009-12-01'',''2009-12-31''" queryout c:\Reports\report.csv -c -t, -T'

exec master..xp_cmdshell @sql

In order to use xp_cmdshell stored procedure, I would first need to enable it. The syntax for bcp is explained in detail in http://msdn.microsoft.com/en-us/library/ms188365.aspx. In case you are wondering the use of "set fmtonly off" , without using this you will get a " [Microsoft][ODBC SQL Server Driver]Function sequence error". The "queryout" is used since I am using a query. "-c" is used to specify character type and it is faster than using -n (native type). "-t," is where you transform your export as csv because each column's field terminator will be a comma. To use the current trusted authentication -T is used. Finally, the last statement of the stored procedure needs to be a select statement.

That is all. Thank you for taking your time to read.

How to: Send email with attachment from SQL Server 2008 Enterprise Edition

Some time back, I had a problem with exporting huge amount of data as csv to view in excel from a production server. It took several minutes when the server load was normal and far worse when it experienced peak traffic. My client asked me if I could take this feature off of the live server and then automate this process so that he would receive the exported data in the mail instead of requesting data from me. This meant three things, creating a sql job that executed once a week which executed an export script using 'bcp' feature to a file which would then be sent as an attachment to the client. I will explain how to configure SMPT mail server and send email in SQL Server 2008 Enterprise Edition as a two part series.

This is the second of the two part series and here I would like to show how to send email with attachment from SQL Server 2008 Enterprise Edition. If you'd like to follow the first part, here is the link http://dreamfusions.blogspot.com/2010/02/how-to-configure-smtp-mail-server-in.html.

  • Open SQL Server Management Studio (SSMS) and login either using your Windows Authentication or user credentials.
  • Once there, if you don't already see the "Object Explorer" hit F8 to open it.
  • Expand the "Management" folder and right click on "Database Mail".
  • Select "Configure Database Mail".
  • You will need to first create a new profile. To do this, select the first radio option that reads "Set up Database Mail by performing the following tasks."
  • Give a Profile Name and a short description. The Profile Name is important to send emails.
  • Then click on "Add" button to add SMTP server account you configured in part I of this series.
  • Fill out the necessary items. Leave the SMPT port to 25. Enter 127.0.0.1 as your server name. Also if you have Windows Authentication, select that or enter the login you used earlier.
  • Now you are done with profile and mail server account.
  • You can now test by right clicking on Database Mail and clicking on Send Test email.
  • To verify use:
    SELECT * FROM sysmail_sentitems --to view sent items
SELECT * FROM sysmail_faileditems --to view failed items
SELECT * FROM sysmail_log --to view the reason why your mail was not sent among other things.

  • Now to manually send email (this is our goal), you need to first reconfigure the Database Mail to enable it. To do this run the following scripts.
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
sp_CONFIGURE 'Database Mail XPs' 1
GO
RECONFIGURE
  • You are now ready to send email manually!! The sample script sends email with attachment. You use the msdb database and the profile you created should be entered.
[USE msdb]
GO
EXEC sp_send_dbmail
@profile_name='myMailProfile',
@recipients='tejaswi.rana@gmail.com',
@subject='Sending message from SQL Server 2008',
@body='You have received mail from SQL Server',
@file_attachments ='c:\Reports\report.csv'

That's all there is to it. Thank you for taking your time to read this.







How to: Configure SMTP mail server in Windows Server 2008 and IIS 6.0

Some time back, I had a problem with exporting huge amount of data as csv to view in excel from a production server. It took several minutes when the server load was normal and far worse when it experienced peak traffic. My client asked me if I could take this feature off of the live server and then automate this process so that he would receive the exported data in the mail instead of requesting data from me. This meant three things, creating a sql job that executed once a week which executed an export script using 'bcp' feature to a file which would then be sent as an attachment to the client. I will explain how to configure SMPT mail server and send email as a two part series.

This is the first of the two part series where I would like to show how to configure SMTP mail server in Windows Server 2008. The link to the second part is http://dreamfusions.blogspot.com/2010/02/how-to-send-email-with-attachment-from.html.

  • From the Start Menu, navigate to "Administrative Tools" and select "Server Manager".
  • From the "Features Summary" click on "Add Features".
  • Select "SMTP Server" and click on Install. Accept all changes.
  • Now from "Administrative Toos" , select "Internet Information Services (IIS) 6.0 Manager".
  • Right click on "SMTP Virtual Severs" and click on properties.
  • Navigate to "Access" tab and click on "Relay" button.
  • Leave the "Only the list below" radio button clicked and click on "Add" button.
  • Leave the "Single computer" option selecte and enter 127.0.0.1 as your IP address.
  • Now click apply and you are almost done.
  • Right click on "SMTP Virtual Severs" and click on start.
This is it. Now you have SMPT server configured and running!! Follow the next part in this two part series to send mail via SQL Server 2008 Enterprise Edition.

Think again before using ntext, text and image data types in MS SQL while doing new development.

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

For more information please follow this link.

20 Best ways to Secure your Apache WebServer Hosting Configuration

Here are 20 things you can do to make your apache configuration more secure.
Disclaimer: The thing about security is that there are no guarantees or absolutes. These suggestions should make your server a bit tighter, but don't think your server is necessarily secure after following these suggestions.
Additionally some of these suggestions may decrease performance, or cause problems due to your environment. It is up to you to determine if any of the changes I suggest are not compatible with your requirements. In other words proceed at your own risk.
#1. First, make sure you've installed latest security patches
There is no sense in putting locks on the windows, if your door is wide open. As such, if you're not patched up there isn't really much point in continuing any longer on this list. Go ahead and bookmark this page so you can come back later, and patch your server.
#2. Hide the Apache Version number, and other sensitive information.
By default many Apache installations tell the world what version of Apache you're running, what operating system/version you're running, and even what Apache Modules are installed on the server. Attackers can use this information to their advantage when performing an attack. It also sends the message that you have left most defaults alone.
There are two directives that you need to add, or edit in your httpd.conf file:ServerSignature Off
ServerTokens Prod
The ServerSignature appears on the bottom of pages generated by apache such as 404 pages, directory listings, etc.
The ServerTokens directive is used to determine what Apache will put in the Server HTTP response header. By setting it to Prod it sets the HTTP response header as follows:Server: Apache
If you're super paranoid you could change this to something other than "Apache" by editing the source code, or by using mod_security (see below).
#3. Make sure apache is running under its own user account and group
Several apache installations have it run as the user nobody. So suppose both Apache, and your mail server were running as nobody an attack through Apache may allow the mail server to also be compromised, and vise versa.User apache
Group apache
#4. Ensure that files outside the web root are not served
We don't want apache to be able to access any files out side of its web root. So assuming all your web sites are placed under one directory (we will call this /web), you would set it up as follows:
Order Deny,Allow
Deny from all
Options None
AllowOverride None


Order Allow,Deny
Allow from all


Note that because we set Options None and AllowOverride None this will turn off all options and overrides for the server. You now have to add them explicitly for each directory that requires an Option or Override.
#5. Turn off directory browsing
You can do this with an Options directive inside a Directory tag. Set Options to either None or -IndexesOptions -Indexes
#6. Turn off server side includes
This is also done with the Options directive inside a Directory tag. Set Options to either None or -IncludesOptions -Includes
#7. Turn off CGI execution
If you're not using CGI turn it off with the Options directive inside a Directory tag. Set Options to either None or -ExecCGIOptions -ExecCGI
#8. Don't allow apache to follow symbolic links
This can again can be done using the Options directive inside a Directory tag. Set Options to either None or -FollowSymLinksOptions -FollowSymLinks
#8. Turning off multiple Options
If you want to turn off all Options simply use:Options None
If you only want to turn off some separate each option with a space in your Options directive:Options -ExecCGI -FollowSymLinks -Indexes
#9. Turn off support for .htaccess files
This is done in a Directory tag but with the AllowOverride directive. Set it to None.AllowOverride None
If you require Overrides ensure that they cannot be downloaded, and/or change the name to something other than .htaccess. For example we could change it to .httpdoverride, and block all files that start with .ht from being downloaded as follows:AccessFileName .httpdoverride

Order allow,deny
Deny from all
Satisfy All

#10. Run mod_security
mod_security is a super handy Apache module written by Ivan Ristic, the author of Apache Security from O'Reilly press.
You can do the following with mod_security:
Simple filtering
Regular Expression based filtering
URL Encoding Validation
Unicode Encoding Validation
Auditing
Null byte attack prevention
Upload memory limits
Server identity masking
Built in Chroot support
And more
#11. Disable any unnecessary modules
Apache typically comes with several modules installed. Go through the apache module documentation and learn what each module you have enabled actually does. Many times you will find that you don't need to have the said module enabled.
Look for lines in your httpd.conf that contain LoadModule. To disable the module you can typically just add a # at the beginning of the line. To search for modules run:grep LoadModule httpd.conf
Here are some modules that are typically enabled but often not needed: mod_imap, mod_include, mod_info, mod_userdir, mod_status, mod_cgi, mod_autoindex.
#12. Make sure only root has read access to apache's config and binaries
This can be done assuming your apache installation is located at /usr/local/apache as follows:chown -R root:root /usr/local/apache
chmod -R o-rwx /usr/local/apache
#13. Lower the Timeout value
By default the Timeout directive is set to 300 seconds. You can decrease help mitigate the potential effects of a denial of service attack.Timeout 45
#14. Limiting large requests
Apache has several directives that allow you to limit the size of a request, this can also be useful for mitigating the effects of a denial of service attack.
A good place to start is the LimitRequestBody directive. This directive is set to unlimited by default. If you are allowing file uploads of no larger than 1MB, you could set this setting to something like: LimitRequestBody 1048576
If you're not allowing file uploads you can set it even smaller.
Some other directives to look at are LimitRequestFields, LimitRequestFieldSize and LimitRequestLine. These directives are set to a reasonable defaults for most servers, but you may want to tweak them to best fit your needs. See the documentation for more info.
#15. Limiting the size of an XML Body
If you're running mod_dav (typically used with subversion) then you may want to limit the max size of an XML request body. The LimitXMLRequestBody directive is only available on Apache 2, and its default value is 1 million bytes (approx 1mb). Many tutorials will have you set this value to 0 which means files of any size may be uploaded, which may be necessary if you're using WebDAV to upload large files, but if you're simply using it for source control, you can probably get away with setting an upper bound, such as 10mb:LimitXMLRequestBody 10485760
#17.Limiting Concurrency
Apache has several configuration settings that can be used to adjust handling of concurrent requests. The MaxClients is the maximum number of child processes that will be created to serve requests. This may be set too high if your server doesn't have enough memory to handle a large number of concurrent requests.
Other directives such as MaxSpareServers, MaxRequestsPerChild, and on Apache2 ThreadsPerChild, ServerLimit, and MaxSpareThreads are important to adjust to match your operating system, and hardware.
#16. Restricting Access by IP
If you have a resource that should only by accessed by a certain network, or IP address you can enforce this in your apache configuration. For instance if you want to restrict access to your intranet to allow only the 176.16 network:
Order Deny,Allow
Deny from all
Allow from 176.16.0.0/16
Or by IP:Order Deny,Allow
Deny from all
Allow from 127.0.0.1
#17. Adjusting KeepAlive settings
According to the Apache documentation using HTTP Keep Alive's can improve client performance by as much as 50%, so be careful before changing these settings, you will be trading performance for a slight denial of service mitigation.
KeepAlive's are turned on by default and you should leave them on, but you may consider changing the MaxKeepAliveRequests which defaults to 100, and the KeepAliveTimeout which defaults to 15. Analyze your log files to determine the appropriate values.
#18. Run Apache in a Chroot environment
chroot allows you to run a program in its own isolated jail. This prevents a break in on one service from being able to effect anything else on the server.
It can be fairly tricky to set this up using chroot due to library dependencies. I mentioned above that the mod_security module has built in chroot support. It makes the process as simple as adding a mod_security directive to your configuration:SecChrootDir /chroot/apache
There are however some caveats however, so check out the docs for more info.

Webserver Performance Benchmarks

You can benchmark Apache, IIS and other web server with apache benchmarking tool called ab. Recently I was asked to performance benchmarks for different web servers.

It is true that benchmarking a web server is not an easy task. From how to benchmark a web server.

What is important is the average time it will take when you have a maximum number of users on your site simultaneously. Another important thing is how much more time it will take when there are 2 times more users: a server that take 2 times more for 2 times more users is better than another that take 4 times more for the same amount of users."

Here are few tips to carry out procedure along with an example:
Apache Benchmark Procedures

* You need to use same hardware configuration and kernel (OS) for all tests
* You need to use same network configuration. For example, use 100Mbps port for all tests
* First record server load using top or uptime command
* Take at least 3-5 readings and use the best result
* After each test reboot the server and carry out test on next configuration (web server)
* Again record server load using top or uptime command
* Carry on test using static html/php files and dynamic pages
* It also important to carry out test using the Non-KeepAlive and KeepAlive (the Keep-Alive extension to provide long-lived HTTP sessions, which allow multiple requests to be sent over the same TCP connection) features
* Also don't forget to carry out test using fast-cgi and/or perl tests

How do I carry out Web server Static KeepAlive test?
$ ab -k -n 1000 -c 5 http://202.54.200.1/snkpage.html