So I go for it Part2. A begginer's log in 70-462

ektorakos

So here I am . Thrilled and ready with the 70-462 training kit in my hands.

I read the contents of the book and a few topics that sounded interesting are :

- Migrating, Importing and exporting.

If i want to switch my Access Db to SQL this sounds as very useful

- Logins Roles and Users

In my Access app i have many users and i sort of have a way to control who can do what. So this should be how to do this in SQL. It would also help me get answers to some things that were written in 461.
-Securing
Again sounds very useful. protect the db so that nobody can f*** it up.

-Indexes and Concurrency.

-SQL Back up and REstore
Veeeery usefulApart from the above mentioned topics I dont understand anything else.

I have some questions if anyone could answer.

In the first pages there are some instructions of how to install SQL in 4 computers
eeeerm. this is technicaly very difficult to happen at home? is it important? is there a solution?
is this the first thing i should do otherwise i will not be able to follow the book??

Also i saw that i need to find the SQL server R2 Enterprize edition with Service Pack 1.
I dont have this installed at home. Should i uninstall and find Enterprise edition??

thanks everyone!

Violated

It shouldn't be hard to do with a few VM's in Hyper-V or VMWare Workstation. I would do it because the book follows that environment.

I don't know if you need enterprise to follow along with the book, but it shouldn't be hard to do an upgrade if you find a need for enterprise.

Good luck on your studies!

ektorakos

Thanks violated.

VM = Virtual Machines? (you understand i am tottaly nube with this )

Ok i will find how to do this by googling
Thanks

Violated

Yes, it's virtual machine. It makes it a lot easier to set up multiple servers since you don't need to use a physical server.

AvgITGeek

If you can't install either Hyper-V or VMWare there is always Oracle VirtualBox. I'm finishing up my lab environment now. Got my Domain Controller installed, now to join my 5 SQL Servers and get cracking!

ektorakos

I managed to find in the web some really nice instructions for the setup.

these are the links

option 1 using hyper-V
MCSA 70-462 Training Kit Preparation: Hyper-V Lab Environment Setup - SQLServerCentral

option 2 using VMware Workstation 9
https://toddkleinhans.wordpress.com/

I think i will go with the first option.
I also have a friend that knows a lot Windows Server so i will setup with him.

AvgITGeek

That is the exact lab I'm using with Hyper-V. Got it from the Microsoft Press book. Too bad they didn't feel the need to update the book but I can understand why they didn't. 2012 and 2014 are so similar.

ektorakos

good to hear that. so i will definitely go with this method

ektorakos

I have started studying the book. I have not setup the lab yet so i skip the exercises for now. sometime during this week i will set this up and come back.

Okkk. so after reading the first 2 chapters i have to say :

1) The first chapter was a shock. so different from 461. so technical stuff that i was thinking
Oh God i will never pass this! Why am I studying this!

2) after studying the 2nd Chapter i feel a bit more confident. either it was easier, or my brain has adapted to reading this type of things.

3) Fun Factor. Much lower than 461. Its not that so technical stuff is not important. indeed they are. i just dont find them that exciting.

4) However. I feel that 462 is more straightforward. you studied? you ll pass. with 461 i had a feeling that even if i studied a lot things could go really nasty and fail.

5) while i read the book , i am underlining the important parts (most of the parts ) and then i am highlighting the top important parts.
And then i reach the questions in the end of the lesson and i am asked things that i felt naaah they cant ask you to memorize this table with all sorts of versions and operating system compatibility. i am a nube in such things so i try to understand the why is these things important and not the how. Why should you configure the Resource Governor and not how to do this. But from what i see the requirement is going to be the latter.

6) A note and a question for you. I think i should give much more emphasis in learning how to do various configurations using T-Sql and not that much by using the graphical nodes of SSMS (right clicking + properties etc). What do you think? does the 462 test require to use code as well?

please note that the above are only for the 2 first chapters. I dont know whats coming next.

AvgITGeek

I haven't taken this exam but I have taken the 410 and 411. Based on those two, I'd expect to be questioned on both the GUI, T-SQL and PowerShell way of configuring something. The installation requirements and upgrade paths I'd also be sure to know. Installation and picking the right version is a very important part of SQL Server administration.

ektorakos

hello everyone,

disappointment all the way up.

i managed to do the lab with a friend of mine. i learned quite a lot of things.
but now i am starting the exercises and jesus nothing seems to work.

My first problem is the core server! in this blind environment its almost too impossible (for me) to do anything.
I dont know how to get sql installed because i dont know how i am going to go in the volume that contains the setup file.
In the other servers i was able to show the external hard disk that contained the setup so i could mount the iso and proceed .
i am going to need help with that.

but a more serius problem is that after installing sql engine in SQL A and B I cannot connect to server SQL - B from SQL SSMS in Server SQL A
the so called connectivity problems.

who knows what is wrong. i think that its going to be too dificult for me to follow the exercises.

AvgITGeek

Being that it sounds like you can access your host's DVD (or iso), you'll have to use the command line to get to the mounted DVD (or iso) in Server Core. cd d:\ (if your mount point is d) then you should be able to run a dir command and see setup.exe.

I'm assuming you have network connectivity between Servers A and B. If not, you need to get that working first.

If you are following the book and installing from the command line make sure to enable TCP. I pulled my hair out because of this omission in the command line install. If you can connect to your SQL Core server from one of your GUI SQL servers, you may be able to enable TCP remotely. I haven't tested but it should be possible. Also, check to see if SQL Browser is running on the server(s) you can't connect to.

While in Server Core, the command: sconfig Is really useful.

ektorakos

AvgITGeek wrote: »

Being that it sounds like you can access your host's DVD (or iso), you'll have to use the command line to get to the mounted DVD (or iso) in Server Core. cd d:\ (if your mount point is d) then you should be able to run a dir command and see setup.exe.

I have only Virtual machines. so i cannot put a dvd anywhere . I have created a virtual hard disk C: for each virtual machine.
I have an external HD that contains the iso file . for the other virtual machines (non core) i set them up to be able to see the external disk of the main computer (the one that runs HyperV) . So no problem with them. I wander if there is a way to copy paste the contents of the external disk in the virtual hd of the core server. i will try and do that

AvgITGeek wrote: »

I'm assuming you have network connectivity between Servers A and B. If not, you need to get that working first.

Is this below the procedure to link them? Then i did it for all servers one by one.


Netsh interface ipv4 set address "Ethernet" static 10.10.10.60
Netsh interface ipv4 set dnsservers "Ethernet" static 10.10.10.10 primary
netdom renamecomputer %computername% /newname:SQL-CORE

I am not sure i did the primary reverse zone in the "prepare AD DS" part correctly. because it was saying to do it for a subnet 10.10.10.x
all i could do was input 10.10.10 and leaving the last part empty.
but below it was saying sth like 10.10.10.blahblahsth (that was surely not x)

AvgITGeek

If you are using hyper-v, create a DVD and point it at your ISO. That way, the ISO will act like a DVD drive and you can access setup.exe by cd d:\ (or what ever your VM is thinking is the DVD mount)

Reverse DNS lookup I wouldn't worry about. If you got all of the member servers to join the domain, they should be able to talk to each other. Did you create the Group Policy to allow sqlservr.exe inbound? Did it apply properly? Open powershell as administrator and run gpresult /r and scroll up. It will tell you if the SQL-POLICY applied. I take it you are using the MS Press study guide.

I'm using the MS Press 70-462 training kit and am just getting to the point of installing SQL server. My Hyper-V servers are all running Server 2012 R2 so I'll be catching up to you in a bit.

ektorakos

AvgITGeek wrote: »

If you are using hyper-v, create a DVD and point it at your ISO. That way, the ISO will act like a DVD drive and you can access setup.exe by cd d:\ (or what ever your VM is thinking is the DVD mount)

that sounds a good idea. i will try it

AvgITGeek wrote: »

Did you create the Group Policy to allow sqlservr.exe inbound? Did it apply properly? Open powershell as administrator and run gpresult /r and scroll up. It will tell you if the SQL-POLICY applied. I take it you are using the MS Press study guide.

hmm. i did it but i didnt know of a way to check it. actualy i used exactly what the book was saying, but later i made a change to the path as the actual in my pc was different from that in the book. maybe there is another difference. i will also try to check it the way you say.


thanks man.

I would like to say here that i am learning way too many things but it slows me down in my studying.
The good thing is that from what i see most of the exercises work just in SQL - A so i can move on.

ektorakos

the trick with the dvd rom using hyper v worked as a charm

now there is another problem using the prompt to install sql

first of all i am using sqlserver 2014 developer edition

i typed the setup.exe command that is shown in page 49 of the book but i get en error for the term IACCEPTSQLSERVERLICENCETERMS

ITS not recognised
does anyone know why??

AvgITGeek

For Group Policy, as long as you change the path to what you are using, that should work. The gpresult command isn't going to tell you if the application can't get through, just tell you if the Group Policy Object (GPO) was applied or not.

Glad you got the DVD situation worked out. Now that you have the DVD drive created, all you need to do is in your Hyper-V settings for that VM is point it to a different ISO if you need to use it and it works in real time on your VM guests.

Things I've noticed and suggestions:

There is a typo. It should be: IACCEPTSQLSERVERLICENSETERMS

Break it up to remember: I Accept SQL Server License Terms. I think it is genius they make you type this so you are aware that you are installing licensed software. Clever and funny on MS part.

If you are using the MS Press study guide, there are a lot of typos in that book.

Also, because you are using a developer version, you should include the switch /PID="xxxxx-xxxxx-xxxxx-xxxxx-xxxxx" with your activation code in place of the xxxxx. If you don't include this, evaluation edition will be used as default by the installer. You also may need to include /TCPENABLED=1 or you may not be able to connect to the SQL Server over TCP.

Check the following link for the valid switches (SQLServer 2012 is pretty much identical too):
http://msdn.microsoft.com/en-us/library/ms144259(v=sql.120).aspx

ektorakos

bummer!!! i always spell this word wrongly (licence)

so now i get error for not having NET 3.5 . so i am using your other thread about this.

but regarding the rule for getting the different SQL instances communicate among servers nope!! nothing works. i have tried everything. and i mean everything. i have even disabled firewall completely in SQL-A and SQL-B and DC. nothing.

when i run gpres i get a lot of things ,but nothing regarding the SQL-policy i try!

ektorakos

SQL server installed in CORE! at last!

question. should DC be switched on always?

ektorakos

a new problem

i have used hyper v settings to enable enhanced Session mode policy.

I did that to enable my hyper V see my external hd.

It worked perfectly and every time i was connecting to the hyper v it was asking if i want to make any volume visible .
But due to my stupidity i pressed save current settings and i dont see this box anymore. so i dont know how to re enable it.
the setting is still on , i see the box when i switch on the other vms but for this specific one i cannot.

AvgITGeek

Start the VM in question but DO NOT connect to it. Right click the VM in Hyper-V Manager and choose Edit Connection Settings. Click Show Options, Local Resources, In Local devices and resources click More... Expand drives and select the ones you want your VM to have access to. If you want to keep these settings, keep Save my settings for future connections to this virtual machine checked on the Display tab.

Yes. The Domain Controller needs to be on or else Group Policy will not work. The domain controller will also authenticate users if they never logged onto a domain joined member server.

For connections between servers with a GUI, make sure the TCP protocol is enabled in server configuration and you also might want to turn on SQL Server Browser too. Now that you have windows firewall disabled, can you ping Server-A from Server-B? I'm thinking you can but start out with the simple things troubleshooting wise.


Hope this helps.

ektorakos

AvgITGeek wrote: »

Start the VM in question but DO NOT connect to it. Right click the VM in Hyper-V Manager and choose Edit Connection Settings. Click Show Options, Local Resources, In Local devices and resources click More... Expand drives and select the ones you want your VM to have access to. If you want to keep these settings, keep Save my settings for future connections to this virtual machine checked on the Display tab.

well that is the problem. i accidentally checked the keep save my settings for future connections, and now the options dont show automatically when i double click my vm.
and i dont know how to reenable it

ektorakos

Back to my connectivity problems.
i pinged every possible direction. no "no communication errors" so i believe all is ok from this point.

I proceeded to excercises of chapter 2 lesson 2 where you are asked to install additional Db Engine instances in every Vm called ALTERNATE.

Isnt it strange that i cannot even connect to instance ALTERNATE from the same vm??
when i enter the studio and click the server pop up menu in the server name i can only see SQL - A. no Alternate. i actually dont know how i could enter it. the other option is browse for more where i cant do anything.
i am using the windows authentification mode .
and i have completely disabled the firewall
also from sql configuration tools i configured network configuration protocols enabled tcp IP and named pipes.
and restarted both instances to effectuate the changes.
I must be doing sth really wrong...

