When COUNT(*) was too slow to get total rows from a billion row heap …
SELECT MAX(REPLACE(CONVERT(VARCHAR(20),
CONVERT(MONEY, rowcnt), 1), '.00', '')) [rows]
FROM sys.sysindexes
WHERE OBJECT_NAME(id) = 'TableName';
Original code from here …
Twice I had to ask how to do this – so it is probably time for me to write it down.
Setting the scene – this was a virtual SQL Server hosted on Azure where I had already configured a backup policy within the Portal, and then wanted to add another database to that policy.
Clicking around in the Backup center and Backup policy screens did not help. The trick was to go through the motions of taking a backup.
2. On the next screen make sure the Datasource type is “SQL in Azure VM” before selecting the right vault.
3. Choose “Configure Backup” …
4. Click “Add” …
5. Then – you can choose the Instance (or AlwaysOn AG), database, then Enable Backup. Simples 🙂
Ok not really “Replication”. More like keeping a MySQL table in sync with a SQL Server table using both varieties of SQL.
I avoided using a linked-server as I wanted this to be able to cope with bulk loading. Sadly the correct tool, SSIS, was not available during this project.
I created a SQL Job with two steps 1) Export data to CSV and 2) Import into MySQL. The SQL code is highly parameterised so it can be reused.
Job Step 1: Export data to a CSV file
/* ExportDataToCsvFile.sql */
DECLARE @datasource VARCHAR(100) = 'SomeTableName';
DECLARE @cmd VARCHAR(400);
SELECT @cmd = 'BCP SomeDatabase.dbo.' + @datasource + ' out D:\Export\' + @datasource + '.csv -t, -c -T';
EXEC master..xp_cmdshell @cmd;
Line 1: Is just to let me know I have my own copy of this code block.
Line 3: Should be updated to the data source specific to each project (NOTE: For simplicity the data-source and CSV file both share this name.)
Job Step 2: Import CSV into MySQL table
/* ImportCsvIntoMYSqlTable.sql */
DECLARE @table VARCHAR(100) = 'SomeTable'; /* << change this */
DECLARE @database VARCHAR(100) = 'SomeInstance_SomeDatabase'; /* << change this */
DECLARE @sql VARCHAR(2000) = '';
DECLARE @cmd VARCHAR(8000);
DECLARE @IsError INT;
/* 1 Build MySQL script to empty then refill table */
SET @sql = @sql + 'START TRANSACTION;';
SET @sql = @sql + 'DELETE FROM ' + @table + ';';
SET @sql = @sql + 'LOAD DATA LOCAL INFILE ''G:\\Export\\' + @table + '.csv'' INTO TABLE ' + @table
SET @sql = @sql + ' FIELDS TERMINATED BY '','' ENCLOSED BY ''\"'' LINES TERMINATED BY ''\r\n'';';
SET @sql = @sql + 'COMMIT;';
/* 2 Execute it */
SET @cmd = 'G:\Export\MySql\bin\mysql.exe --defaults-extra-file=G:\Export\MySql\'' + @database + '.cnf -e "' + @sql + '";';
EXEC @IsError = master..xp_cmdshell @cmd;
IF @IsError <> 0 RAISERROR('INFILE Error', 16, 1);
/* 3 Defragment table and Update stats */
SET @sql = 'OPTIMIZE TABLE ' + @table + ';';
SET @cmd = 'G:\Export\MySql\bin\mysql.exe --defaults-extra-file=G:\Export\MySql\' + @database + '.cnf -e "' + @sql + '";';
EXEC @IsError = master..xp_cmdshell @cmd;
IF @IsError <> 0 RAISERROR('OPTIMIZE Error', 16, 1);
Lines 3: Will need to be changed for each project. And names both the CSV file and target MySQL table.
Line 4: Is a previously created file used by MySQL to connect to a target instance & database.
Lines 12 to 16: Builds up a string of MySQL commands to empty then refill the table from the CSV file.
Lines 12 & 16: These two lines create a single transaction. This serves two purposes. 1) The ‘old data’ will not be deleted if the ‘new data’ load fails. 2) The switch-over from ‘old data’ to ‘new data’ will be instant.
Database Consultant 