Config Server Firewall (csf) and Login Failure Daemon (lfd) is a robust firewall solution having Stateful Packet Inspection (SPI), Login/Intrusion Detection and Security application for Linux servers. Although it is more compatible with CPanel we have been able to use the same for the Plesk hosting control panel also and it is running fine. Please visit the below link for more information.

http://www.configserver.com

I have listed the installation steps for CSF / LFD.Login to your server with ‘root’ user and issue below commands :

Change directory to either /root or /usr/local/src , which ever you normally use for such installations

# cd /usr/local/src

[Remove any old source that might be present]

Download and untar the source for installation

# wget http://www.configserver.com/free/csf.tgz
# tar -xzf csf.tgz

Run installation script

# cd csf
# sh install.sh

Once the installation complete, you can run the below scripts provided by vendor to check if your server/vps has required iptables modules available :

# perl /etc/csf/csftest.pl

CSF provides the script to remove the other popular combination I talked about above i.e. apf/bfd, The below script will remove apf/bfd from your server/vps.

# sh /etc/csf/remove_apf_bfd.sh

Common setting for incoming/outgoing TCP/IP and UDP connection.

ETH_DEVICE = “eth1″

ETH_DEVICE_SKIP = “eth0″

# Allow incoming TCP ports
TCP_IN = “20,21,25,53,80,106,110,111,143,443,465,587,865,873,993,995,8443,8880″

# Allow outgoing TCP ports
TCP_OUT = “20,21,22,25,80,110,443,43,873,8443″

# Allow incoming UDP ports
UDP_IN = “53,111,123,230,631,859,862,2109,5353″

# Allow outgoing UDP ports
# To allow outgoing traceroute add 33434:33523 to this list
UDP_OUT = “20,21,53,113,123,2109″

# Allow incoming PING
ICMP_IN = “1″

# Set the per IP address incoming ICMP packet rate
# To disable rate limiting set to “0″
ICMP_IN_RATE = “0″

# Allow outgoing PING
ICMP_OUT = “1″

# Set the per IP address outgoing ICMP packet rate
# To disable rate limiting set to “0″
ICMP_OUT_RATE = “0″

# Enable login failure detection daemon (lfd).
LF_DAEMON = “1″

For allowing Qmail in CSF alter below setting(s)

SMTP_BLOCK = “1″
SMTP_ALLOWLOCAL = “1″
SMTP_PORTS = “25,587″
SMTP_ALLOWUSER = “qmaild,qmaill,qmailp,qmailq,qmailr,qmails”
SMTP_ALLOWGROUP = “qmail,nofiles,mail,mailman”

Set CSF/LFD reporting FROM/TO ID as below [**** Need to set for Plesk]

LF_ALERT_TO = “supportteam@diadem.co.in”
LF_ALERT_FROM = “csf_LFD@diadem.co.in”

Allowing third party block list checking

# Enable IP range blocking using the DShield Block List at
LF_DSHIELD = “86400″

# Enable IP range blocking using the Spamhaus DROP List at
LF_SPAMHAUS = “86400″

# Enable IP range blocking using the BOGON List at
LF_BOGON = “86400″

Now Add the LFD ignore list for qmail/plesk mail user/process in csf.pignore file.

# vim /etc/csf/csf.pignore

