Transcript Powerpoint

Please remember to thank our
sponsors!
MSSQL is Coming to Linux
Are You Ready?
Nem W. Schlecht
Big things are coming!
• MSSQL is coming to Linux!!
• LAMP=
•
•
•
•
3
Linux
Apache
MySQL/MariaDB/MSSQL
PHP/Perl/Python
So what???
• Completely free MSSQL
• SQL Express
• Linux
• VirtualBox
• Windows Server 2012 (eval)
4
Some questions…
• What won’t be there?
•
•
•
5
Clustering
Access to registry values
SSMS (on Linux)
Some answers
• Easy install
•
apt-get install mssql-server
• Most things should work
•
•
•
6
Replication
Mirroring
HA Backups
Some answers
• Just the database engine
•
•
No Reporting Services
No Analysis Services
• For now…
7
Sign up today!
• Sign up for notifications on MSSQL
on Linux
•
https://www.microsoft.com/en-us/cloud-platform/sql-server-on-linux
• http://bit.ly/linuxmssql
8
What about now?
• What can we do now?
• Port applications to MSSQL
• Access MSSQL from Linux
9
First steps
• Use linked servers on MSSQL
•
Connect to Linux databases
• FreeTDS library on Linux
•
1
0
Yes! You can access MSSQL from Linux
already!
Linked Servers
• Connect to any DB with an ODBC
connector
•
•
•
1
1
MySQL
PostgreSQL
SQLite
Linked Servers
• Link to external database
•
•
•
•
1
2
Install DB OBCD driver
Connect to DB
Add Linked Server
Query away
Linked Servers
• OPENQUERY()
•
1
3
Query external database
Linked Servers - SELECT
SELECT *
FROM OPENQUERY(MySQLdb, '
');
1
4
SELECT id
, name
, age
, address
, active
FROM users
WHERE active = "y"
Linked Servers - INSERT (What?)
INSERT INTO
OPENQUERY(MySQLdb, '
SELECT id
, name
, age
, address
FROM users
WHERE 1=0
')
VALUES (
'nem'
, 41
, '123 Town Street, Anycity USA, 12345'
);
1
5
Linked Servers - UPDATE
UPDATE
OPENQUERY(MySQLdb, '
SELECT id
, name
, age
, address
FROM users
WHERE name = "nem"
')
SET age = 41;
1
6
Linked Servers - DELETE
DELETE
OPENQUERY(MySQLdb, '
SELECT id
, name
, age
, address
FROM users
')
WHERE name = ‘nem’
;
1
7
Linked Servers - Copy Table
SELECT name
, age
, address
, active
INTO users
FROM
OPENQUERY(MySQLdb, '
SELECT id
, name
, age
, address
, active
FROM users
');
1
8
Linked Servers - Fun Stuff
• Join a MySQL table to an SQLite
table
• Connect to a MySQL database over
an SSH tunnel
• Use VIEWs to simplify OPENQUERY
1
9
Linked Servers - Demo
• Demo
2
0
FreeTDS library
•
•
Connect to MSSQL Servers from Linux
(and Mac)
Use:
•
•
•
•
•
•
2
1
PHP
Perl
Python
Ruby
Command Line Utilities
Bash Scripts
FreeTDS Configuration
• /etc/freetds/freetds.conf
[global]
tds version = auto
text size = 64512
client charset = UTF-8
[localdb]
host = localhost
instance = MSSQLSERVER
port = 1433
tds version = 8.0
2
2
FreeTDS Configuration (multiple names)
[localdb]
host = localhost
instance = MSSQLSERVER
port = 1433
tds version = 8.0
[appdevdb]
host = localhost
instance = MSSQLSERVER
port = 1433
tds version = 8.0
2
3
FreeTDS Configuration Instance
[appqadb]
host = localhost
instance = APPQA
port = 14331
tds version = 8.0
2
4
FreeTDS Utilities
• tsql
• Test SQL connection, but can run any SQL
• defncopy
• Print out CREATE statement for SQL object
• datacopy
• Copy data between servers
2
5
FreeTDS Utilities
• freebcp
• bcp clone
• fisql
• Interactive SQL shell
• osql
• OSQL clone (uses ODBC)
2
6
FreeTDS Utilities
• Demos
2
7
FreeTDS Library
• Uses existing language database API
2
8
FreeTDS Library API (Perl)
#!/usr/bin/perl
use DBI;
$db = "nemdev";
my $dbh = DBI->connect("dbi:Sybase:$db"
, "$ENV{DBI_USER}"
, "$ENV{DBI_PASS}") ||
die "No DB Connect";
$dbh->do("USE tempdb");
$dbh->do("DROP TABLE test_table");
$dbh->do("CREATE TABLE test_table (test_field VARCHAR(10))");
2
9
FreeTDS Library API (Perl cont.)
my $sth = $dbh->prepare("
INSERT INTO test_table
(test_field)
VALUES (?)
");
my(@insdata)=qw(this is just some text);
for (@insdata) {
$sth->execute($_);
}
$sth->finish();
3
0
FreeTDS Library API (cont.)
$sth = $dbh->prepare("
SELECT test_field
FROM test_table
ORDER BY test_field
");
$sth->execute();
while (my @row = $sth->fetchrow()) {
print "Row data: @row\n";
}
$sth->finish();
$dbh->disconnect();
3
1
FreeTDS Library API (PHP)
<?php
$link = mssql_connect('nemdev', 'nem', 'S3cr3t!');
if (!$link)
die('Unable to connect!');
if (!mssql_select_db(tempdb', $link))
die('Unable to select database!');
$result = mssql_query('SELECT test_field FROM test_table');
while ($row = mssql_fetch_array($result)) {
var_dump($row);
}
mssql_free_result($result);
?>
3
2
Questions?
• Wake Up!
3
3
Contact Info
•
Nem W. Schlecht
•
•
Email:
Twitter:
[email protected]
@nemws1
• PASS Fargo Chapter
• sqlpass.org – search for "Fargo“
• http://sqlfargo.sqlpass.org/
3
4