John W Colby
jwcolby at gmail.com
Wed Feb 19 20:38:21 CST 2014
Arthur, >I'm sick with envy for your 96GB hardware, not that I have any clients with a need for it, but nonetheless, wow. I didn't just go buy that though. I started in late 2004 with a hand built server; dual core AMD, 4 gigs of RAM, x32 windows, x32 SQL Server, and a single database, the infamous "database from hell". It started as only a single table, but 65 million records, 640 fields, FIXED WIDTH. I knew NOTHING about SQL Server, had never touched it. Many on the SQL Server list suffered through my railing about the shitty (non-existent) debug of SSMS. Tons of RTFM answers. Tons more truly helpful answers. But I kept upgrading my hardware and perhaps 3 or 4 years ago I finally upgraded to a dual processor (AMD) motherboard. I could only afford a single 8 core processor so I populated 1/2 half of the board, with 32 gigs (using 8gb sticks, registered, ECC. THAT was the start of a SMOKIN (to me) SQL Server. Awhile later I bought another processor (8 more cores) and pulled 1/2 of the existing memory to give to that new processor. 32 GB with 16 cores. Then I bought 32 gigs at a time twice more to push it up to 96 gigs total. As an aside I am debating whether to go back out and start buying 16 gig sticks to aim for 256 gigs, or just go max out the system with another 32 gigs of 8 gig sticks (for 128 gigs). It was about this time that I discovered SQL Server compression and went about compressing everything. So while I do now have 96 gigs of RAM, I also can load about 128 gigs of data into that 96 gigs. SQL Server loads compressed data into memory, keeping it compressed in memory, and decompressing it on-the-fly. Keep those cores busy!!! I am literally (in many cases) running most or all of my database(s) in ram. Or so I believe. I can tell you that this iteration of the server finally gave me the horsepower I actually required to get "near real-time" processing when joining 4 tables (vews) with 1/4 of a billion records total and get results back within tens of minutes instead of days. I have one particular database with two tables, each of which has 225 million records and are joined on a "shared" PK, i.e. they are more or less 1-1. I will then join those two to a similar set of two tables from another database (108 million records in each table) with filters on both recordsets, pulling counts of matches on 200 bit binary hash fields. And I get these counts in anywhere from 5 to 30 minutes. That is what my client needs and that takes some serious (to me) horsepower. I also BTW keep these databases on a terabyte SSD raid 6 array, to ensure that when I do have to go to disk, it happens quickly. As you might imagine, just loading up 96 gigs of RAM as you start processing something can take awhile. The moral of the story is that it has been a constant evolution, of hardware, software and knowledge to get me where I am today. I am understandably uneasy about diving into moving the database engine to MySQL when I have spent 9 years learning what little I know about SQL Server. If MS hadn't broken their promise about never charging a per core license, I would never have considered this. But with 16 cores, I can never afford SQL Server 2012. And SQL Server 2008 is reaching the end of support. But that is life in the fast lane eh? John W. Colby Reality is what refuses to go away when you do not believe in it On 2/19/2014 3:31 PM, Arthur Fuller wrote: > JC, > > As I wrote in a previous message, moving the data is trivial, but moving > the sprocs is more complex due to differences in syntax. In general, the > MySQL and MariaDb syntax most closely resembles Oracle syntax. A trivial > example is the CREATE PROCEDURE syntax. In MS-SQL, you have to anticipate > the existence of said sproc X using an IF EXISTS block. This is not > required in MySQL or MariaDb. Instead you check its existence in the first > statement, either creating it or updating it. > > Beyond the trivia, there are many other differences. My and Maria do not > support table variables (which lack has caused me problems; I should have > known better than to invest in vendor-specific code). > > That said, the basics remain the same. IN() works as expected in all > implementations, as does JOIN (although there are syntactic variations, but > they are easily found and corrected). > > I'm sick with envy for your 96GB hardware, not that I have any clients with > a need for it, but nonetheless, wow. I feel so inadequate with a mere 8GB > on my laptop. > > As to your immediate question (SQL Server to MySQL) I suggest a look at the > Import/Export Wizard, which can be accessed in several ways (SSMS, > Start/SQL Server/Data Tools/Import|Export, and one other which currently > slips my mind). > > Arthur > > > On Wed, Feb 19, 2014 at 3:16 PM, John Colby <jwcolby at gmail.com> wrote: > >> Can anyone personally recommend conversion tools for moving entire sequel >> Server databases to MySQL. >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > >