Why is it important to cross-skill?
In today’s world, new technologies are emerging everyday. The existing technologies are not only being replaced by something better or, easier or, robust but these technologies themselves are being improved to do more and better in less time and with lesser burden on the end-users whether that means ease of adaptability or, management or, hosting or, administration. Think of something like cloud computing or, Oracle’s autonomous offerings; the list goes on. Today there is a better way of doing the same thing as compared to yesterday; database administration is no exception.
This fast evolving world of emerging technologies demands “us” to follow the trend if not create one by expanding our horizon and help clients make a better decision as part of cost savings or, simply to do better in one way or another.
Why this post?
Learning MSSQL Server administration while already being a seasoned Oracle DBA is not easy as a click of a button but very practical at the same time. I did it myself and when one of my well-respected senior colleague asked, how much SQL Server can be taught to an Oracle DBA in two days, I thought of writing this post.
Obviously, I am not the first one on this topic and the question is not new as I have seen quite a few variants of it. During my research and learning curve, I found various articles, posts, videos even books which talks about the same topic, however barring some (I will provide links which I personally found to be useful), almost all concentrated on learning MSSQL Server administration and in my honest opinion, I wanted something by which I can quickly relate with what I already knew! This post is a small attempt to address the gaps that I found so that anyone who is willing to take up an additional skill gets a decent starting point.
Believe you me, the topic is vast however, I will try to be as concise as possible while touching each of the following important topics in my subsequent posts.
- Database and instance (The biggest confusion!)
- Base architecture
- Access management
- Backup and recovery
- Job scheduling
- Performance tuning
Database and Instance (The biggest confusion!)
While both the database systems follow similar terminologies, the difference lies between what they mean in Oracle vs. MSSQL Server. Personally, I have seen the biggest confusion in understanding the difference between a database and an instance.
An instance in SQL Server is a running copy of the binaries or, the software itself. For simplicity, think of a SQL Server instance as database home in Oracle. A database home in Oracle can contain multiple databases, same is the case with an instance in SQL Server which can host multiple databases. As you can install multiple Oracle homes on a machine, multiple instances of SQL Server can also be installed on a single machine provided the hardware requirements are met.
In Oracle, an instance is a set of background processes and memory structures which manages the database files and helps users to interact with the database. An oracle database can have one or, more instances (in case of Real Application Cluster (RAC)).
A MSSQL Server installation comes with a set of default databases which are required for SQL server to operate. Oracle will have all the requirements within the home or, database itself. The list of default databases in MSSQL Server includes:
- master: Created with SQL Server installation and is the primary configuration and metadata for the instance
- model: Created with SQL Server installation and serves as template for new database creation. (Make changes to this database to get the same structure in any new database you create)
- msdb: Created with SQL Server installation and used for database job and alert scheduling
- tempdb: Created with SQL Server installation and provides a workspace for temporary objects
- resouredb: Read-only database created with SQL Server installation and contains system objects
- ReportServer: Created as part of reporting services installation
- ReportServerTempDB: Created as part of reporting services installation
- Distribution: Created when replication is configured
Please refer to the following links for more information on this sub-topic. I will talk about the remaining topics in the subsequent posts, stay tuned!
MSSQL Server system databases
Oracle database instance
Oracle RAC 19c