April 16, 2012 at 10:17 pm
Comments posted to this topic are about the item Linked server to MySQL Server.
April 17, 2012 at 1:49 am
It's worth mentioning that some queries require additional casting in order to work across the link otherwise you get hexadecimal gobbledegook.
SELECT
CAST(NAME AS VARCHAR(64)) AS TableName,
CAST(COALESCE(Engine,'') AS VARCHAR(64)) AS Engine,
COALESCE(ROWS,0) AS RowsInTable,
COALESCE(Avg_row_length,0) AS AverageRowLength,
coalesce(Data_Length,0) AS Data_Length,
COALESCE(Max_Data_Length,0) AS MaximumDataLength,
coalesce(Index_Length,0) AS IndexLength,
COALESCE(Auto_Increment ,'') AS AutoIncrement
FROM OPENQUERY(MYSQLSRV,'SHOW TABLE STATUS;')
SELECT
CAST(variable_name AS VARCHAR(100)) AS [variable_name] ,
CAST([value] AS VARCHAR(100)) AS [value]
FROM OPENQUERY(MYSQLSRV,'show variables;')
If you do use MySQL it is also worth bearing in mind the storage engine used by the product.
For example, if the engine is MyISAM then performance for a sequential extraction is very high. InnoDB is good for OLTP style queries.
April 17, 2012 at 3:29 am
Link to download MySQL for MS Windows in one installer package.
http://dev.mysql.com/tech-resources/articles/mysql-installer-for-windows.html
Kelsey Thornton
MBCS CITP
April 17, 2012 at 8:27 am
I have a question for someone here who has experience with MySQL. I've heard that with MySQL, there are are options for multiple table storage types, one of which is called "non transaction safe". Is this table type not transaction logged at all, or is it just minimally logged in a manner similar to the SQL Server's Simple Recovery Model?
I'd like to experiment with options for staging multi-GB sized datasets. In this scenario, I would need to bulk load 100 million or more rows into a table and then performing multiple non-selective full table updates. There would be no indexes on this staging table other than a primary key on a single column.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 17, 2012 at 3:37 pm
I could be wrong but I thought that InnoDB was the engine closest to the model used by SQL Server and therefore THE engine that is transaction safe.
I didn't think that MyISAM, Merge or memory engines supported transactions.
Blackhole is a strange engine as it simply logs actions into BIN logs so in the MySQL Master/Slave replication you can have a Blackhole slave that acts as a master for further slaves.
MySQL recommend around 8 slaves per master so this means that 1 master can have 8 black hole slaves and then 64 true target slaves.
Of course as the blackhole engine is simply a bin log engine there is no reason why you cannot simply chain these together to get the desired number of slaves.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply