0

I have been tasked with pushing Google Analytics data periodically to SQL Server 2005 running on a different server from various websites (about 450 of them) grouped on various servers. I understand I can run PDO queries with PHP to write to the SQL Server DB, but I'm unclear about the second part, doing it periodically. The client wants the data pushed hourly for some data and daily for others.

Would setting up a cron job be the best way to do this? Would I have to add the PDO snippet to each site I want to push data from, or can I run the query for multiple sites from one location on each server? Are cron jobs and PDO the best way or is there a more practical way?

user3792199
  • 3
  • 1

1 Answers1

1

You didn't mention on which OS those websites run, but I'll assume Linux because you mentioned cron jobs; in this case, I'd recommend PERL or Python, which fully support running SQL queries and are really easy to run as shell scripts, and thus as cron jobs. I'd avoid PHP, as getting it to run from a shell script can get somewhat more tricky, but it's your call: you could be a lot more confident with PHP than other languages, and you actually can use PHP for something else than building web pages.

Cron jobs are definitely the way to go when scheduling taks on a Unix/Linux system.

If by chance you need to do the same in Windows, I'd still recommend the same approach (with Scheduled Tasks in the place of cron jobs): while SQL Server has command-line tools for running queries (osql and sqlcmd), you'd still have to wrap some kind of script around them in order to gather and format your data; PowerShell can help you here (and it also can perform direct queries to SQL Server), but you'll be probably more familiar with other scripting languages, and both PERL and Python are available on Windows.

Massimo
  • 68,714
  • 56
  • 196
  • 319