It’s interesting how large publications write multi-page features and then pepper the page – sometimes even interrupting content with links to totally irrelevant content. How am I to make it through their expose without clicking out to some other link and getting distracted – thereby losing the supposed value that a long form story bears? Let’s think a little.
Monthly Archives: November 2009
SSIS and Oracle
Working with Oracle and SSIS/DTS on a 64-bit windows server has been a painful experience.
Here are the steps to connect to Oracle if you’re setting up a new SSIS package.
- Create a connection using the .net Providers\OracleClient Data Provider
- Use the DataReader source type. Select the Oracle connection you created in step one.
- Use whatever destination you need to – it doesn’t matter at this point. I typically use the OLE DB for SQL Server destinations.
- Schedule as a job with the integration services type step. No 32 bit DTEXEC modification needed as was the case with DTS steps that hit Oracle.
You may hit some quirky errors with overflows or numeric data types. It’s good to make sure you have the latest oracle patchset applied before proceeding with workarounds.
Backups
I’ve always been a stickler for backups, even before I was a sysadmin. I create music on my home computer, creating massive volumes of data just waiting to disappear from my life.
I have been using SuperDuper and TimeMachine on OS X. I don’t backup my windows machine, since it is a media center we mostly just stream stuff off the web.
When I leave town I throw the backup drives in an unlocked fireproof save with a note begging thieves not to steal it. This data is truly unique and irreplaceable.
But what if there was a fire when I didn’t have my drives in the safe? I’d be very sad, that’s what.
There are a few online backup services which are starting to look very attractive. Most offer 2GB for free. I’ll be needing at least 50GB for audio mixes. Actual song files are another story….maybe 300GB or so. Then there are various video, picture and music files I’d hate to lose. So it’s looking like I’ll be needing around 500GB to lock it all up.
Enter Mozy – $4.95 a month for unlimited storage space. I assume this is for one computer. What a deal. That puts a year at $60. 40 years at $2400.
Sold.
Reporting Services and Sharepoint Integrated mode
Microsoft’s licensing has once again saved me some work. To run Reporting Services in SharePoint integrated mode , you will need a SharePoint license on your reporting server. A SOAP endpoint is enabled on the report server which lets SharePoint talk to the report server. Seems somewhat of a strange departure from the typical MSFT licensing. We will not be buying another license, needless to say.
Helping out the auditors
If you’d like to give an auditor their very own copy of your data on their machine and your DB is less than 4 GB, then read on. As a responsible IT person, you won’t be putting sensitive information on this auditor machine, unless it is properly secured, electronically and physically, right?
1. Set up the auditor machine
Install SQL Express on the auditor machine. Enable remote connections via TCP/IP with the config tools. Grant permissions for your agent account on the source server to be an admin on the auditor’s machine. You’ll also need to grant access for the agent account to write to a backup directory on the auditor machine.
2. Create a script that will restore the database and do some other steps. This script is referenced from sqlcmd in step 3. My script does these things:
MASTER.dbo.usp_killusers ‘MyDB’ –something similar to this script ->> http://www.sqlservercentral.com/articles/Administration/71/
RESTORE DATABASE MyDB FROM DISK = ‘\\workstation\backup\MyDB_Auditor.bak’
WITH REPLACE,
MOVE ‘MyDBtrainingsys’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf’,
MOVE ‘MyDBtrainingdata’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_1.ndf’,
MOVE ‘MyDBtraininglog’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_2.ldf’–Drop our auditing ddl trigger from the database
IF EXISTS (SELECT * FROM sys.triggers WHERE name = N’DBA_Audit’ AND parent_class=0)
DROP TRIGGER [DBA_Audit] ON DATABASEEXEC sp_changedbowner ‘sa’
–Add rights for whomever needs access
CREATE USER [Domain\AuditorLogin] FOR LOGIN [Domain\AuditorLogin]
EXEC sp_addrolemember N’db_datareader’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_datawriter’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_ddladmin’, N’Domain\AuditorLogin’
3. Create the job with these steps on your source server.
(T-SQL type) – Create backup on workstation
BACKUP DATABASE MyDB TO DISK = ‘\\workstation\Backup\MyDB_Auditor.bak’
(OS Command) – Restore
sqlcmd -i \\Share\auditorRestore\MyDBAuditorRestore.sql -Sworkstation\sqlexpress -o \\share\sf.log
(OS Command) – Cleanup
del \\workstation\backup\MyDB_Auditor.bak