The most recent version of SQL Server that runs on Windows 7 is SQL 2014. You can download SQL 2014 Express here: https://www.microsoft.com/en-us/download/details.aspx?id=42299.
You should immediately apply Service Pack 3: https://www.microsoft.com/en-us/download/details.aspx?id=57474
And, yes, you need SQL Server to have any use for SSMS. SSMS is just a tool to talk to SQL Server.
As for what SQL Server does - it does the same as MySQL: it is a relational database management system, RDBMS. Beware, though, that SQL Server and MySQL do not always do things in the same way. This is true for about any pair of RDBMS.