#### Custom for Plesk ####
user:admin
exe:/var/qmail/bin/qmail-smtpd
exe:/usr/bin/imapd
exe:/var/qmail/bin/qmail-queue
exe:/usr/bin/pop3d
exe:/var/qmail/bin/qmail-send
cmd:qmail-send
cmd:/usr/bin/pop3d Maildir
cmd:/var/qmail/bin/qmail-queue
cmd:/var/qmail/bin/qmail-smtpd /var/qmail/bin/smtp_auth /var/qmail/bin/true /var/qmail/bin/cmd5checkpw /var/qmail/bin/true
cmd:/usr/bin/imapd Maildir
exe:/var/qmail/bin/qmail-rspawn
cmd:qmail-rspawn
exe:/var/qmail/bin/qmail-clean
cmd:qmail-clean
exe:/usr/sbin/clamd
cmd:clamd
exe:/var/qmail/bin/splogger
cmd:splogger qmail
exe:/var/qmail/bin/qmail-remote.moved
user:qmaill
user:popuser
user:qmaild
user:qmails
user:qmailr
user:qmailq
user:qscand
exe:/usr/sbin/avahi-daemon
user:avahi
exe:/usr/local/sbin/zabbix_agentd
cmd:/usr/local/sbin/zabbix_agentd
user:zabbix
exe:/usr/bin/sw-engine-cgi
cmd:/usr/bin/sw-engine-cgi
user:sso
exe:/usr/sbin/sw-cp-serverd
cmd:/usr/sbin/sw-cp-serverd -f /etc/sw-cp-server/config
user:sw-cp-server
exe:/usr/bin/sw-engine-cgi
cmd:/usr/bin/sw-engine-cgi -c /usr/local/psa/admin/conf/php.ini -d auto_prepend_file=auth.php3 -u psaadm
user:psaadm
exe:/usr/libexec/mysqld
cmd:/usr/libexec/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –socket=/var/lib/mysql/mysql.sock
user:mysql
exe:/usr/libexec/hald-addon-acpi
exe:/usr/sbin/hald
cmd:hald
user:haldaemon
exe:/usr/bin/postgres
user:postgres
exe:/sbin/portmap
cmd:portmap
user:rpc
exe:/usr/bin/xfs
cmd:xfs -droppriv -daemon
user:xfs
exe:/usr/bin/python
cmd:/usr/bin/python /usr/lib/mailman/bin/qrunner –runner=VirginRunner:0:1 -s
user:mailman
exe:/usr/java/jdk1.6.0_20/bin/java
user:tomcat

Note: You may need to add few more process/user as per your requirement.

Now start the CSF

# csf -s

Restart LFD

# service lfd restart

Installation is done, now check the website, mail  and other services(s) and disable TESTING mode and restart CSF/LFD

# csf -r
# service lfd restart

I will list below some of very common commands you will need to use/manage csf firewall :

Enabling the firewall

# csf –enable OR
# csf -e

Disabling the firewall

# csf –disable
# csf -x

Starting firewall / applying rules

# csf –start
# csf -s

Stopping firewall / flushing rules

# csf –stop
# csf -f

Adding an IP in firewall

# csf -d 2.3.4.5 “Reason for blocking the IP”
# csf –deny 2.3.4.5 “Reason for blocking the IP”
where 2.3.4.5 is the IP you want to block.

Removing IP from deny list

# csf -dr 2.3.4.5

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
 Loading ... Loading ...
Posted by ankur under Linux, Plesk Control Panel
0 Comments

When you export a VM, a complete copy of the VM (including disk images) is stored as a single file on your local machine, with a .xva file extension. The VM export/import
feature can be used in a number of different ways:

  • As a convenient backup facility for your VMs. An exported VM file can be used to recover an entire VM in the event of disaster.
  • As a way of quickly copying a VM, for example, a special-purpose server configuration that you use many times. You simply configure the VM the way you want it, export it, and then import it to create copies of your original VM.
  • As a simple method for moving a VM to another server.

Note It may not always be possible to run an imported VM that was exported from another server with a different CPU type. For example, a Windows VM created on a server with an Intel® VT Enabled CPU, then exported, may not run when imported to a server with an AMD-VTM CPU.

To export a VM:

  1. If the VM you want to export is running, you must shut it down first. See the image below:
  2. In the Resources pane, select the VM, right-click, and then click Export as Backup on the shortcut menu. Alternatively, on the VM menu, click Export as Backup.
  3. Enter a name for the export file and specify the folder where you want it to be saved.
  4. To have the export file verified, select the Verify export on completion check box at the bottom of the dialog box. Be aware that verification may take some time.
  5. Click Save to begin exporting the file.
  6. The export progress is displayed in the status bar at the bottom of the XenCenter window and on the Logs tab. To cancel an export in progress, click on the Logs tab, find the export in the list of events, and click the Cancel button.

Exporting a VM may take some time, depending on its size and the speed and bandwidth of the network connection between the server and XenCenter.

