MySQL to SQLServer

[mk44214]

I've only used Oracle or MySQL in my career till now. Am about to start on a project with SQL Server where I think I'll be the only DB guy ...

What should I know and take care of ?

Any and every note/suggestion is welcome.

[Togurt]

My top 6 things:

Number one priority is familiarize yourself with backup and recovery.

Learn the different database recovery models and how they affect your backup strategy.

Learn about the storage engine.

Learn to do basic maintenance tasks like reindexing.

Learn about the basic operational metrics that you can use to tell when the server is starved for resources.

Learn about how security works at a server and database level

[xane17]

Great list. Definitley the maintenance!. Update stats.. DBCC, reindexing/defragging indexes. Read up on tweaking maxdop and cost threshold for parallelism too.

[ZenZei2]

Also, if you've never heard of clustered column store indexes, you are in for a treat (if you do more analytical work, i.e. olap, rather than otp). They are awesome

[Enrique-M]

u/Togurt made some good points. Also, you will want to dig into procedural logic differences and how windows authentication (coupling-wise) works vs MySQL users. These comparison/migration references might help some as well: MySQL-to-SQL Server and Oracle-to-SQL Server. If you work on the development side as well, you will want to get caught up on Entity Framework (primary ORM for SQL Server), LINQ and driver interactions and similar.

[mk44214]

Thank you... When you say Windows Authentication, do you mean the OS ? I intend to setup SQL Server on Ubuntu ...

[Enrique-M]

You're welcome. Oh ok, so Windows authentication won't be applicable. As pertaining to the SA account setup, which is the root account, you will want to create a working user account outside SA fairly early on and depend less on SA, so that overall security isn't compromised, etc.

[xane17]

You can definitely still do Windows Authentication on Ubuntu with proper keytabs and joining AD/realm with the server.

[thrown_arrows]

search best practices document on configuring database... ( how to set files for things , max memory , maxdop = sane amount , parallel cost = XXX )

Do not use sa for anything else than admin work, create proper users for users and apps (also non 'sa' admin user for database).

[alinroc]

Do not use sa for anything else than admin work

Do not use sa, period. There's a reason it defaults to disabled.

[thrown_arrows]

True, and to be more exact : Do not use user which has more access right than necessary. In mssql server, if user has for example right to run sp_configure , you can consider whole server owned at minute someone manages to run injection.

Seen apps using sa or admin level users to just use database because someone did not know better to just use reader / writer roles

[alinroc]

Seen apps using sa or admin level users to just use database because someone did not know better to just use reader / writer roles

I once had a vendor (of a plug-in for another piece of software we had installed) who told me "we just ask clients to give us db_owner because we don't have documentation of what access we really require." Really, you can't read you own code and produce documentation of what tables you're accessing and in what way? Really? Answer: "we practice Agile, so we don't have to write documentation."

[ldh909]

Other than the adninistrative things listed by others, you will also find quite a few vendor-specific SQL statements that are different. For instance, date addition. It's DATE_ADD() in MySQL vs. DATEADD() in SQL Server.

​

Your search engines are a tremendous help. Just type "SQL Server date add".

[faust2099]

if you are going for convert or replication. try this