Aaron Berquist comes through with a solution to a problem my team has run into several times before, which is including data from a linked SQL Server in a view for Smartlist Builder. Check it out here.
Entries in SQL Server (10)
One of the first things I have to do after I’ve installed Dynamics GP and created the DYNAMICS, TWO and company databases, is setup jobs to backup and optimize those databases. The best method I’ve found to do that is a free solution developed by Ola Hallengren that can be found here. This solution received the 2010 Editor’s Choice Bronze Award for best Free SQL Tool, while the SQL Server Community gave it the Gold. After a previous write-up of this solution Ola contacted me directly asking for my feedback, and he has been in touch several times since – he clearly cares about his solution and makes regular improvements as SQL Server features are added or changed.
The solution can be installed by downloading and running a single sql script – direct link here. Upon opening the script in SQL Management Studio, I typically make two changes. First I change the value of the @BackupDirectory parameter, highlighted below, to point to the location I want the backups stored. The backup job will create a folder with the server name in this folder, with folders for each database below that, followed by folders for each of the backup types – full, differential, and log.
The second change I make is that I like to keep 3 days worth of backups on disk if possible, and the scripts defaults to cleaning up backups older than 24 hours, so I like to change that to 72 hours. This can certainly be done afterwards by editing the job step but I like to do it up front. I usually do a quick find and replace operation on “24, ” replacing with 72.
After the replace is completed, I run the script and the highlighted jobs below are created. From there I open each job, set the desired schedule for each one, and I’m done.
I use this script because I want consistently configured backup and database maintenance jobs on all of the SQL Servers I manage for my customers. It takes 10-15 minutes to configure and I love that it’s smart enough to not throw an error on the whole transaction log job if someone creates a new company and the recovery is still set to Simple – it skips that database and moves on to the next one. It is also much smarter then the SQL Server Maintenance Plans about whether it rebuilds or reorganizes the databases indexes. Visit Ola’s site, http://ola.hallengren.com to read more about the solution or view the documentation.
I just signed up for SQL Saturday #67 in Chicago! SQL Saturday is a free one-day training event that is put on in cities around the country. There may be a small charge for lunch – $7 in my case. I’m completely comfortable with basic administration of SQL Server but I want to learn more about the internals and be able to quickly resolve weird performance issues. I’m not expecting to get too deep into those topics in one day of free training but I do hope to network with some real SQL pros and get pointed in the right direction.
Unfortunately not all of us work for companies that see the value of investing in their employees, so a fat training budget or professional development plan are not in the cards. I’m left to pay for my training out of my earnings, so Convergence every year isn’t an option. I genuinely enjoy my work, my customers, and most of the people I work with, so I want keep improving my skill set, which requires investing in myself. There are a lot of good free and low cost options for Dynamics GP as well – free virtual conferences at MSDynamicsWorld.com, online training at Partnersource and Customersource, as well as two great books from Mark Polino and Victoria Yudin.
So to all the GP admins out there, I encourage you to check out the SQL Saturday events in your area at the SQL Saturday page here. It’s an excellent option for “sharpening your axe” – and if your employer won’t support you, don’t let that stop you. You may find that demonstrating your commitment to learning and improving will change that in the future. In addition to SQL Saturday check out 24 Hours of PASS at http://www.sqlpass.org/24hours/Spring2011/.
Just caught this post by Jorge Segarra regarding a new session of SQL University, a series of blog posts designed to teach SQL Server technology from the ground up. Most of my education on SQL Server has taken place on the job and many times I find myself wishing for a more thorough and structured education, so it’s back to school I go. Class is scheduled to start on April 12th, so I’m going to commit to making time to following this each week. Go here for more info on SQL University.