Rocket that launches at the end of the round?

Use a func_movelinear, set the direction to up and the distance to something high like 10000 don't forget to make the texture nodraw or set the render method to don't render.
Parent a prop_dynamic (the rocket) to the func_movelinear brush.

When the round ends, set the speed to something appropriate (units per second)



For mine in sd_sabotage I actually use a math_counter and logic_timer to do the rocket speed.
The counter starts at 1 and every time it changes sends the new value to the func_movelinear as a new speed. The logic_timer fires every .1 second and adds 20 to the counter.

This way, the counter starts at 1 and increases by 200 over the course of every second, so the speed starts slow and builds steadily simulating a constant acceleration rather than a sudden speed jump for a more natural rocket take off.

The particle effect is a weird one. Use an info_particle_system with rockettrail_doomsday as the particle effect. Use a logic_timer refiring every .25s that stops it, then 0.05s later starts it again. That way it will appear continuous rather than stuttery like doomsday's one.
(don't forget to parent the info_particle_system to the movelinear)

Trigger_hurt brush for the exhaust kill volume (again, parented)

Ambient_generic with the sound doomsday.launch for the liftoff sound
and finally an env_shake, aplitude 5, radius 8000, duration 5, frequency 50 and you'll get a nice screenshake as it takes off. (rough values)