To import an external VM in XenCenter, you use the Import wizard: select a server or pool in the Resources pane, right-click, and then select Import VM on the shortcut menu, and follow the wizard steps.

  1. Import source. Enter the location of the exported VM in the Import file name box and ensure that the Exported VM option button is selected. Click Next to continue.
  2. Home server. Select a home server for the new VM, then click Next to continue.
  3. Storage. Select a storage repository where virtual disks for the new VM will be stored, then click Import to begin the import process.
  4. Network. Configure the virtual network interfaces for the imported VM. The default network interfaces are listed on this page; you can add, modify, or delete virtual network interfaces as required. When you are happy with the new VM’s network configuration, click Next to move to the final wizard page.
  5. Finish. To have your new VM start up as soon as the import process has finished and the new VM is provisioned, ensure the Start VM after import check box is selected before  you click Finish.

Click Finish to complete the import process and close the wizard.

The import progress is displayed in the status bar at the bottom of the XenCenter window and also on the Logs tab. The import process may take some time, depending on the size of the VM and the speed and bandwidth of the network connection between XenCenter and the server where you are installing the new VM.

Once the newly-imported VM is available, it appears in the Resources pane. This is in a nutshell, the simplest way to backup your VMs if you are the administrator of a Xenserver. There are advanced tools available like PHDVirtual, which automates the backup and transfer of your VMs to an external storage and though their pricing is a bit steep its well worth the price for the convenience and automation offered.

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
 Loading ... Loading ...
Posted by anirban under Virtualisation
1 Comment

Zimbra Collaboration Server (www.zimbra.com) is a fully featured open source messaging and collaboration application that supports email messaging,  group calendars, blackberry sync and other features using an advanced Ajax web interface that enables tool tips, drag-and-drop items, advanced searching capabilities and right-click menus in the UI. The software consists of both client and server components. Two versions of Zimbra are available: an open-source version, and a commercially supported version (“Zimbra Network”) with closed-source components such as a proprietary connector to Outlook for calendar synchronization. Zimbra has been bought over from Yahoo by Vmware and they have recently released ver 7 of the Zimbra Open source and Network editions on their website.

The Zimbra Server uses several open source projects e.g. Postfix, MySQL, OpenLDAP, Apache Tomcat (replaced by Jetty since 5.0), ClamAV, SpamAssassin, AMaViS and Amavisd-new, DSPAM, Aspell, etc. It exposes an Application programming interface (API) to all its functionality and also is an IMAP and POP3 server. The server runs on many distributions of Linux as well as on Mac OS X.

Zimbra can synchronize mail, contacts, and calendar items with open-source mail clients such as Novell Evolution and also with proprietary clients such as Microsoft Outlook and Apple Mail, but only through proprietary connectors available exclusively in the commercially supported version (“Zimbra Network Edition”). Zimbra also provides native two-way sync to many mobile devices (Nokia Eseries, BlackBerry, Windows Mobile, iPhone with 2.0 software).

Zimbra can be an ideal mail server for your organization if you are having more than 50 users and if your users require the advanced collobaration features which a groupware can offer (calendaring, shared folders, appointment tracking, web based UI, etc.). As it has full POP3 and IMAP support alongwith a robust spam filter (it segregates the suspected spam mails in a junk folder so that users can review the mails and mark the false positive mails as ‘not junk’), its very useful at organisations who want to stomp out spam mail but need the option to verify the mails which have been tagged as spam (akin to most web based email services). We have successfully implemented Zimbra for several of our clients in a virtualised environemnt and  it works well with just 2 GB RAM and Multicore processor for upto 100 users.

We have also recently migrated one of our client’s 12 GB mail box to the Zimbra Server with a single script as given below.

To migrate the mails, first you need to create all your mailboxes in zimbra server.

And after that move all your messages from Plesk Server to Zimbra server in a same folder structure.

To backup from Plesk server follow the steps.

# cd /var/qmail/mailnames/
# tar -zcvf /your/path/domain.tgz domain.com

Now move the backup file (domain.tgz) to Zimbra server with SCP or Samba share (or any other method) which ever is available. Go to the path where you have copied the backup file and extract the backup file.

# cd /your/path
# tar -zxvf domain.tgz

It will look like something as below when you list the extracted folder.

domain.com/user1
domain.com/user2

Now copy the below script in domain folder as ‘mailexport.sh’ and grant execution permission and run the script. It will add all your mails from Inbox and other custom folder to Zimbra server mailbox.

#!/bin/bash
#
# Maildir to Zimbra import
# Drop in your user root and run as superuser.
#

domain=”domain.com” # change to your domain!

for user in `ls -d1 */|sed s/\\\///`
do
echo
echo “User $user”
echo
#
#
find $user -maxdepth 10 -type d -name cur | while read line;
do
folder=`echo ${line}|cut -f3 -d”/”|sed s/\\\.//`
line2=`echo ${line/%cur/new}`
echo “FOLDER $folder”
if [ "$folder" = "cur" ]
then
/opt/zimbra/bin/zmmailbox -z -m $user@$domain addMessage Inbox $PWD/$user/Maildir/cur
/opt/zimbra/bin/zmmailbox -z -m $user@$domain addMessage Inbox $PWD/$user/Maildir/new
else
if [ "$folder" != "Sent" ] && [ "$folder" != "Drafts" ] && [ "$folder" != "Junk" ] && [ "$folder" != "Trash" ]
then
/opt/zimbra/bin/zmmailbox -z -m $user@$domain createFolder “/$folder”
fi
/opt/zimbra/bin/zmmailbox -z -m $user@$domain addMessage “/$folder” “${PWD}/${line}”
/opt/zimbra/bin/zmmailbox -z -m $user@$domain addMessage “/$folder” “${PWD}/${line2}”
fi
done

done

All done now you can get your old mails within the Zimbra server. If you are looking at an alternative messaging alternative to either Google Apps, MS Exchange or simply need a more robust messaging solution, the Zimbra collaboration suite (open source or network editions) could be a suitable solution to meet your needs.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
 Loading ... Loading ...
Posted by ankur under Linux, Mail Service
0 Comments

If you do not have Administrator privilege on the MS SQL Database server (common for shared hosting environments), you may often face a problem with shrinking the database or the transaction log, specially if the disk space is limited for you. You can easily shrink your database and log files using the simple scripts given below, even if you have the restricted db_owner database role.

Script to shrink the database

USE <databasename>
DBCC SHRINKDATABASE (<databasename>, 0);
GO

NOTE: <databasename> is the name of the database which you want to shrink. The second parameter in the SHRINKDATABASE command denotes the percentage of free space you want to keep in the database. If you put 0 (Zero) it means the database will be shrunk to the minimum possible size.

Script to shrink the transaction log

USE <databasename>
GO
ALTER DATABASE <databasename> SET RECOVERY SIMPLE
DBCC SHRINKFILE(2,0)
ALTER DATABASE <databasename> SET RECOVERY FULL
GO

NOTE: <databasename> is the name of the database for which you want to shrink the transaction log. Recovery mode of the database needs to be changed to simple to shrink the log file successfully which must be changed back to full.
The first parameter in the DBCC SHRINKFILE command denotes the file which you want to shrink and the log file is defined by the numeric value 2. The second parameter is as same as the SHRINKDATABASE command where 0 (Zero) is shown to shrink the log file to its minimum size.

The above commands can be run through the SQL Query Analyser by connecting to the remote DB from your end. Hopefully this will help you to manage database files to keep them in size.

Posted by Kollol Biswas under Database, Windows Server
0 Comments

Having a latest hardware with leased internet connectivity and a feature rich email client are not the only requirements you need to setup your office email server. Unless you can send mails to your clients, partners and associates across the globe, your mail server is of no practical use. It is the trustworthiness of your mail server over the Internet that is standing between you and the recipient.

To be a trusted E-mail sender we need to follow the best practices which are highlighted below:

1.  Messages from IP (No Domain)

Messages should be from a domain and not from any IP Address. e.g. user@domain.com is a valid sender, whereas user@192.168.0.18 is an invalid sender and thus the mail will be rejected.

2.  Mail Servers on Dynamic/Dial-up Addresses

The sender server IP Address must not be a dynamic IP. Normally all IP addresses should have a correct reverse DNS, and especially email servers. Most Anti-Spam tools will reject email from places whose reverse DNS looks not to belong to an email server, but more likely to be a home or office dynamic address. If you are running an email server, make sure the Reverse DNS of the IP address reflects your domain, e.g.  ‘gateway.yourdomain.com’ or ‘mail.yourdomain.com’.

