February 2, 2015 at 6:05 am
hi all,
i am an oracle dba but recently my manager gave me about 100 excel files which have lakhs of data about clients and this data may add upto 200gb and he want these in sql server for mainly reporting.
i learnt about the costing and all and said him we will go with 2008 r2 and i am done with data-modelling i mean like structure, types of coloumns. but i have these questions:
1> which edition of sql server and os is appropriate?
2> whats the procedure to be followed during installation, i mean the difference between oracle and mssql?
3> i came across something like openrowset to import data. is this the right option?
4> tips on best system configuration and dba tasks.
thanks and regards
Tejesh Rao R B
February 4, 2015 at 7:51 pm
Tejesh (2/2/2015)
hi all,i am an oracle dba but recently my manager gave me about 100 excel files which have lakhs of data about clients and this data may add upto 200gb and he want these in sql server for mainly reporting.
i learnt about the costing and all and said him we will go with 2008 r2 and i am done with data-modelling i mean like structure, types of coloumns. but i have these questions:
1> which edition of sql server and os is appropriate?
2> whats the procedure to be followed during installation, i mean the difference between oracle and mssql?
3> i came across something like openrowset to import data. is this the right option?
4> tips on best system configuration and dba tasks.
thanks and regards
Tejesh Rao R B
1. Edition of SQL Server - Don't go with SQL Server 2008R2. Mainstream support is end of life and there are already two newer versions of SQL Server. Those newer versions will come with lots of manageability enhancements. Go with SQL Server 2012 or 2014 on Windows Server 2012 R2. There are a lot of advantages to pairing these new SQL Server versions with the newest OS.
2. Differences between Oracle and SQL Server Install - I know nothing about Oracle so I can't speak to differences. I would encourage you to look up Glen Berry's blog posts on installation best practices. One thing to keep in mind is SQL Server instant file initialization, how SQL Server uses CPU and memory and the multitude of settings related to these items that are configurable post installation.
3. Use of openrowset to import the data - With that many files involved I would look into building an SSIS package to read in the data programmatically. It will offer a lot of ability in terms of data massaging that may need to be done in the process and is designed to be a tool to load data into SQL Server.
4. Tips on best system configuration and DBA tasks: This question inspires entire books to be written. You would do well to look at blog sites and search them for these topics. MSSQLTips, SQLSKills.com, BrentOzar.com, John Sansom, Robert Davis. These are just the tip of the iceberg to get you started. There is an excellent series about being an "Accidental DBA" on the SQLSKills site. That would be a good place to look.
February 5, 2015 at 3:57 am
🙂 thanks for the quick reply Mr. Imarkum
About the edition of Sql Server will you please let me know that will 2008 R2 work for the scenario where 9 associate are working on these excel files for reporting and hence this account should be automated in terms of cost, i mean is 2012 or above mandate for this small account?
whats the procedure for SSIS in your view as i was planning to use openrowset.
in this case whats the best server system configuration according to you.
would be a great help, thanks in advance.
-Tejesh
February 5, 2015 at 10:56 pm
Tejesh (2/5/2015)
🙂 thanks for the quick reply Mr. ImarkumAbout the edition of Sql Server will you please let me know that will 2008 R2 work for the scenario where 9 associate are working on these excel files for reporting and hence this account should be automated in terms of cost, i mean is 2012 or above mandate for this small account?
whats the procedure for SSIS in your view as i was planning to use openrowset.
in this case whats the best server system configuration according to you.
would be a great help, thanks in advance.
-Tejesh
SQL Server 2012 is not required. I just think it is a waste to install SQL Server 2008R2. Newer versions have better troubleshooting tools available. Newer versions still have mainstream support from Microsoft. Even if you can still buy 2008R2 from somewhere, when you go to license it you will be licensing it based on the new licensing model that rolled out with 2012, which is more expensive. So, if you are spending more money you might as well spend it on a new product.
I am not saying you can't or should not use openrowset. I am saying that if you are going to be importing files on a regular basis, I would build an SSIS package. However, if you are more at home in T-SQL as opposed to SSIS then for right now, openrowset may be the way to go for you.
In terms of server configuration there are a lot of directions you could go in and a lot of things to configure. If this were my project I would ask for the following, at a minimum.
Two Dual Core Intel Processors
Windows Server 2008R2 (Preferably 2012 R2)
SQL Server 2012 (Preferably SQL Server 2014)
32 GB of RAM, Preferably more
500 GB of disk space configured something like as follows: 50 GB for TEMPDB on its own drive. 100GB for Transaction Log files on its own drive. 50-75 GB for the OS drive. That leaves you with 275 GB of space to allocate to the database files.
If there is going to be any type of ongoing reporting on this data then I would install not only the database engine but SQL Server Reporting Services as well. This assumes you don't already have some type of reporting tool available. Another thing you could do is skip installing SSRS and look into giving your 9 users access to the tables through Excel. They can use regular Excel to do a lot of good reporting through connections to the database or you could look into utilizing the new Power BI tools in Excel, which allow for some more advanced data modelling prior to making reports in Excel.
Prior to installation I would make sure that you create proper service accounts and assign the service account for the database engine the permissions necessary for instant file initialization. Post install I would configure Max Server memory in SQL Server to be 24 GB. That will leave you 8 GB for the OS and any other processes, like SQL Server Reporting Services. This is what I would do because if you are going to set up a server for this then I think you should think long term.
In terms of DBA tasks, I would think they would be similar to what you are doing with Oracle. The most important task is going to be making sure you can restore this data if it should be lost. That means taking backups and actually restoring them somewhere so that you know the backups are good and that you are capable of restoring the database. You have to think about security for the data.
See what I mean about how there is a lot to consider? There are other things as well, but these are the basics I would start with.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply