SQL Server Performance

Adding MySQL Connection in to SQL Server 2008 R2

Discussion in 'SQL Server 2008 General DBA Questions' started by jace808, Apr 7, 2011.

  1. jace808 New Member

    We have about 6 main SQL servers that store all our reporting data and unfortunately about 30 MySQL servers throughout the country we have to pull from daily. We have linked servers set up but I wanted to know if it is possible to connect directly to a MySQL server without it being linked. I would like to be able to open up the tables and and see the columns as I do with our SQL servers. This is just one reason, but there are others as well. TIA.
  2. satya Moderator

    How frequently the data is going to be pulled from MySQL?
    Is that data going to be big in terms of number of rows (volume)?
    I don't see a way round other than using Linked Server to outside data sources. That is the only way you will need to work via management studio (or third party tools for MySQL).
  3. jace808 New Member

    [quote user="satya"]
    How frequently the data is going to be pulled from MySQL?
    Is that data going to be big in terms of number of rows (volume)?
    I don't see a way round other than using Linked Server to outside data sources. That is the only way you will need to work via management studio (or third party tools for MySQL).
    [/quote]
    I spoke before I knew what I was talking about. I'm new to MS SQL... Before I got this large promotion I only had access to.... Access and didn't know the power of SQL Server so I'm learning things new every day.
    I manage the project and we have about 20 developers devoted to our project (not just this one) and I spoke with the lead yesterday and he told me that they would be using SSIS which is new to me and I assume it's SQL Server Integration Services? At any rate I had read that linked servers are only good for a smaller amount of data and this would not be a small amount of data. We have roughly 20,000 employees we would have uploads to the 30 servers every day with up to 100 rows of data after it's staged so linked servers may not be the answer. I'm going to start looking in to how to set up SSIS now that I've learned this.
    To answer your questions it would be a once a day early AM pull and upwards of 20,000 x ~70 rows so about 1,400,000 rows taken in every morning, but that's the production side.
    I originally posted this for my personal use to keep tabs of what's available and how things are proceeding because I'm starting to think we are overpaying our contractors for work they are doing the bare minimal on.
    For instance, they are requesting that the vendors who produce this data to provide a stored proc on each server (which schemas vary slightly), but the more I think about it; why don't our developers just create the view on our end and the stored proc because it's taking a very long time to make this all happen. We already have access to the data and to me it seems it would require less time to go this route not to mention I would think it would be faster. Also, if anything changed down the line we could make the changes in 1 place instead of the 30. Doesn't this sound more logical?
  4. satya Moderator

    Your explanation has cleared many doubts surrounding the topic, so I believe the approach is on right track and in terms of optimization stored procedure on each server is beneficial for performance and execution plan management.
  5. Jahanzaib Member

    Linked Server usage is to use tables from another server,if you want to see the catalogs or other objects in the MySQL database then you have to install client tool for this
  6. satya Moderator

    Are you recommening a third party tool in thsi case?
  7. Jahanzaib Member

    Not third party tool you have to install MySQL ODBC driver and then create Linked Server with DSN of MySQL

Share This Page