ektorakos

OH GOD browse for more did the trick !!! i managed to connect to everything!
i will try re enabling the firewall and check again

ektorakos

ok! after re enabling the firewall i can still connect to SQL-B from vm SQL-A and vise versa

But i cannot connect To SQL-b Alternate from vm SQL - A and vise versa.

I dont really care but its strange as i just made a copy of the inbound rule changed the name and the word MSSQSERVER to ALTERNATE in the path to the exe file. but no it didnt work.

However i am really happy , i progressed and did some exercises. tomorrow i will do some more. goodnight everyone!

ektorakos

Ok! i think i am on track!

i finished all exercises until chapter 4. tomorow i will proceed with chapter 5.

Reading of the book has stopped at end of chapter 6.
But now that i do the exercises i understand much better the concepts i was studying.

it really worthed the fuss and the sweat to set up the whole thing. I have never used a single code line in prompt and now i learned so many things.

The exercises are very nice (for my level) no excercise is easy (for me). Some things i find them in the book lessons (most of them ) but there are certain things that i have to google to find out how to do them. and i thing this teaches me so many things.

i think that the second reading through the book will be so much easier as i will have so much better understanding of the actual meaning.

I also want to say that i start to feel excitement in the administrative tasks. in the beggining it felt a bit boring. but now i feel the power. copying this from here there, migrating this, exporting this to a file and then bulk inserting it, creating logins and passing them from one instance to the other using scripts and so much more.
it has its fun for sure

AvgITGeek thanks for your help throughout this process.

AvgITGeek

ektorakos wrote: »

well that is the problem. i accidentally checked the keep save my settings for future connections, and now the options dont show automatically when i double click my vm.
and i dont know how to reenable it

I'm not following you and I can't recreate what you are describing. When I tick the box to save my connection settings when I connect to a VM that dialog box never appears again. The only way I can get it to appear again is by the steps I outlined above. I just did the same thing here and I had the option to set my monitor resolution, if I wanted the clipboard and printers to work and under the Local devices had the option to see if I wanted the VM to use any of my external drives.

Not to sound like a dick, but I'm going to. You haven't even given me one rep for all of the time I've spent trying to help you. Not that rep really matters to me but if someone gives you or you see helpful advise, rep the person.

Good luck

ektorakos

AvgITGeek wrote: »

Not to sound like a dick, but I'm going to. You haven't even given me one rep for all of the time I've spent trying to help you. Not that rep really matters to me but if someone gives you or you see helpful advise, rep the person.

Good luck

oh man you will make me sound like a nube again.

what is a rep ? and how can i rep you? (possitive reporting?)


just found the little star under posts!

if you see my previous post (5 min before yours) , i specifically thanked you for your help, and i will do it again now. apart from that i dont know what else i am able to do. so you have to help me in that as well .

and you dont sound like a dick. i would probably feel the same.

About the problem (with my core vm) using local devices , i might missed a step.

just found the little star under posts!

ektorakos

AvgITGeek wrote: »

I'm not following you and I can't recreate what you are describing. When I tick the box to save my connection settings when I connect to a VM that dialog box never appears again. The only way I can get it to appear again is by the steps I outlined above. I just did the same thing here and I had the option to set my monitor resolution, if I wanted the clipboard and printers to work and under the Local devices had the option to see if I wanted the VM to use any of my external drives.

Ok... i found the solution . no it was not working. in my case after you press the button there was no way to get that options back again. But according to the below post i am not the only one.
however saving the options creates a file vmconnect.rdp.{virtual machine GUID}.config .
you delete it and next time the options appear again.
the file resides in the below path.
C:\Users\<user>\AppData\Roaming\Microsoft\Windows\Hyper-V\Client\1.0


https://social.technet.microsoft.com/Forums/en-US/dad8fd6a-2180-4c57-b0c9-9d98ec81ace2/how-to-change-hyperv-2012r2-enhanced-session-mode-options?forum=virtualserver

ektorakos

I think that my 2014 developer edition does not support replication features.
is there an alternative to download (evaluation of course) that supports it out there?

adam9870

Download SQL Server 2014 SP2 from here:
https://www.microsoft.com/en-in/evalcenter/evaluate-sql-server-2014-sp2


When you install, the choice Enterprise edition.