3.  Valid FROM domain

The FROM address is important as it is the address that would be used if problems were encountered delivering the message, so if the address can’t be responded to, the E-mail cannot be accepted from that address either.

e.g. user@yourdomain.com is an accepted sender but the following bad examples will get rejected:

user@localhost
user@192.168.1.200
user@myfakedomain.not

4.  The sender server must identify itself and the identification must be Valid (Valid HELO)

Most email servers and anti-spam tools will not allow mails to be received unless the above protocol is in place. This practice does not supply enough information to safely identify the server and responsible party, if a problem email is received. And almost every mail server offers this capability. This is a simple rule which just insists that the HELO (or EHLO) is sent with every email connection and it is properly formatted.

In order to ensure that messages are not stopped by this check, make sure the HELO is a FQDN.The HELO string sent should in the style of:

HELO mta1.mycompany.com

The following bad example(s) will get rejected:

HELO
HELO localhost
HELO hostnameonly
HELO 192.168.1.1 (just an IP)

If you are the one who is not receiving emails or are unable to send mail to some of your recipients, you may ask them to check if they really follow these practices.

At Diadem Technologies, we provide secure and reliable hosted email solutions on shared, dedicated and virtual servers. With over 10,000 hosted mailboxes and a decade of expertise, we have the right solution for your needs. Please visit our website or drop us a line for more details.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
 Loading ... Loading ...
Posted by Kollol Biswas under Mail Service, Web Hosting
0 Comments

As a loyal customer of Parallels for the past 7 years who has been using the Industry standard hosting control panel, Plesk for our web hosting automation needs, we are excited with the latest release of their control panel – Panel 10. In the past few weeks we have been evaluating Parallel’s new release of Plesk Panel version 10. It is pretty good with respect to managing user level permissions. From this version onwards, web hosters would need to maintain the recommended user hierarchy while creating users, E.g. Reseller > Client/Customer > Domain/Subscription. Domain Administrator feature has been deprecated and a role based user access level has been created, e.g. certain users can only configure email ids, some users cannot access ftp options and so on.

Another feature which has been added in the Linux version of Plesk is the option to create additional FTP users, which will benefit organisations which require additional ftp users with restricted access to their folders and a primary admin user to manage the individual ftp users from a single console.

New client dashboard with an enhanced menu system. The new menu is more user friendly and faster access with respect to earlier Plesk versions.

The above screenshot is for the user (role) creation for grant/deny of particular service(s).

The most awaited new feature “Additional FTP Account”, which can be control with a Main FTP user.

An improved application vault for installing your favourite open source applications like WordPress, Joomla and much more.

Some of the key features of Plesk 10 is highlighted below:

Separate administrator user interface – Parallels Plesk Panel now comes with a separate administrator interface, optimized for server and business management.

Control panel improvements – Performance improvements for control panels make pages load much faster.

Upgradable Apache configurations – Your customized Apache configurations will now remain in force when you upgrade.

Role-based access – Customer administrators can specify application access based on user roles, enabling them to easily limit access to only those users who need it.

Improved security and performance – FastCGI lets you isolate sites in a shared hosting environment with greater account density than suPHP.

Improved password security – Assignment of random default passwords and a password strength indicator protect your servers from hackers and lower support costs attributable to broken passwords.

More flexibility in hosting plans – New and more flexible hosting concepts support a superior growth path to the future. Key features include multi-domain hosting; ability to assign multiple add-on plans to a subscription; and ability to automatically update subscription settings when plans change.

Intuitive and modern SiteBuilder (free with Parallels Plesk Panel Unlimited licenses) – The brand-new SiteBuilder, rebuilt from the ground up, enables instant Web site creation. It includes point-and-click and drag-and-drop capabilities, complete with content appropriate for different small business types and personal interests.

Like most web hosters, we are hopeful that the new version would allow our clients to get more out of their hosting plans and we intend to upgrade our clients to the new version in a phased manner over the next few months. Feel free to share your views on the new Plesk 10 panel.

Links for more information on Plesk 10:

http://www.parallels.com/products/plesk
http://www.parallels.com/products/plesk/new
ftp://download1.sw-soft.com/Plesk/PP10/Doc/

Posted by ankur under Plesk Control Panel, Web Hosting
0 Comments

Diadem Technologies launches India’s first, datacenter API driven, server management web portal

After months of development, stumbling blocks and functionality improvements, our server management portal is finally online for our Dedicated and VPS hosting clients. This application will allow our clients to monitor, manage and request support through a simple web based interface which has been specifically developed keeping in view the needs of organisations that outsource their server hosting requirements to private datacenter providers.

This is probably India’s first, API driven, fully automated, web based server management application and full credit goes to our developers and our Datacenter API services team in ensuring that the remote API services integrated seamlessly with our application and and all issues were resolved before we released the app online. Following are some of the key features of this server management application:

  • 256-bit SSL encryption is being used rendering all communication between your browser and the server as fully secure.
  • Sensitive data within the application is being encrypted and saved in the DB using a secret encryption key which can only be deciphered from within the application.
  • Servers can be rebooted online. The login details for various applications deployed alongwith the server hardware details can also be viewed online.
  • Post your support tickets online and each ticket will be archived for future reference.
  • Monitor your server bandwidth usage on a real-time basis with MRTG graphs.

You can read more details on our server management portal  on our website and if you are an existing client, you can jump right in and login to your account. We intend to keep adding new features to this application on a sustained basis and make it an indispensable tool of choice for managing your online dedicated and virtual servers.

If you would like to use this utility for managing your hosted servers, drop us a line to discuss your requirements and recommend an appropriate solution.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
 Loading ... Loading ...
Posted by hriday under Datacenter, Server Management Portal
1 Comment

There is often a need for a hot backup of a live SQL server database as a standby. To accomplish this a stored procedure can be created in the master database and called periodically using SQL server Jobs with proper arguments. The script provided below is for MSSQL server 2005 database but the same can be done for SQL server 2000 and SQL server 2008 databases too. I will explain the changes to be done for the same as well.

Run the script below in the Query window on the “Master” database to create the DB copy stored procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE master
GO

IF object_id(‘dbo.Dbcopy_sp’,'P’) IS NOT NULL
BEGIN
PRINT ‘Procedure already exists. So, dropping it’
DROP PROC dbo.Dbcopy_sp
END
GO

CREATE PROCEDURE Dbcopy_sp
(
@DB varchar(200),
@BackupFile varchar(2000),
@TestDB varchar(200),
@RestoreFile varchar(2000)
)

AS
BEGIN

DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)

SET @DataFile = @RestoreFile + ‘.mdf’

DECLARE @LogFile varchar(2000)

SET @LogFile = @RestoreFile + ‘.ldf’

IF @DB IS NOT NULL

BEGIN

SET @query = ‘BACKUP DATABASE ‘ + @DB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFile, ””)

EXEC (@query)

END

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)

BEGIN

SET @query = ‘DROP DATABASE ‘ + @TestDB

EXEC (@query)

END

RESTORE HEADERONLY FROM DISK = @BackupFile

DECLARE @File int

SET @File = @@ROWCOUNT

– This always returned 0 for me but the

– RESTORE call returned the number

– of rows associated with the backup. Strange…

DECLARE @Data varchar(500)

DECLARE @Log varchar(500)

SET @query = ‘RESTORE FILELISTONLY FROM DISK = ‘ + QUOTENAME(@BackupFile , ””)

CREATE TABLE #restoretemp

(

LogicalName nvarchar(500),

PhysicalName nvarchar(500),

Type varchar(10),

FilegroupName nvarchar(200),

Size numeric(30,0),

MaxSize numeric(30,0),

FileID bigint,

CreateLSN numeric(25,0),

DropLSN numeric(25,0),

UniqueId uniqueidentifier,

ReadOnlyLSN numeric(25,0),

ReadWriteLSN numeric(25,0),

BackupSizeInBytes bigint,

SourceBlockSize int,

FileGroupId int,

LogGroupGUID uniqueidentifier,

DifferentialBaseLSN numeric(25,0),

DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit,

IsPresent bit

)

INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = ‘D’

SELECT @Log = LogicalName FROM #restoretemp WHERE type = ‘L’

PRINT @Data

PRINT @Log

TRUNCATE TABLE #restoretemp

