It's nearly impossible to be a software engineer without interacting directly with databases. In many large companies you'll find that Microsoft SQL is a staple, and it really helps to know TSQL very well. Whether you're an expert or a beginner, it helps to follow these five simple rules.
1. Backups. Make sure you have a daily backup schedule, and that you make backups before you make any changes. You will never regret having backups.
2. Always do your work on a development server. It should go without saying, but you should never log into a production server unless you absolutely have to. In fact, try to have your access to prod servers revoked completely if you can. If your company doesn't have a dev server, push for one. If you can't get one, install SQL Server Express on your development machine and load the tables with dummy data.
3. Save your TSQL scripts in version control software. A lot of developers that have to do a lot of TSQL really like Redgate, and it is actually really sweet. Unfortunately it doesn't seem to be that well known, and the licensing for it can be expensive. If you don't have it, your regular version control software will suffice.
4. Try to keep it simple. I know, it's easier said than done with TSQL stored procs a lot of the time. But simplicity will almost always benefit you.
5. Use automation to deploy to production. When it's time to deploy to production, you're going to want to automate it as much as possible to minimize the chance of human errors. There are a lot of great CI (continuous integration) software suites out there like Jenkins, Bamboo, Travis, and TeamCity that can handle it (Jenkins is my current favorite); or, at the very least use scripts that you have thoroughly tested on your development server.
Let me know what you think. Do you agree or disagree with any of these rules? Is there something important that I didn't mention? I want to hear about it, let me know!
Friday, March 31, 2017
Sunday, May 27, 2012
Migrating from Visual FoxPro to .NET
From 1994 to 2004 Visual FoxPro was at the heart of many data oriented business application that ran in Windows. And for good reason too; it allowed developers to quickly and effectively develop excellent business applications. But alas, Visual FoxPro is at end of life. Microsoft is recommending that all enterprise applications be written in .NET. This is a good thing. The Microsoft .NET framework is far superior to FoxPro in every way. Unfortunately, business are still using FoxPro - and I say unfortunately because migrating from Visual FoxPro to .NET can be quite frustrating.
Microsoft has stopped releasing OLE DB and ODBC drivers for 64 bit systems. Since Windows Server 2008R2 and the up and coming Windows "8" Server (which is available as a free beta right now) don't have 32 bit releases of these operating systems. That means the need to migrate VFP to .NET can come very suddenly.
Fortunately, it is very possible to migrate each piece of software without suffering from any down-time in your network applications (keep in mind I said "possible", not guaranteed) as you're building your .NET applications.
If your company does not make use of any virtualization software, now is the time to start. Covering the benefits of virtualizing servers is not within the scope of this article, here is a link to one that lays out the benefits very well. What you'll want to do is setup a virtual machine, one for Microsoft Server 2003 (32 bit version). You won't need to allocate very much space to it, 20 gigabytes is probably sufficient for our purposes here. It's not going to require much RAM either - 1 gigabyte should be more than sufficient.
Once you get the virtual machine setup, go ahead and install all the updates for it. This is going to take a couple of hours, so make sure you have something else to do while this is going on. After all the updates have installed, go ahead and install the Microsoft FoxPro OLE DB drivers. You can find them here. After you get the driver installed you only need one more piece of software: Microsoft SQL 2008 SQL Express. It must be this version, the new 2012 will not work (believe me, I tried.). Here is a link to the Server, and Management Studio; you will need both.
After you are finished installing the Server and Management Studio Express, what you'll want to do is configure a linked server to the FoxPro database. I prefer to use scripts rather than the GUI, so here's what I executed:
EXEC master.dbo.sp_addlinkedserver
@server = N'SERVERNAME',
@srvproduct=N'',
@provider=N'VFPOLEDB',
@datasrc=N'E:\PATH\TO\FOXPRO\DIRECTORY
Please note that the datasource will be the directory of the FoxPro data files, not the actual datafile themselves. With this configuration, you can call read and write to your FoxPro tables from this SQL database. The easiest way to do this is with the OPENQUERY function:
SELECT * FROM OPENQUERY(SERVERNAME, 'SELECT * FROM MyFoxProTable')
Well, there you have it! It's not the most elegant solution out there, but it's not too hard and very inexpensive to go this route. Good luck in your applications migration!
Microsoft has stopped releasing OLE DB and ODBC drivers for 64 bit systems. Since Windows Server 2008R2 and the up and coming Windows "8" Server (which is available as a free beta right now) don't have 32 bit releases of these operating systems. That means the need to migrate VFP to .NET can come very suddenly.
Fortunately, it is very possible to migrate each piece of software without suffering from any down-time in your network applications (keep in mind I said "possible", not guaranteed) as you're building your .NET applications.
If your company does not make use of any virtualization software, now is the time to start. Covering the benefits of virtualizing servers is not within the scope of this article, here is a link to one that lays out the benefits very well. What you'll want to do is setup a virtual machine, one for Microsoft Server 2003 (32 bit version). You won't need to allocate very much space to it, 20 gigabytes is probably sufficient for our purposes here. It's not going to require much RAM either - 1 gigabyte should be more than sufficient.
Once you get the virtual machine setup, go ahead and install all the updates for it. This is going to take a couple of hours, so make sure you have something else to do while this is going on. After all the updates have installed, go ahead and install the Microsoft FoxPro OLE DB drivers. You can find them here. After you get the driver installed you only need one more piece of software: Microsoft SQL 2008 SQL Express. It must be this version, the new 2012 will not work (believe me, I tried.). Here is a link to the Server, and Management Studio; you will need both.
After you are finished installing the Server and Management Studio Express, what you'll want to do is configure a linked server to the FoxPro database. I prefer to use scripts rather than the GUI, so here's what I executed:
EXEC master.dbo.sp_addlinkedserver
@server = N'SERVERNAME',
@srvproduct=N'',
@provider=N'VFPOLEDB',
@datasrc=N'E:\PATH\TO\FOXPRO\DIRECTORY
Please note that the datasource will be the directory of the FoxPro data files, not the actual datafile themselves. With this configuration, you can call read and write to your FoxPro tables from this SQL database. The easiest way to do this is with the OPENQUERY function:
SELECT * FROM OPENQUERY(SERVERNAME, 'SELECT * FROM MyFoxProTable')
Well, there you have it! It's not the most elegant solution out there, but it's not too hard and very inexpensive to go this route. Good luck in your applications migration!
Friday, December 31, 2010
Using PostgreSQL with Symfony 1.4
Recently my company decided to upgrade from Symfony 1.0 to Symfony 1.4. This upgrade was long overdue. With this upgrade came a problem; the PDO driver for MySQL did not support an SSL connection, which is something that one of our clients required. As a result we decided to go with PostgreSQL as our database, as the PDO driver did support an SSL connection.
After doing several Google searches and asking for help on some IRC forums, it became clear that very few people knew much about getting Symfony to work with PostgreSQL. Not surprising since almost all PHP developers use MySQL. Turns out, it is not as bad as it sounds to get Symfony up and running with PostgreSQL quickly.
Step 1: Install PostgreSQL. Quick instructions for this can be found on PostgreSQL's own website: http://www.postgresql.org/docs/8.4/interactive/install-short.html. The only thing I would change about this is adding the --with-openssl option when you run the configure script before compiling.
Step 2: Start your Symfony project. This is also pretty straight forward, the instructions for this are here: http://www.symfony-project.org/jobeet/1_4/Doctrine/en/01
Step 3: Download the Symfony plugin for PostgreSQL.
Step 4: Publish the plugin.
Step 5: Alter the config/ProjectConfiguration.class.php file to get the enablePlugins function to accept an array.
Step 6: Create the plpgsql language. As the postgresql user run the command:
And there you have it! You should now be able to operate Symfony with PostgreSQL just like you did with MySQL. Enjoy!
After doing several Google searches and asking for help on some IRC forums, it became clear that very few people knew much about getting Symfony to work with PostgreSQL. Not surprising since almost all PHP developers use MySQL. Turns out, it is not as bad as it sounds to get Symfony up and running with PostgreSQL quickly.
Step 1: Install PostgreSQL. Quick instructions for this can be found on PostgreSQL's own website: http://www.postgresql.org/docs/8.4/interactive/install-short.html. The only thing I would change about this is adding the --with-openssl option when you run the configure script before compiling.
Step 2: Start your Symfony project. This is also pretty straight forward, the instructions for this are here: http://www.symfony-project.org/jobeet/1_4/Doctrine/en/01
Step 3: Download the Symfony plugin for PostgreSQL.
php ./symfony plugin:install sfPostgresDoctrinePluginStep 4: Publish the plugin.
php ./symfony plugin:publish-configStep 5: Alter the config/ProjectConfiguration.class.php file to get the enablePlugins function to accept an array.
class ProjectConfiguration extends sfProjectConfiguration
{
public function setup()
{
$this->enablePlugins(array(
'sfDoctrinePlugin',
'sfPostgresDoctrinePlugin'));
}
}
Step 6: Create the plpgsql language. As the postgresql user run the command:
createlang plpgsql [databasename]And there you have it! You should now be able to operate Symfony with PostgreSQL just like you did with MySQL. Enjoy!
Subscribe to:
Posts (Atom)