July 25, 2013 at 2:57 pm
Hi guys,
Synopsis
Bored at work, I came up with the idea having (historical) monitoring of our SQL Server instances available on Oracle APEX pages like we do for our Oracle environments. My 2 main reasons for this are:
1. experience/something to kill the time with
2. rather amusingly we have hundreds of SQL Server instances but not a single DBA test server for SQL Server. My hope from this project is that it will convince my manager to give us our own server which would also act as the repository for forwarding to the Oracle DB.
So I installed XE on my laptop & used it to test using Linked Servers. Having successfully passed data from remote SQL servers, into my laptop and then on to Oracle a colleague said I could use his team's server (since our laptops are repeatedly forced to sleep and so overnight jobs would not run). I've now got a database on his server collecting from 2005 & 2008 databases (11 servers in total).
Main point
I can collect data from 2005 & 2008 databases OK but the queries would fail on 2000 due to views like sys.databases & I think SERVERPROPERTY was SERVERPROPERTYEX?
Is there some .sql available online that will create 2005 views/functions in 2000?
If not publicly available, has anyone done similar & is willing to share?
Just so I know I'm not re-inventing the wheel~~
July 25, 2013 at 3:20 pm
I personally havn't had a need to do it, but since you have the inspiration, here is a link to get you started.
Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
http://msdn.microsoft.com/en-us/library/ms187997(v=sql.90).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 25, 2013 at 3:34 pm
Oh I have this: http://www.mssqltips.com/sqlservertip/1037/system-information-in-sql-server-2000-vs-sql-server-2005
My question was more whether anyone has done "CREATE VIEW sys.databases ...;" already or not~
July 25, 2013 at 3:45 pm
July 25, 2013 at 3:46 pm
Dird (7/25/2013)
Oh I have this: http://www.mssqltips.com/sqlservertip/1037/system-information-in-sql-server-2000-vs-sql-server-2005My question was more whether anyone has done "CREATE VIEW sys.databases ...;" already or not~
Since your code needs to run against 2000 - 2008, perhaps you could use the 'Compatibility Views'. They're not as comprehensive as newer views in 2005+, but may work for what you need.
http://msdn.microsoft.com/en-us/library/ms187376(v=sql.90).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 25, 2013 at 3:51 pm
Elliott Whitlow (7/25/2013)
YAlso using linked servers to reach out to a lot of servers tends to fail, a lot. It can be done but it is not idea. SSIS is a much better process.
Why does it tend to fail (besides login issues) a lot? What kind of number is "a lot" of servers?
Although currently I'm just looping through 11 servers and doing 2 simple queries for each it has been running OK & when it hits an error it continues through the loop.
July 25, 2013 at 3:57 pm
Dird (7/25/2013)
Why does it tend to fail (besides login issues) a lot? What kind of number is "a lot" of servers?Although currently I'm just looping through 11 servers and doing 2 simple queries for each it has been running OK & when it hits an error it continues through the loop.
So long as there is no issue with login credentials, connectivity, network, etc. then the linked server connection will work 99.99% of the time. The same issue above will also break SSIS. I have a job that uses linked servers to pull resultsets on execution requests (runlengh, sql text, i/o, blocking, etc.) from a handful of servers into a local table every few minutes. It works 24x7.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 25, 2013 at 4:00 pm
I have seen processes that use linked servers tend to die in ways that cannot be trapped within a sproc, these errors tend to kill a connection. You can have some connectivity issues with SSIS as well but the ability to control failures more cleanly is a huge selling point for me. Clear?
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply