6 Replies - 956 Views - Last Post: 23 November 2014 - 11:56 AM

#1 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Tools Suggestions Request (MS SQL 2012)

Posted 21 November 2014 - 10:58 AM

I am not a DBA, and database management is really NOT my forte. I can do relational design, but I still don't even have a solid understanding of stored procedures. Sad, I know, but I'm looking to fix that lack of knowledge.

I'm hoping for suggestions. Bonus if the software is free, but I do intend to monetize the project I'll be building so I don't mind spending a little on it. Just no $500 software suites please (unless it's like...super badass good and does EVERYTHING I need).

Here's what I already have, fully owned and licensed (not student, not trial), so if there are additional packages I don't know about I have access to those as well.

I have:
MS SQL 2012
Windows Server 2012
Visual Studios 2012
Windows 8.1 (this is my dev environment)

My goal is to build an MVC Web Application.

The tools I have a passing familiarity with for managing MS SQL are
SQL Server Management Studio
SQL Server Configuration Manager

That is, sadly, the limit of my experience there. And that is very limited too. I know how to connect to a database (mostly, I'm having trouble establishing a remote connection) and write/run queries. I understand SQL syntax, just not the software beyond running the scripts.

I have MS SQL 2012 and its packaged tools. I've had some trouble getting Management Studio to connect to my remote instance, though I was able to successfully set it up as a Data Source in Visual Studios and connect there.

Below this point I'm going to discuss what kind of tools I want and what I'm looking for suggestions for. I really do not know and haven't been able to find anything free/affordable scouring Google a few times over the past week (I've spent maybe 2 hours total digging around, plus however much time reading reviews on various tools). I'll be honest, the more noob friendly the better. If any of this already exists in the above tools and packages I mentioned, I simply haven't found that part of the program and do not know what explicitly to look for to identify it. Please point it out to me? I don't mind researching how to do what I need to on my own, but I don't have exact terms for what I'm trying to do that have identified.

What I'm looking for are tools to make the management and design aspects of the database easier. I'm capable of writing all the add table scripts and writing out definitions for each column line by line, but I'd really prefer something a little simpler and I can't imagine it doesn't exist - I know there's a PHP tool my old host used to use to manage MySQL that made design somewhat easier.

What I'm imagining is the way MS Access allows you to visually design tables and define their relationships. I would love something that let me do that.

Another thing I'd love to be able to do is quickly and easily manage multiple instances of the same DB so I can set and manage multiple environments (Dev, QT, Deployment, Production) - I'd also like to be able to easily move data backward on that chain, like copy all data from Prod to Dep before upgrading Dep with the QT build, so I can verify that an exact copy of Prod works prior to deploying the changes to Prod. And some way to easily scan for mismatched data between two instances.

This post has been edited by depricated: 21 November 2014 - 11:10 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Tools Suggestions Request (MS SQL 2012)

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15679
  • View blog
  • Posts: 62,783
  • Joined: 12-June 08

Re: Tools Suggestions Request (MS SQL 2012)

Posted 21 November 2014 - 11:19 AM

Quote

What I'm imagining is the way MS Access allows you to visually design tables and define their relationships.

So you are looking for something that takes your UML diagrams and makes them into tables?
http://www.sqlmaestr.../mssql/maestro/

Quote

I'd also like to be able to easily move data backward on that chain,

Linked servers.. then it's an easy as "INSERT INTO <sever name>.<table name>(columns) SELECT <columns> from <server2 name>.<table 2 name>"
http://msdn.microsof...y/ff772782.aspx
Was This Post Helpful? 1
  • +
  • -

#3 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Tools Suggestions Request (MS SQL 2012)

Posted 21 November 2014 - 11:41 AM

modi I love you man

Looking over Maestro, I never saw that in my search but it looks to be exactly what I'm looking for. Yes, I want to convert UML diagrams to tables - I hadn't even thought to describe it that way. Taking a cursory look at this now. I'll trial it over the weekend. I think that's absolutely perfect though. And the price is on the high end of within my budget for software for the project, but that would pretty much tap out what I'm willing to spend until the project starts rolling. But it looks like this might do literally everything I want, so that would be worth it.

I don't think linked servers is what I want. The upgrade path I intend to take is to have 4 instances of the database - Dev, QT, Dep, Prod - and 4 instances of the application, likewise Dev, QT, Dep, Prod. I will do development locally, then deploy against my Dev Instance (so dev.mytestsite.com). Once I've passed Dev testing I'll deploy database upgrades and the application to qt.mytestsite.com. I have 3 people who will be helping me run tests there, and do automated testing. Once it's passed QT I want to replace the deployment DB with an exact copy of the prod db - so I could just back up prod and then restore deploy from the prod backup. Then I'd want to run a comparison against the prod db to make sure everything matched up. THEN I would deploy the application to mytestsite.com. Do some smoke testing on the deployment environment just to make sure it appears to be working properly, then take down mysite.com, upgrade the prod DB, and deploy the latest build to production.

Seems like a fairly standard upgrade path to me - I'm just looking for ways to make my life easier since I don't have a huge team to help me with it, just me and a few of my friends.

and those friends are mostly just providing testing and suggestions

edit: I accidentally a word

This post has been edited by depricated: 21 November 2014 - 11:42 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15679
  • View blog
  • Posts: 62,783
  • Joined: 12-June 08

Re: Tools Suggestions Request (MS SQL 2012)

Posted 21 November 2014 - 12:20 PM

Quote

Yes, I want to convert UML diagrams to tables

Also you may want to keep an eye on Visio. For quite a few years now they have a direct 'create db tables from UML' baked in.

https://products.off...wchart-software
Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7507
  • View blog
  • Posts: 15,558
  • Joined: 16-October 07

Re: Tools Suggestions Request (MS SQL 2012)

Posted 21 November 2014 - 05:31 PM

View Postdepricated, on 21 November 2014 - 01:58 PM, said:

My goal is to build an MVC Web Application.


If my primary goal was this and I wasn't a database guy, I'd use Entity Framework with "code first." Essentially, you write the classes, or with EF you can also doodle the classes, and let Microsoft figure out what the database should look like. It's reasonably clever. The LINQ expressions are even optimized into SQL, so you essentially never have to worry about the database, or even writing SQL; just the "context."

Start here: http://www.asp.net/m...mvc-application
Was This Post Helpful? 1
  • +
  • -

#6 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Tools Suggestions Request (MS SQL 2012)

Posted 22 November 2014 - 10:50 PM

I've been looking into the Entity Framework after reading this. Not sure if that'll help or not. Maybe, maybe not. I'm going to try it out, once I figure it out - it might be absolutely perfect. It just seems to me that I would have less control over the DB with it, and that seems like a bad thing.

But I'll try it before it before judging. That's just my trepidation. I've spent a couple years at this point determining exactly what I need table-wise, since that's really the majority of the application.
Was This Post Helpful? 0
  • +
  • -

#7 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7507
  • View blog
  • Posts: 15,558
  • Joined: 16-October 07

Re: Tools Suggestions Request (MS SQL 2012)

Posted 23 November 2014 - 11:56 AM

View Postdepricated, on 23 November 2014 - 01:50 AM, said:

It just seems to me that I would have less control over the DB with it


You are entirely correct. However, it does depend on your focus. I'm a database guy; the company DBA, actually. I use EF from the other side, generating it from an existing schema.

Along those lines, you can set up a Database project in VS2013 and manage all the object SQL from there. If you don't know databases, it has some gui stuff, but I use it because I can touch SQL directly. It also has some spooky smarts for validation. Now that you have a happy SQL Database, point EF at it and see what comes out. This gives you the best of both worlds, really.

To be clear, I generally loathe ORM tools like EF. They usually piss my off at some point and I end up writing my own code. However, EF is pretty mature now and easily the best ORM I've seen. There are many places along the line you can override it if is screws up. And, most importantly, it translates the code based LINQ into SQL WHERE, so I'm not getting soaked on performance. ( Well, much. )
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1