DROP TABLE #restoretemp

SET @query = ‘RESTORE DATABASE ‘ + @TestDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) +

‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ +

QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””) + ‘, FILE = 1, RECOVERY’

EXEC (@query)

END
GO

After you run the above script you can find a Stored procedure created in the name of  “Dbcopy_sp” in the master database. You can run the stored procedure manually or call it from scheduled jobs provided by SQL server agent for creating the database copy. The  variables that needs to be passed to the stored procedure are as below:

  • @DB = The original database name which you want to copy.
  • @BackupFile = The database backup file name that will be created along with the path( e.g : C:\Databases\dbcopy\Sourceddbackup.bak )
  • @TestDB = The backup database name that will be created
  • @RestoreFile = The new database file name without .mdf/.ldf (e.g  C:\Databases\destinationdb)

For scheduling the database copy creation create a job with below command:

EXEC   Dbcopy_sp    <sourcedatabase> ,  ‘C:\Databases\dbcopy\Sourceddbackup.bak’ ,  <destinationdatabase> ,  ‘C:\Databases\destinationdatabase’

This will create a database named destinationdatabase and the associated .mdf and .ldf files will be placed under the directory “C:\Databases”. You will need to alter the database name and backup/restore location according to your need.

NOTE: The procedure mentioned above uses the file level restore and hence the fields defined in the table “restoretemp” in the stored procedure has to be altered for SQL Server 2000 and SQL Server 2008/2008 R2. Please visit the below links for the exact fields and their data types.

For SQL Server 2000:

http://msdn.microsoft.com/en-us/library/aa238420%28v=sql.80%29.aspx

For SQL Server 2008/2008 R2

http://msdn.microsoft.com/en-us/library/ms173778.aspx

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
 Loading ... Loading ...
Posted by anirban under Database, Windows, Windows Server
0 Comments

Here are a couple of tweaks which can improve your mysql server performance significantly. We implemented this on our hosted servers and the php/mysql queries for dynamic DB driven applications  improved by over 50%. The following are the relevant commands which can be inserted in the mysql conf file, which is normally located at /etc/my.cnf.

# Improves mysql server performance by disabling name resolution and remote mysql connections
skip-name-resolve
skip-networking

The skip-name-resolve option will disable name resolution for hostnames and the skip-networking option would disable remote connections to be made to the local MySQL server. This is a recommended setting and would improve your server security as well.

Feel free to post your queries and share any other optimisation tips as well!

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 2.50 out of 5)
 Loading ... Loading ...
Posted by hriday under Database, PHP/MySQL
0 Comments

As 2010 finally comes to a close, here is a good time for all of us to take a stock of our hits and misses and plan ahead for the next year. I am pretty sure all of us have had some unique experiences to share and from an organisation perspective, I am summing up some of the milestones which we were able to reach this year:

  • Diadem website revamp: We were able to redo our very own website, www.diadem.co.in and launch it online.
  • New shared hosting plans: We overhauled our shared hosting plans for windows and linux and upgraded our clients to take benefit of the enhanced features available.
  • Launch of our VPS Hosting Plans: We achieved a significant milestone this year by launching our VPS hosting plans and subsequently upgrading the server hardware, automating backup and being able to provide custom solutions for a growing group of clients, who are able to run their mission critical apps within their own dedicated server environment at costs, which are a fraction of  a dedicated server.
  • Newsletters: As part of my client interaction initiatives, I am now sending out regular e-newsletters to our clients to keep them abreast on our current happenings and upcoming trends in the industry. This effort has been well received and the efforts appreciated by our clients.
  • Dedicated Server Management Module: Our very own server management module is on the verge of being released and would allow our dedicated and VPS clients to being able to manage and communicate more effectively through this web based application. Stay tuned for more details on this soon.

Apart from the above, we were able to enhance our server hardware, software and generally streamline our operations to deliver over 99.5% service uptime to our clients. Though we also have had our fair share of misses and non starters to contend with this year, we have quite a few  services planned for the future and I feel pretty good to conclude this year on a high note. On behalf of our entire crew @ Diadem, we wish you continued success for the  year ahead and hope you make the most of it as well!

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
 Loading ... Loading ...
Posted by hriday under News & Updates
0 Comments