SQL Multiple Computers

[YodaMan]

Our company is running SQL on one computer that runs DTS packages every morning to update our current website. This ability is only available on one computer with our office. I am worried that if that computer goes down, I will be totally messed up. Before this ever happens I would like to create another instance of this program on another computer. Is this process just as simple as putting in the disc on another computer and installing it w/ the user name and password, or shoudl I be very careful and stray from this option and just do a backup of all the information the server holds?
I am a rookie at SQL but I think that installing the program on multiple computers that is connected live to our web server can cause a lot of problems. Am I right?
Thanks in advance
Yodaman

ps. Is this even legal for me to install another version of the software on another computer, just as a back up to the first one?

 

[labgeek]

SQL server can run DTS packages on a scheduled basis at the server itself. Then if the machine is down, it wouldn't matter anyway as a 2nd client couldn't get to the server anyway. We have several DTS packages running this way. Many are chained one right after the other... They simply alert us if there's a problem.

Installing the MS SQL Enterprise Manager is not a big issue technically. I don't remember the licensing arrangement off hand though. We have a couple of machines we use to connect to the SQL server for management (including adhoc DTS runs), and if one went down, it would take mere minutes to have another running it assuming a functional machine as base to install from. Make sure your DTS packages are stored somewhere safe - not only in the repository, but also as files and backed up that was as well.

Speaking of backups, you're running SQL backups not just file backups right?

 

[YodaMan]

So lets start from the top here. First of all (as much as this might shock you), we do not run any sort of backups on the SQL as I understand it. The information we put on the SQL database comes from a different server which runs backups every night. Also what benefit would I have from running backups on the SQL server everyday? First of all I dont know much about SQL at all, so please feel free to tell me anything, no matter how stupid it might seem to you. Also if I just copy the DTS packages somewhere on a couple of other computers, can I just install Enterprise Manager and run them? What other information shoudl I be looking to save over to other computers? How difficult is it to run scheduled packages. The problem with that is that we have to use a client as our server itself lies in our parent company 35 miles away. Thanks for all your help with this.
Yodaman

 

[labgeek]

I don't know your setup, so I can't tell you about how important backing up the SQL data is. From the sounds of it, it may not be. That's something you'd need to determine for yourself. And it sounds like you have a way to recover it if need be.

Where are your DTS packages being stored? When the packages were saved you had a few options on where they were put - file, SQL server, Microsoft repository. The default is the SQL server which saves it to the sysdtspackages table in the msdb database. Which means if you aren't backing up your database and it corrupts or you lose drive(s), you're hosed. Repository allows some other things to be done such as versioning depending on the repository your saving to. A file is just that - a file. It can be copied to other computers as well. Using the default of SQL Server as the location where it was saved means any computer with Enterprise manager can run the package without copying. It's just there under the Data Transformation Service section. If your importing or exporting data to/from files you'll need to make sure the files are the same place on all computers that can run the package. Better yet, I go back to why isn't the server running them itself? It would be faster anyway, and the SQL server is required to be running for it to work anyway. We do this often. We FTP into a specified directory from a mainframe and a 'nix box and it does reconciliations and various manipulations all on a timed schedule.

It's not that hard to run them. Sometimes permissions can be a little tricky to setup but not that bad. Right click on the DTS package and select Schedule Package. Tell it when. It will then show up under Management - SQL Server Agent - Jobs. Make sure SQL Server Agent is running on the server. BTW, it can email/page/etc. when the job completes/fails/etc. so it can be monitored pretty easily.

 

[YodaMan]

Hey thanks for your response. Now I am pretty sure that we are not running our packages from the server because someone sits there every morning and runs the packages from their own computer, and I can access the files trough "MY Computer" on that guys computer. Now I am working here part time and was not here when this stuff was installed. So I have no clue as to who,what, where, when....I am just trying to save this company from possible disaster.
My lack of understanding says at the moment to just copy the dts packages from his computer to mine as well. Install enterprise manager and try to run the packages from here. In terms of setting up and permissions, do they have to be approved by the server (meaning do I have to put my IP address in the server as an acceptable client?)
The files we import actually come from another computer and any computer from within the company can be mapped to that comptuer so we dont have to worry about the location from which they are coming. My guess as to why the server isnt running them is probably because our server is actually located 40 miles from us and the files we use are the files that are updated by a computer in house every night. There might have been a problem just connecting the two in terms of security, or more possibly due to the lack of Tech Support within the company and the desire for the "quickest" and cheapest" fix.
Also if you could suggest where I should start to prevent any problems that would be great. I mean we are running the packages everyday and they are annoying but I would rather first backup everything just to be safe. I dont have constant access Enterprise manager so I really cant check, but is it just as simple as choosing an option on the title bar and clicking on "run backup" or something along those lines?
thanks again
yodaman

 

[labgeek]

The files you say you can access through "My Computer" are the .dts files (the packages themselves)? If so then yes, you'll need to copy them to the other computer. I don't know what the packages themselves are doing, so I can't really assess the possible problems with running them from another client. DTS packages can do lots of things - it includes VBA (visual basic for applications).

SQL authentication can take a couple of forms: Username and password or NT Authentication. Either way you need to be authorized. One will use your NT domain login information and verify against that, the other will login against the SQL server itself.

Backing up a database isn't that hard. We use OmniBack to do it, but that would probably be overkill for what your looking to do. To backup a database, in Enterprise Manager - Databases, select the database to backup, right-click, select All Tasks, then Backup Database. From there you can back it up to a tape device or a file. You probably want to backup: master, model, and msdb along with the database where your user data resides, at least for the 1st time. Backups use local to the server devices and directories to store the data. That is a backup to c:\sqlbackup\ will be to the server's C: drive not yours.

 

[YodaMan]

Yea the files from "My Computer" are actually the .dts files. Well these packages basically take the data that is outputted each night from our server and converts it and organizes it so we can show it on our website. But in either case I will try to backup as you suggested. Thanks a lot for your help.
Have a nice day!